Howdy, Stranger!

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

Categories

Re: Need help in working with Access & VB

java0927java0927 Member Posts: 3
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!
«1

Comments

  • lionblionb Member Posts: 1,688 ✭✭
    : 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]

  • java0927java0927 Member Posts: 3
    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.

  • BarkeeperBarkeeper Member Posts: 335
    : 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

  • lionblionb Member Posts: 1,688 ✭✭
    (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.
    :
    :

  • lionblionb Member Posts: 1,688 ✭✭
    :
    : 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.
  • Hadji_13Hadji_13 Member Posts: 274
    : (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.
  • lionblionb Member Posts: 1,688 ✭✭
    : : (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]
  • Hadji_13Hadji_13 Member Posts: 274
    : : : (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.
  • lionblionb Member Posts: 1,688 ✭✭
    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.
  • java0927java0927 Member Posts: 3
    : 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.

«1
Sign In or Register to comment.