Code to loop through all records in MS Access

The Problem (Q-score 19, ranked #9th of 95 in the VBA Core archive)

The scenario as originally posted in 2011

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!

Why this Access DoCmd / Recordset path keeps breaking

The scenario uses DoCmd or OpenRecordset, both of which are notorious for bubbling silent failures when the source query has uncommitted changes. The question captures a common debugging dead-end in VBA Core.

The Verified Solution — elite answer (top 10 %%) (+46)

28-line VBA Core pattern (copy-ready)

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

Error-handling details to lift with the snippet

This answer wires error flow through MsgBox / Err.Description. Keep that intact: stripping it to “make it cleaner” removes the signal you’ll need when the macro fails silently on a user machine.

Loop-performance notes specific to this pattern

The loop in the answer iterates in process. On a 2026 Office build, setting Application.ScreenUpdating = False and Application.Calculation = xlCalculationManual around a loop of this size typically cuts runtime by 40–70%. Re-enable both in the Exit handler.

When to Use It — vintage (14+ years old, pre-2013)

A top-10 VBA Core pattern — why it still holds up

Ranks #9th of 95 in the VBA Core archive. The only pattern ranked immediately above it is “VBA Check if variable is empty” — compare both if you’re choosing between approaches.

What changed between 2011 and 2026

The answer is 15 years old. The VBA Core object model has been stable across Office 2013, 2016, 2019, 2021, 365, and 2024/2026 LTSC, so the pattern still compiles. Changes that might affect you: 64-bit API declarations (use PtrSafe), blocked macros in downloaded files (Mark-of-the-Web), and the shift toward Office Scripts for web-first workflows.