One of the most common things that all Access developers do is loop through recordsets to read them, edit them, …
I thought I’d give a few code samples for those of you that are just starting off.
The Basic Loop
At the core of it , the most basic example of a recordset loop would be
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM YourTableName")
With rs
Do While Not .EOF
'Do something here with each record
.MoveNext
Loop
End With
rs.Close
Set rs = Nothing
A simple example might be:
'Output the first name of each contact in a messagebox
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Contacts")
With rs
Do While Not .EOF
MsgBox ![FirstName]
.MoveNext
Loop
End With
rs.Close
Set rs = Nothing
Reporting Empty Recordsets
The code above will simply not do anything in the event of an empty recordset. If you actually want to detect the fact that there are no records returned to be processed and act accordingly (say to notify the user), then you need to add a simple test, such as:
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM YourTableName")
With rs
If .RecordCount <> 0 Then
Do While Not .EOF
'Do something here with each record
.MoveNext
Loop
Else
'There are no records returned!
MsgBox "No records to process!", vbInformation Or vbOKOnly, "No Records"
End If
End With
rs.Close
Set rs = Nothing
Some developers prefer to test both .BOF and .EOF instead, so something along the lines of:
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM YourTableName")
With rs
If Not (.BOF And .EOF) Then
Do While Not .EOF
'Do something here with each record
.MoveNext
Loop
Else
'There are no records returned!
MsgBox "No records to process!", vbInformation Or vbOKOnly, "No Records"
End If
End With
rs.Close
Set rs = Nothing
Getting An Accurate Record Count
I’m sure you’re saying to yourself that’s simple, you already showed us how with .RecordCount! Well, you’d be right and wrong at the same time!
You see, there an issue with the RecordCount and the way OpenRecordset works. Access won’t necessarily load all the records at once, thus .RecordCount can reliably tell you if there are records, or not, but won’t necessarily give you the right total count. To get the proper count, you must first force Access to navigate to the last record, then retrieve the count. This can be done by doing:
Dim rs As DAO.Recordset
Dim lRecordCount As Long
Set rs = CurrentDb.OpenRecordset("SELECT * FROM YourTableName")
With rs
.MoveLast
lRecordCount = .RecordCount
.MoveFirst
If lRecordCount <> 0 Then
Do While Not .EOF
'Do something here with each record
.MoveNext
Loop
Else
'There are no records returned!
MsgBox "No records to process!", vbInformation Or vbOKOnly, "No Records"
End If
End With
rs.Close
Set rs = Nothing
Notice how I perform a .MoveLast prior to using .RecordCount and then immediately perform a .MoveFirst to go back to the start so I can then perform my loop.
Another alternative would be to loop through the records backwards, by doing something like:
Dim rs As DAO.Recordset
Dim lRecordCount As Long
Set rs = CurrentDb.OpenRecordset("SELECT * FROM YourTableName")
With rs
.MoveLast
lRecordCount = .RecordCount
If lRecordCount <> 0 Then
Do While Not .BOF
'Do something here with each record
.MovePrevious
Loop
Else
'There are no records returned!
MsgBox "No records to process!", vbInformation Or vbOKOnly, "No Records"
End If
End With
rs.Close
Set rs = Nothing
but, personally, I usually tend to prefer the former approach.
Inserting Records
You can easily use a loop to insert new records into a recordset, the basic concept being
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT FirstName, LastName, CreatedOn FROM Contacts WHERE 1=0", , dbAppendOnly)
With rs
.AddNew
![FirstName] = "John"
![LastName] = "Wayne"
![CreatedOn] = Now()
.Update
End With
rs.Close
Set rs = Nothing
Updating Records
Updating existing records is equally straightforward and is done by doing something along the lines of:
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT CreatedOn FROM Contacts WHERE CreatedOn Is Null")
With rs
Do While Not .EOF
.Edit
![CreatedOn] = Now()
.Update
.MoveNext
Loop
End With
rs.Close
Set rs = Nothing
That said, this is an inefficient approach and running a query would be better, such as:
CurrentDb.Execute "UPDATE Contacts SET Contacts.CreatedOn = Now() WHERE Contacts.CreatedOn Is Null", dbFailOnError
Deleting Records
The same is true of Deleting records, the basic syntax being
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Contacts WHERE CreatedOn =#2/24/2022 10:08:51#")
With rs
Do While Not .EOF
.Delete
.MoveNext
Loop
End With
rs.Close
Set rs = Nothing
Once again, a query would be best:
CurrentDb.Execute "DELETE FROM Contacts WHERE CreatedOn = #2/24/2022 10:08:51#", dbFailOnError
Best Practices
Most of the common query best practices apply here.
Only Grab What You Need!
For instance, in my examples, it doesn’t make sense to return the entire table’s content if all I wanted was the First and Last names of the contacts! So
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Contacts")
would be better written
Set rs = CurrentDb.OpenRecordset("SELECT [FirstName], [LastName] FROM Contacts")
Only Grab What You Need (2)!
I’ve seen numerous times in forum posts where users loop through a recordset looking for a value and then performing some action on it, something like
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Contacts")
With rs
If .RecordCount <> 0 Then
Do While Not .EOF
If ![ContactId] = Me.ID Then
'Do Something
End If
.MoveNext
Loop
Else
'There are no records returned!
MsgBox "No records to process!", vbInformation Or vbOKOnly, "No Records"
End If
End With
rs.Close
Set rs = Nothing
This is incredibly inefficient! Instead, apply your filter directly on the OpenRecordset, like:
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM Contacts WHERE ContactId = " & Me.ID)
With rs
If .RecordCount <> 0 Then
Do While Not .EOF
'Now you will only loop through matching records
.MoveNext
Loop
Else
'There are no records returned!
MsgBox "No records to process!", vbInformation Or vbOKOnly, "No Records"
End If
End With
rs.Close
Set rs = Nothing
Be Aware Of Nulls
Many times we will use recordset loop to read data, say to push the data to another recordset, create an Excel WorkSheet, generate a Word Mail Merge, … just be careful as the returned value could easily be a NULL value which can break your code quite easily.
Thus, depending on the field definition and what you are doing exactly, code like
Dim sFirstName As String sFirstName = ![FirstName]
would be better off written as
Dim sFirstName As String sFirstName = Nz(![FirstName], "")
as sFirstName being a String variable cannot handle a NULL value ever being passed to it. Another option would be to dim sFirstName as a Variant instead.
Is A Recordset Loop The Right Tool For The Job?
This is an issue far too often overlooked.
People dive head first into coding without properly evaluating what it is they have chosen to do exactly. Far too often I see people using loops to run through entire tables to find matches and update each match one by one. This is simply horrible, often slow and progressively gets slower as the table gets more and more records over time.
Remember:
So think long and hard if you truly need a loop mechanism or whether a simple Insert/Update/Delete query wouldn’t be much better! (I’ll tell you a secret, it quite often is!).
Which Library Should I Be Using?
If you look over a lot of the samples that have been around for a long time (since nothing on this front has changed in years) or look at various posts, you will often see mention of setting the “Microsoft DAO X.X Object Library” which is used when dimming DAO variables, things like:
Dim db as DAO.Database Dim rs as DAO.RecordSet
While that was accurate many moons ago, since the release of Access 2007+ (15+ years now) you should really be using the newer “Microsoft Office XX.X Access database engine Object Library” (the ACE library) instead. So remove the DAO reference and instead include the ACE library instead. You don’t need to change anything in your code whatsoever, just switch the reference and compile your code again.
Inserting New Records
If you are looking for how you can Insert new records, you may wish to look over my article
Daniel,
Wow. I wish I had found your site sooner. I have been writing VBA for over 20 years, and I can still learn much from you. Your code is solid and your tips on optimization are excellent.