Best way to output list of records from database

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.

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

A better loop whould be:

Select * From CompanyInfoTable

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

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.

Code:

---

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.

:)

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 ;

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, " "
                        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, " "

---

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 :)

I whould do it like this:

[CODE]

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, " "

---

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

Code:

---

                        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, " "

---

this is just getting better and better :)

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

bon point :)