Best way to output list of records from database

[plenderj]

Howdy.

I'm using an access database to record some stuff.
There's about 10000 records in the database. Each record has about 30 fields.

I'm using my own numbering system (because I dont trust Access). The RecNum field is a unique numeric field that says what record number this record is.

So for viewing things etc. I use Select * From CompanyInfoTable Where RecNum = n.....

And its going great.

But now I have to write a piece of code to make reports.
Its going to be outputting fields from various fields.
And its going to have to go through each record.

I was thinking of just storing what the total number of records is as a long, then using a do loop :
Do Until i = totalRecordNumber
' ...
Loop

And then looping thru each record and saving it to the file.

Is there any better way of doing this ?
I just want to pick out say 4 or 5 fields from each of the 10,000 records and put that into a file.

[Merlin]

What type of connection to the database are you making? Or is this a VBA question?

[hlynurj]

A better loop whould be:

Select * From CompanyInfoTable

while not recordset.EOF
[CODE]
recordset.MoveNext
wend

[peet]

ok.. I'm sticking my neck out , assuming u use ado or dao ... and want to create a report file.

this routine creates a report.txt file, where the fields are sep. with tabulator.

Private Sub Command3_Click()
    Dim db As Database
    Dim s As String
    Dim rs As Recordset
    Dim SQL As String
    Dim sFile As String
    sFile = "C:\REPORT.TXT"
    SQL = "SELECT CompanyName, ContactName, ContactTitle FROM Customers"
    Set db = opendatabase("c:\demodb.mdb")
    Set rs = db.OpenRecordset(SQL)
    Open sFile For Output As #1
    While Not rs.EOF
        'add info from the fields that you want
        ' in this case the fields are sep. by tabulator
        Print #1, rs("CompanyName") & "" & vbTab & rs("ContactName") & "" & vbTab; rs("ContactTitle") & ""
        rs.MoveNext
    Wend
    Set db = Nothing
    Set rs = Nothing
    Close #1
End Sub

hope this can give you a push in the right direction.

[plenderj]

Well this is what im doing , and its working ok.
I was just wondering (as I'm not too hot with databases) if there as a better way ;

Private Sub cmdCreateList_Click()
    On Error GoTo errorhandler
    For i = 0 To 3
        If (Option2(i).Value) Then Exit For
    Next i
    If (txtPath.Text <> "") Then
        
        Open cd1.FileName For Output As #1
                Select Case Option2(i).Caption
                    
                    Case "Mailing List":
                        Print #1, "Report : Mailing List"
                        Print #1, "Start Time : " & Time
                        Print #1, "---------------------"
                        Print #1, " "
                        Print #1, " "
                        For i = 1 To totalRecNum
                            Set RS = DB.OpenRecordset("SELECT * FROM CompanyInfoTable WHERE RecNum = '" & i & "'", dbOpenDynaset)
                                Print #1, RS.Fields(dFieldName(0)) & vbTab & RS.Fields(dFieldName(8)) & vbTab & RS.Fields(dFieldName(6))
                            Set RS = Nothing
                            Debug.Print i & ":" & i / totalRecNum
                        Next i
                        Print #1, " "
                        Print #1, " "
                        Print #1, "End Time : " & Time
                        Print #1, "---------------------"
                        Print #1, " "
                        Print #1, " "

[peet]

You should pull all the records at once, and then fill the report file. This will be faster than pulling out one by one....

see prev. example

[hlynurj]

I whould do it like this:

[CODE]

 
Private Sub cmdCreateList_Click() 
  On Error GoTo errorhandler 
  For i = 0 To 3 
    If (Option2(i).Value) Then Exit For 
  Next i 
  If (txtPath.Text <> "") Then 

  Open cd1.FileName For Output As #1 
  Select Case Option2(i).Caption 

  Case "Mailing List": 
    Print #1, "Report : Mailing List" 
    Print #1, "Start Time : " & Time 
    Print #1, "---------------------" 
    Print #1, " " 
    Print #1, " " 
    
    set RS = DB.OpenRecordset("SELECT * FROM CompanyInfoTable", DBOpenDynaset)
    
    While not RS.EOF
      
      Print #1, RS.Fields(dFieldName(0)) & vbTab & RS.Fields(dFieldName(8)) & vbTab & RS.Fields(dFieldName(6)) 
      
      'Debug.Print i & ":" & i / totalRecNum 
    Wend
    
    Set RS = Nothing 
    
    Print #1, " " 
    Print #1, " " 
    Print #1, "End Time : " & Time 
    Print #1, "---------------------" 
    Print #1, " " 
    Print #1, " "

[plenderj]

Ok hows this then... its about 10 - 15 times faster...

                        Print #1, "Report : Mailing List"
                        Print #1, "Start Time : " & Time
                        Print #1, "---------------------"
                        Print #1, " "
                        Print #1, " "
                        Set RS = DB.OpenRecordset("SELECT * FROM CompanyInfoTable", dbOpenDynaset)
                        For i = 1 To totalRecNum
                            RS.MoveNext
                            Print #1, RS.Fields(dFieldName(0)) & vbTab & RS.Fields(dFieldName(8)) & vbTab & RS.Fields(dFieldName(6))
                            Debug.Print i & ":" & i / totalRecNum
                        Next i
                        Print #1, " "
                        Print #1, " "
                        Print #1, "End Time : " & Time
                        Print #1, "---------------------"
                        Print #1, " "
                        Print #1, " "

[peet]

this is just getting better and better

[hlynurj]

plenderj,

shouldn't it be like this:


For i = 1 To totalRecNum
Print #1, RS.Fields(dFieldName(0)) & vbTab & RS.Fields(dFieldName(8)) & vbTab & RS.Fields(dFieldName(6))
Debug.Print i & ":" & i / totalRecNum
RS.MoveNext
Next i

[plenderj]

bon point