Working with Access VBA Recordsets Using DAO or ADO: Some More Key Points with VBA Recordsets
Following up on another article where I covered the first three key points of cheating and tips to keep in mind when using Access VBA DAO or ADO libraries that call the Recordset object, here are four more key points to keep in mind.
As a summary, the first three points mentioned include SCOUNDREL versus Teentesting cursor movement methods via record sets and the default open recordset types of methods.
Here are four more key points:
1. Seek and you shall find
There are two method options for finding matching data values. You can use the look for gold Find (Find First, Find Last, Find Next, Find Previous) methods.
Tea look for The method is faster as it indexes the ‘indexed’ key fields in a table and not queries or SQL statements compared to the Find methods that parse criteria for a set of records, making it slower, though more relaxed, to find answers to your data.
A good practice when looking up records is to first test for a match before iterating through known records using the no match property to avoid runtime errors. For example:
rs.FindFirst "Country = ""UK"""
If Not rs.NoMatch Then
'carry out some VBA execution here...
End If
2. Creating a new record, where is the cursor?
To add a new record, use the Add new and update methods wrapped around the collection of fields to a known recordset.
However, the cursor (ie where the insertion point currently is) is not always where the new record instance resides when the record is saved (updated).
So, to ensure that if you want to continue encoding the newly added record, you’ll need to tell the system where to place the cursor to continue editing using the marker and Last modification properties. For example:
rs.AddNew 'Fields here are set to values...
rs.Update rs.Bookmark = rs.LastModified
'Continue working with the new record here...
3. Take out the trash! Close and clean up your code
One of my personal issues with poor coders is the lack of cleaning up after your own mess! When creating any object, learn how to properly dispose of it when you no longer need it.
In fact, VBA is quite friendly in this regard and will take out the garbage for you, but that’s not the point. You may need to look up the object reference elsewhere in your procedures and if the scope of the object variables is not correct or you change it, this can cause reference problems.
Two places to get rid of your object variables; one inside the code routine and the other inside the error handling routines as well. For example:
Sub WorkingWithRecords
On Error Goto Err_WWR
Dim db as Database
Dim rs As Recordset
Set db = CurrentDb()
Set rs = db.OpenRecordset("Customers")
'Some code here...
rs.Close 'Close the opened table.
db.Close
Exit_WWR:
Set rs = Nothing 'Set objects to nothing-ness.
Set db = Nothing
Exit Sub Err_WRR: 'Error handler here...
Resume Exit_WWR
End Sub
4. Do you compile queries into VBA code?
In Access, you create a query using the query interface (known as QBE – Sample Query by Grid), and Microsoft Access learns to compile this query for the first time when you run and save the query.
Accessing the VBA code uses the QueryDef object, which is the way to create a query object in code and also compile it, means it’s a bit faster to get up and running.
This is better practice than creating SQL statements on the fly and then executing them, but as with various approach methods, there are trade-offs to executing query objects in SQL statements that I won’t discuss here.
The object reference is set by the CreateQueryDef method:
Dim qd As QueryDef
Set qd = CreateQueryDef(str_Name, ste_SQL)
There you have it, 4 more key points for DAO and ADO when coding VBA in Microsoft Access. It’s worth spending time looking at the properties, methods, and events of both libraries.