Working with Access VBA Recordsets Using DAO or ADO: Some More Key Points with VBA Recordsets
As a continuation of another article where I covered the first three key points of cheats and tips to keep in mind when using the 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 included DAO versus Teen, testing the cursor movement methods through Record sets and the default OpenRecordset types of methods.
Here are four more key points:
1. Search and you will find
There are two method options for finding matching data values. You can use the Look for gold Find (FindFirst, FindLast, FindNext, FindPrevious) methods.
Tea Look for The method is faster as it indexes the ‘indexed’ key fields in a table and no queries or SQL statements compared to the Find methods that scan criteria for a set of records, making it slower, but more relaxed, to find answers to your data.
A good practice when searching for records is to test for a match first before iterating through known records by using No coincidence property to avoid run-time errors. For instance:
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 AddNew and To update methods wrapped around the collection of fields in a known recordset.
However, the cursor (that is, where the insertion point is positioned) is not always where the new record instance resides when saving (updating) the record.
Therefore, to ensure that if you want to continue encoding the newly added record, you will need to tell the system where to place the cursor to continue editing using the Marker and Last modification properties. For instance:
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 your code
One of my personal bug bears with poor coders is a lack of cleanup after your own mess! When creating objects, learn how to dispose of them properly when you no longer need them.
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 refer to the object reference elsewhere in your procedures and if the scope of the object variables is not correct or changes it, this can cause reference problems.
Two places to get rid of your object variables; one within the code routine and the other within the error handling routines as well. For instance:
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. Compile queries in VBA code?
In Access, you create a query using the query interface (known as QBE – Sample Grid Query), and Microsoft Access learns how to compile this query for the first time when you run and save the query.
The VBA passcode uses the QueryDef object, which is the way to create a query object in code and compile it as well, which means it’s a bit faster to run and run.
This is a better practice than creating SQL statements on the fly and then executing them, but as with various focus methods, there are tradeoffs to running query objects against SQL statements that I’m not going to 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 is worth taking the time to examine the properties, methods, and events of both libraries.