Code to loop through all records in MS Access

Question

I need a code to loop through all the records in a table so I can extract some data  In addition to this  is it also possible to loop through filtered records and  again  extract data  Thanks

User · Answer

Found a good code with comments explaining each statement. Code found at - accessallinone

Sub DAOLooping()
On Error GoTo ErrorHandler

Dim strSQL As String
Dim rs As DAO.Recordset

strSQL = "tblTeachers"
'For the purposes of this post, we are simply going to make 
'strSQL equal to tblTeachers.
'You could use a full SELECT statement such as:
'SELECT * FROM tblTeachers (this would produce the same result in fact).
'You could also add a Where clause to filter which records are returned:
'SELECT * FROM tblTeachers Where ZIPPostal = '98052'
' (this would return 5 records)

Set rs = CurrentDb.OpenRecordset(strSQL)
'This line of code instantiates the recordset object!!! 
'In English, this means that we have opened up a recordset 
'and can access its values using the rs variable.

With rs


    If Not .BOF And Not .EOF Then
    'We don’t know if the recordset has any records, 
    'so we use this line of code to check. If there are no records 
    'we won’t execute any code in the if..end if statement.    

        .MoveLast
        .MoveFirst
        'It is not necessary to move to the last record and then back 
        'to the first one but it is good practice to do so.

        While (Not .EOF)
        'With this code, we are using a while loop to loop 
        'through the records. If we reach the end of the recordset, .EOF 
        'will return true and we will exit the while loop.

            Debug.Print rs.Fields("teacherID") & " " & rs.Fields("FirstName")
            'prints info from fields to the immediate window

            .MoveNext
            'We need to ensure that we use .MoveNext, 
            'otherwise we will be stuck in a loop forever… 
            '(or at least until you press CTRL+Break)
        Wend

    End If

    .close
    'Make sure you close the recordset...
End With

ExitSub:
    Set rs = Nothing
    '..and set it to nothing
    Exit Sub
ErrorHandler:
    Resume ExitSub
End Sub

Recordsets have two important properties when looping through data, EOF (End-Of-File) and BOF (Beginning-Of-File). Recordsets are like tables and when you loop through one, you are literally moving from record to record in sequence. As you move through the records the EOF property is set to false but after you try and go past the last record, the EOF property becomes true. This works the same in reverse for the BOF property.

These properties let us know when we have reached the limits of a recordset.

User · Answer

You should be able to do this with a pretty standard DAO recordset loop  You can see some examples at the following links  http   msdn microsoft com en-us library bb243789 28v office 12 29 aspx http   www granite ab ca access email recordsetloop htm    My own standard loop looks something like this   Dim rs As DAO Recordset Set rs   CurrentDb OpenRecordset  SELECT   FROM Contacts     Check to see if the recordset actually contains rows If Not  rs EOF And rs BOF  Then     rs MoveFirst  Unnecessary in this case  but still a good habit     Do Until rs EOF   True          Perform an edit         rs Edit         rs VendorYN   True         rs  VendorYN     True  The other way to refer to a field         rs Update           Save contact name into a variable         sContactName   rs FirstName  amp       amp  rs LastName           Move to the next record  Don t ever forget to do this          rs MoveNext     Loop Else     MsgBox  There are no records in the recordset   End If  MsgBox  Finished looping through records    rs Close  Close the recordset Set rs   Nothing  Clean up

User · Answer

In  References   import DAO 3 6 object reference   private sub showTableData  dim db as dao database dim rs as dao recordset  set db   currentDb set rs   db OpenRecordSet  myTable    myTable is a MS-Access table created previously   populate the table rs movelast rs movefirst  do while not rs EOF    debug print rs myField   myField is a field name in table myTable    rs movenext              press Ctrl G to see debuG window beneath loop  msgbox  End of Table    end sub   You can interate data objects like queries and filtered tables in different ways   Trhough query   private sub showQueryData  dim db as dao database dim rs as dao recordset dim sqlStr as string  sqlStr    SELECT   FROM customers as c WHERE c country  Brazil    set db   currentDb set rs   db openRecordset sqlStr   rs movefirst  do while not rs EOF   debug print  cust ID     amp  rs id  amp    cust name     amp  rs name    rs movenext loop  msgbox  End of customers from Brazil    end sub   You should also look for  Filter  property of the recordset object to filter only the desired records and then interact with them in the same way  see VB6 Help in MS-Access code window   or create a  QueryDef  object to run a query and use it as a recordset too  a little bit more tricky   Tell me if you want another aproach   I hope I ve helped

[ms-access] Code to loop through all records in MS Access

Examples related to ms-access

Examples related to vba