Re: Need help in working with Access & VB

Hi all:

I am new to VB, and currently I'm given a task that I have to work with Access database. Basically I have to take the data from an Access table and break the data into multiple text files (saving the files with .txt extension). And the filename will also have to be named as same as the 1st field in this table which happpens to be billing #. I'm wondering if anybody would kindly give me some assistance or sample codes? Hope to see some feedbacks soon! Have a nice day!

Comments

  • : Hi all:
    :
    : I am new to VB, and currently I'm given a task that I have to work with Access database. Basically I have to take the data from an Access table and break the data into multiple text files (saving the files with .txt extension). And the filename will also have to be named as same as the 1st field in this table which happpens to be billing #. I'm wondering if anybody would kindly give me some assistance or sample codes? Hope to see some feedbacks soon! Have a nice day!
    :
    The following is very general idea how to create/write sequential file. You can use the same idea to create Random or Binary File just change the some statements (see MSDN for help)
    [code]Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strCon As String, strSQL As String
    Dim strFileName As String, strFile As String

    strCon = "Provider .... " ' blah blah blah
    cn.Open strCon

    strSQL = "select * from MyTable"
    rs.Open strSQL, cn, adOpenKeyset' change cursor according you needs
    strFileName = rs!FieldOne

    Open "C:MyFolder" & strFileName & ".txt" For Output As #1 'sequential file
    Do Until rs.EOF
    strFile = rs!FieldOne & " " & rs!FieldTwo & " " & rs!FieldThree
    Print #FileNum, strFile 'write to file you can also use Write statement
    rs.MoveNext
    Loop[code]

  • Hi lionb:

    Thanks alot for your help in getting me started.
    I have tried to work with your example, and now I can retrieve the data and get them into A (just one) text file - but not multiple like I wanted to...I'm not sure why, if you can help me out that will be great.

    Option Explicit


    Private cn As ADODB.Connection

    Private rs As ADODB.Recordset

    Private Sub Form_Load()

    Dim cn As New ADODB.Connection

    Dim rs As New ADODB.Recordset

    Dim strCon As String

    Dim strSQL As String

    Dim strFileName As String

    Dim strFile As String

    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:sample.mdb; Persist Security Info = False"
    cn.Open strCon

    strSQL = "Select * from finalreport"
    rs.Open strSQL, cn, adOpenKeyset
    strFileName = rs!UNO

    Open "c:MyFolder" & strFileName & ".txt" For Output As #1

    Do While Not rs.EOF

    strFile = rs("UNO") & " " & rs!ACCT_ & _
    rs("DID") & " " & rs!UserName & " " & rs!SDATE & _
    rs("EDATE") & " " & rs!overcall & " " & _
    rs!CALLPACK & _
    rs("OVERCRATE") & " " & rs("TOTAL") & " "

    Print #1, strFile

    rs.MoveNext

    Loop

    End Sub


    -- maybe it's my loop? or? pls advise further if you can thanks.

  • : Hi lionb:
    :
    : Thanks alot for your help in getting me started.
    : I have tried to work with your example, and now I can retrieve the data and get them into A (just one) text file - but not multiple like I wanted to...I'm not sure why, if you can help me out that will be great.
    :
    : Option Explicit
    :
    :
    : Private cn As ADODB.Connection
    :
    : Private rs As ADODB.Recordset
    :
    : Private Sub Form_Load()
    :
    : Dim cn As New ADODB.Connection
    :
    : Dim rs As New ADODB.Recordset
    :
    : Dim strCon As String
    :
    : Dim strSQL As String
    :
    : Dim strFileName As String
    :
    : Dim strFile As String
    :
    : strCon = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:sample.mdb; Persist Security Info = False"
    : cn.Open strCon
    :
    : strSQL = "Select * from finalreport"
    : rs.Open strSQL, cn, adOpenKeyset
    : strFileName = rs!UNO
    :
    : Open "c:MyFolder" & strFileName & ".txt" For Output As #1
    :
    : Do While Not rs.EOF
    :
    : strFile = rs("UNO") & " " & rs!ACCT_ & _
    : rs("DID") & " " & rs!UserName & " " & rs!SDATE & _
    : rs("EDATE") & " " & rs!overcall & " " & _
    : rs!CALLPACK & _
    : rs("OVERCRATE") & " " & rs("TOTAL") & " "
    :
    : Print #1, strFile
    :
    : rs.MoveNext
    :
    : Loop
    :
    : End Sub
    :
    :
    : -- maybe it's my loop? or? pls advise further if you can thanks.
    :
    :

    Never heard about the "SELECT INTO"-Statement in SQL? Check out your MSDN!
    ------------------------------------------
    Only stupidity of mankind and the universe
    are infinite, but i'm not sure concerning
    the universe. A. Einstein

  • (just one) text file - but not multiple like I wanted to...I'm not sure why, if you can help me out that will be great.

    [blue] I gave very simple general example where just one file was open. To write information into different files you have to open several files. For example, if you need to work with 3 files you have to open, write/print and close 3 different files giving then 3 different names like #1, #2, #3. You can do it inside one loop or whatever. My advise to you do not folow any example of code without understanding. Get some other resources like book or MSDN to read about that or like that piece of code[/blue]
    :
    : Option Explicit
    :
    :
    : Private cn As ADODB.Connection
    :
    : Private rs As ADODB.Recordset
    :
    : Private Sub Form_Load()
    :
    : Dim cn As New ADODB.Connection
    :
    : Dim rs As New ADODB.Recordset
    :
    : Dim strCon As String
    :
    : Dim strSQL As String
    :
    : Dim strFileName As String
    :
    : Dim strFile As String
    :
    : strCon = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:sample.mdb; Persist Security Info = False"
    : cn.Open strCon
    :
    : strSQL = "Select * from finalreport"
    : rs.Open strSQL, cn, adOpenKeyset
    : strFileName = rs!UNO
    :
    : Open "c:MyFolder" & strFileName & ".txt" For Output As #1
    :
    : Do While Not rs.EOF
    :
    : strFile = rs("UNO") & " " & rs!ACCT_ & _
    : rs("DID") & " " & rs!UserName & " " & rs!SDATE & _
    : rs("EDATE") & " " & rs!overcall & " " & _
    : rs!CALLPACK & _
    : rs("OVERCRATE") & " " & rs("TOTAL") & " "
    :
    : Print #1, strFile
    :
    : rs.MoveNext
    :
    : Loop
    :
    : End Sub
    :
    :
    : -- maybe it's my loop? or? pls advise further if you can thanks.
    :
    :

  • :
    : Never heard about the "SELECT INTO"-Statement in SQL? Check out your MSDN!

    I heard about SELECT ... INTO. I'll tell more, I even used this statement a lot working with SQL Server, Oracle and Access. At the same time, I've never heard that this statement works with text files. According to MSDN: "The SELECT INTO statement creates a new table and populates it with the result set of the SELECT." There is nothing about text files. Do you have any examples of code where SELECT ... INTO and text files are involved? If you will send me example like that I'll be happy. I am working right now on project where I have to write code to migrate some data form SQL Server to text file. So I need help like that.
  • : (just one) text file - but not multiple like I wanted to...I'm not sure why, if you can help me out that will be great.
    :
    : [blue] I gave very simple general example where just one file was open. To write information into different files you have to open several files. For example, if you need to work with 3 files you have to open, write/print and close 3 different files giving then 3 different names like #1, #2, #3. You can do it inside one loop or whatever. My advise to you do not folow any example of code without understanding. Get some other resources like book or MSDN to read about that or like that piece of code[/blue]
    : :
    : : Option Explicit
    : :
    : :
    : : Private cn As ADODB.Connection
    : :
    : : Private rs As ADODB.Recordset
    : :
    : : Private Sub Form_Load()
    : :
    : : Dim cn As New ADODB.Connection
    : :
    : : Dim rs As New ADODB.Recordset
    : :
    : : Dim strCon As String
    : :
    : : Dim strSQL As String
    : :
    : : Dim strFileName As String
    : :
    : : Dim strFile As String
    : :
    : : strCon = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:sample.mdb; Persist Security Info = False"
    : : cn.Open strCon
    : :
    : : strSQL = "Select * from finalreport"
    : : rs.Open strSQL, cn, adOpenKeyset
    : : strFileName = rs!UNO
    : :
    : : Open "c:MyFolder" & strFileName & ".txt" For Output As #1

    --------The DO needs to be above the open statment

    : : Do While Not rs.EOF
    : :
    : : strFile = rs("UNO") & " " & rs!ACCT_ & _
    : : rs("DID") & " " & rs!UserName & " " & rs!SDATE & _
    : : rs("EDATE") & " " & rs!overcall & " " & _
    : : rs!CALLPACK & _
    : : rs("OVERCRATE") & " " & rs("TOTAL") & " "
    : :
    : : Print #1, strFile
    : :
    : : rs.MoveNext

    ----------------And close the file when you've finished with it, before
    ----------------you loop
    close #1


    : : Loop
    : :
    : : End Sub
    : :
    : :
    : : -- maybe it's my loop? or? pls advise further if you can thanks.
    : :
    : :
    :
    :
    Hope that helps.
  • : : (just one) text file - but not multiple like I wanted to...I'm not sure why, if you can help me out that will be great.
    : :
    : : [blue] I gave very simple general example where just one file was open. To write information into different files you have to open several files. For example, if you need to work with 3 files you have to open, write/print and close 3 different files giving then 3 different names like #1, #2, #3. You can do it inside one loop or whatever. My advise to you do not folow any example of code without understanding. Get some other resources like book or MSDN to read about that or like that piece of code[/blue]
    : : :
    : : : Option Explicit
    : : :
    : : :
    : : : Private cn As ADODB.Connection
    : : :
    : : : Private rs As ADODB.Recordset
    : : :
    : : : Private Sub Form_Load()
    : : :
    : : : Dim cn As New ADODB.Connection
    : : :
    : : : Dim rs As New ADODB.Recordset
    : : :
    : : : Dim strCon As String
    : : :
    : : : Dim strSQL As String
    : : :
    : : : Dim strFileName As String
    : : :
    : : : Dim strFile As String
    : : :
    : : : strCon = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:sample.mdb; Persist Security Info = False"
    : : : cn.Open strCon
    : : :
    : : : strSQL = "Select * from finalreport"
    : : : rs.Open strSQL, cn, adOpenKeyset
    : : : strFileName = rs!UNO
    : : :
    : : : Open "c:MyFolder" & strFileName & ".txt" For Output As #1
    :
    : --------The DO needs to be above the open statment
    :
    : : : Do While Not rs.EOF
    : : :
    : : : strFile = rs("UNO") & " " & rs!ACCT_ & _
    : : : rs("DID") & " " & rs!UserName & " " & rs!SDATE & _
    : : : rs("EDATE") & " " & rs!overcall & " " & _
    : : : rs!CALLPACK & _
    : : : rs("OVERCRATE") & " " & rs("TOTAL") & " "
    : : :
    : : : Print #1, strFile
    : : :
    : : : rs.MoveNext
    :
    : ----------------And close the file when you've finished with it, before
    : ----------------you loop
    : close #1
    :
    :
    : : : Loop
    : : :
    : : : End Sub
    : : :
    : : :
    : : : -- maybe it's my loop? or? pls advise further if you can thanks.
    : : :
    : : :
    : :
    : :
    : Hope that helps.
    :
    [red]Noop. Because in that case the [b]same file[/b] will be opened/written/closed [b]multiple times[/b] and question was about [b]multiple files[/b].[/red]
  • : : : (just one) text file - but not multiple like I wanted to...I'm not sure why, if you can help me out that will be great.
    : : :
    : : : [blue] I gave very simple general example where just one file was open. To write information into different files you have to open several files. For example, if you need to work with 3 files you have to open, write/print and close 3 different files giving then 3 different names like #1, #2, #3. You can do it inside one loop or whatever. My advise to you do not folow any example of code without understanding. Get some other resources like book or MSDN to read about that or like that piece of code[/blue]
    : : : :
    : : : : Option Explicit
    : : : :
    : : : :
    : : : : Private cn As ADODB.Connection
    : : : :
    : : : : Private rs As ADODB.Recordset
    : : : :
    : : : : Private Sub Form_Load()
    : : : :
    : : : : Dim cn As New ADODB.Connection
    : : : :
    : : : : Dim rs As New ADODB.Recordset
    : : : :
    : : : : Dim strCon As String
    : : : :
    : : : : Dim strSQL As String
    : : : :
    : : : : Dim strFileName As String
    : : : :
    : : : : Dim strFile As String
    : : : :
    : : : : strCon = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:sample.mdb; Persist Security Info = False"
    : : : : cn.Open strCon
    : : : :
    : : : : strSQL = "Select * from finalreport"
    : : : : rs.Open strSQL, cn, adOpenKeyset

    -------------Sorry, try the DO in here.

    : : : : strFileName = rs!UNO
    : : : :
    : : : : Open "c:MyFolder" & strFileName & ".txt" For Output As #1
    : :
    : : --------The DO needs to be above the open statment
    : :
    : : : : Do While Not rs.EOF
    : : : :
    : : : : strFile = rs("UNO") & " " & rs!ACCT_ & _
    : : : : rs("DID") & " " & rs!UserName & " " & rs!SDATE & _
    : : : : rs("EDATE") & " " & rs!overcall & " " & _
    : : : : rs!CALLPACK & _
    : : : : rs("OVERCRATE") & " " & rs("TOTAL") & " "
    : : : :
    : : : : Print #1, strFile
    : : : :
    : : : : rs.MoveNext
    : :
    : : ----------------And close the file when you've finished with it, before
    : : ----------------you loop
    : : close #1
    : :
    : :
    : : : : Loop
    : : : :
    : : : : End Sub
    : : : :
    : : : :
    : : : : -- maybe it's my loop? or? pls advise further if you can thanks.
    : : : :
    : : : :
    : : :
    : : :
    : : Hope that helps.
    : :
    : [red]Noop. Because in that case the [b]same file[/b] will be opened/written/closed [b]multiple times[/b] and question was about [b]multiple files[/b].[/red]
    :
    Sorry, look above again, my mistake.
  • I have to take the data from an Access table and break the data into multiple text files (saving the files with .txt extension). And the [red]filename will also have to be named as same as the 1st field[/red] in this table which happpens to be billing #.

    I am sorry, seems to me I missed it first time. Are you trying to save multiple files with same name? In that case you need to save them in different places/folders. Otherwise, file will be overwritten every times. Or you are going to read data from different tables to different files? I am confused.
  • : I have to take the data from an Access table and break the data into multiple text files (saving the files with .txt extension). And the [red]filename will also have to be named as same as the 1st field[/red] in this table which happpens to be billing #.
    :
    : I am sorry, seems to me I missed it first time. Are you trying to save multiple files with same name? In that case you need to save them in different places/folders. Otherwise, file will be overwritten every times. Or you are going to read data from different tables to different files? I am confused.
    :

    Hi lionb:

    Thanks for your advice, I really appreciate it.
    And yes, I need to get the data saved into multiple text files based on the billing #'s.
    tha table is looking something like this:

    billingno field2 field3 ....

    300001 yyy rrr
    300001 bbb fff
    300001
    300002
    300003
    300010
    ect.

    As you can see, with the same billingno, you could have multiple records. And I need to I guess "group" them & save them to text files named 300001.txt, 300003.txt...etc.

    And, yes, I'll spend more time learning from the basics - just I am kind of got thrown into this project by my boss...Anyway. Thanks alot.

  • : : I have to take the data from an Access table and break the data into multiple text files (saving the files with .txt extension). And the [red]filename will also have to be named as same as the 1st field[/red] in this table which happpens to be billing #.
    : :
    : : I am sorry, seems to me I missed it first time. Are you trying to save multiple files with same name? In that case you need to save them in different places/folders. Otherwise, file will be overwritten every times. Or you are going to read data from different tables to different files? I am confused.
    : :
    :
    : Hi lionb:
    :
    : Thanks for your advice, I really appreciate it.
    : And yes, I need to get the data saved into multiple text files based on the billing #'s.
    : tha table is looking something like this:
    :
    : billingno field2 field3 ....
    :
    : 300001 yyy rrr
    : 300001 bbb fff
    : 300001
    : 300002
    : 300003
    : 300010
    : ect.
    :
    : As you can see, with the same billingno, you could have multiple records. And I need to I guess "group" them & save them to text files named 300001.txt, 300003.txt...etc.
    :
    : And, yes, I'll spend more time learning from the basics - just I am kind of got thrown into this project by my boss...Anyway. Thanks alot.
    :
    :

    Check if this works, I think it should.

    [code]
    Option Explicit

    Private cn As ADODB.Connection
    Private rs As ADODB.Recordset

    Private Sub Form_Load()

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    Dim strCon As String
    Dim strSQL As String
    Dim strFileName As String
    Dim strFile As String

    strCon = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:sample.mdb; Persist Security Info = False"
    cn.Open strCon

    strSQL = "Select * from finalreport"
    rs.Open strSQL, cn, adOpenKeyset

    'You might want a routine that kills all the existing
    'files in c:myfolder here.

    Do While Not rs.EOF


    strFileName = rs("billingno")

    Open "c:MyFolder" & strFileName & ".txt" For Append As #1

    strFile = rs("UNO") & " " & rs!ACCT_ & _
    rs("DID") & " " & rs!UserName & " " & rs!SDATE & _
    rs("EDATE") & " " & rs!overcall & " " & _
    rs!CALLPACK & _
    rs("OVERCRATE") & " " & rs("TOTAL") & " "

    Print #1, strFile

    close #1

    rs.MoveNext

    Loop
    msgbox "Done"

    End Sub
    [code]

    Hadji
    ----------------------------------------------------
    The solution to the problem changes the problem

  • : Check if this works, I think it should.
    :
    : [code]
    : Option Explicit
    :
    : Private cn As ADODB.Connection
    : Private rs As ADODB.Recordset
    :
    : Private Sub Form_Load()
    :
    : Dim cn As New ADODB.Connection
    : Dim rs As New ADODB.Recordset
    : Dim strCon As String
    : Dim strSQL As String
    : Dim strFileName As String
    : Dim strFile As String
    :
    : strCon = "Provider=Microsoft.Jet.OLEDB.4.0;DataSource=C:sample.mdb; Persist Security Info = False"
    : cn.Open strCon
    :
    : strSQL = "Select * from finalreport"
    : rs.Open strSQL, cn, adOpenKeyset
    :
    : 'You might want a routine that kills all the existing
    : 'files in c:myfolder here.
    :
    : Do While Not rs.EOF
    :
    :
    : strFileName = rs("billingno")
    :
    : Open "c:MyFolder" & strFileName & ".txt" For Append As #1
    :
    : strFile = rs("UNO") & " " & rs!ACCT_ & _
    : rs("DID") & " " & rs!UserName & " " & rs!SDATE & _
    : rs("EDATE") & " " & rs!overcall & " " & _
    : rs!CALLPACK & _
    : rs("OVERCRATE") & " " & rs("TOTAL") & " "
    :
    : Print #1, strFile
    :
    : close #1
    :
    : rs.MoveNext
    :
    : Loop
    : msgbox "Done"
    :
    : End Sub
    : [/code]

    That's not right either. Here's the logic needed:
    [code]

    Open Connection to database

    SELECT DISTINCT billingno FROM finalreport

    FOR EACH billingno

    SELECT * FROM finalreport WHERE billingno = [billingno]

    OPEN [billingno].txt

    FOR EACH record from finalreport

    write data to open file

    END FOR EACH

    CLOSE open file

    END FOR EACH

    [/code]
  • From the MSDN:

    Syntax

    SELECT field1[, field2[, ...]] INTO newtable [IN externaldatabase]
    FROM source

    The SELECT...INTO statement has these parts:

    Part Description
    field1, field2: The name of the fields to be copied into the new table.

    newtable: The name of the table to be created. It must conform to standard naming conventions. If newtable is the same as the name of an existing table, a trappable error occurs.

    externaldatabase: The path to an external database. For a description of the path, see the IN clause.

    source: The name of the existing table from which records are selected. This can be single or multiple tables or a query.

    Clicking the Link of "externaldatabase":

    external database
    Either an ODBC database such as Microsoft SQL Server that resides on a remote server, or one of the external databases such as Paradox, dBASE, Microsoft FoxPro, Microsoft Excel, Microsoft Access, Lotus 1-2-3, HTML, and [b]Text[/b].

    ------------------------------------------
    Only stupidity of mankind and the universe
    are infinite, but i'm not sure concerning
    the universe. A. Einstein

Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories