The Recordset object is an essential component in Access development, but it often trips up even experienced developers. Susan Harkins explains 12 common Recordset pitfalls you can anticipate and avoid.
As an Access developer — or even as a power user — you won't get far if you can't work with the Recordset object. But it's easy to go astray, and there are a number of mistakes that even experts make. Sometimes, we're just careless. Often, we're unaware of a subtle nuance between the two object libraries, Data Access Objects (DAO) and ActiveX Data Objects (ADO). No matter how experienced you are, it's difficult to commit every little behavior and requirement to memory. Luckily, you can avoid some of the most common mistakes if you just plan for them.
Note: This information is also available as a PDF download.
#1: Eliminate ambiguous objects
ADO is the default object library for Access, but DAO is still available. If you use a DAO Recordset, you must explicitly reference it. Referencing both libraries is likely to generate an ambiguous object error. To avoid this type of error, give priority to the library that your code references the most.
In the Visual Basic Editor, choose References from the Tools menu, highlight the main library, and click the up button to position it above the other object library.
ADO is the main object library is this project.
When both libraries are referenced and both libraries support an object, VBA will assign the library with the highest priority.
#2: Usurp implicit library references
Giving the main library priority is good (#1). Explicitly referencing each object's library when you declare the object is better:
Dim rst As DAO.Recordset Dim rst As ADODB.Recordset
Combine #1 with #2 for the best results.
#3: Avoid moving violations
Moving through the records in a Recordset is a common task, but doing so comes with a few hazards. Using any move method will generate an error if the Recordset object is empty. The most reliable check uses a simple If statement before executing a move method:
If Not(rst.BOF And rst.EOF) Then
The BOF property returns True when the current position is before the first record. Similarly, EOF returns True when the current position is just beyond the last record. If both properties are True simultaneously, the Recordset is empty.
#4: Expose an erroneous record count
The previous technique uses BOF and EOF to expose an empty Recordset before code can generate an error. Another way to avoid this type of error is to use the RecordCount property to count the records as follows:
If rst.RecordCount <> 0 Then
This statement is a bit unpredictable with ADO because some ADO Recordset objects return -1 as the count. Use a static or keyset cursor to return a true record count in an ADO Recordset.
When using DAO, you must remember to populate (fully) the Recordset before counting, as follows:
If rst.RecordCount <> 0 Then
DAO populates with records as they are needed, and consequently, its RecordCount property considers only the records already accessed. ADO, on the other hand, defines the RecordCount property as the total number of records, period. That's why this second check for an empty Recordset is less flexible and can be troublesome.
Whether using RecordCount to check for the existence of records or to count records, you must remember the differences between DAO and ADO:
- When using DAO, populate (fully) the Recordset before checking for an empty Recordset or a total count.
- When using ADO, use a static or keyset cursor to return the actual record count.
#5: You can't move beyond the first or last record
In tip #3, I told you to use the BOF and EOF properties to test for an empty Recordset. You'll also use them individually when moving through records. Moving beyond the first or last record returns an error. For instance, the following code returns an error when the MoveNext method moves beyond the last record:
If rst![fieldname] = value Then
If you're moving forward, use EOF to inhibit the error that occurs when you move beyond the last record, as follows:
Do While Not rst.EOF
If rst![fieldname] = value Then
As long as EOF equals False, meaning the current position is a valid record, this loop executes the If statement. Once the EOF property is True, the check eliminates the error that would otherwise occur by bypassing the MoveNext method in the loop.
#6: Avoid an endless loop
In the previous tip, a Do loop moves through records. If you omit the MoveNext method, the loop becomes stuck in an endless loop. The condition for ending the loop is never met and the position within the loop never moves. Always make sure you include a MoveNext method when you mean to cycle through a set of records. Failing to move in any type of looping structure can have the same result — this isn't just a Do loop consideration.
#7: Accommodate nested Recordset objects
Access 2007 introduces the multivalue field. That means despite what we've all been taught, you can now store multiple values in a single field. Whether you decide to use this enhancement is up to you. In fact, DAO and ADO use the Recordset object to represent a multivalue field. However, the possibility means that any Recordset that includes such a field must be able to handle the multiple values. In other words, a Recordset field could actually be another Recordset object. When encountering a multivalue field, you must add a loop to cycle through the values.
#8: Avoid errors when there's no match to a search
You can apply criteria to find specific records using a Find or Seek methods. However, when the method fails to find a matching record, it returns an error. You must ensure that a match exists before trying to move to the matching record, using the NoMatch property as follows:
If Not rst.NoMatch Then
...do something...End If
(DAO uses the FindFirst, FindNext, and FindLast methods.) If there's no matching record, the code skips the If statement and continues, without generating an error.
#9: Force the newest record to be the current record
When you add a record to a Recordset, you might expect that record to become the current record. If that's your expectation, your code will fail to return the expected results because that is not what happens. If you want to work with the new record, you must force the newly added record to become the current record by setting a bookmark immediately after the Update method as follows:
rst.Update rst.Bookmark = rst.LastModified
#10: Avoid IS incompatibility in ADO
ADO's Find method doesn't support the IS operator. That means when you're searching or excluding NULL, you shouldn't use IS as follows:
rst.Find "LastName Null"
It's the exact opposite in DAO, which requires IS:
rst.Find "LastName IS Null"
This difference is bound to cause trouble if you don't know about it. Because it's so subtle, it could take a long time to find.
#11: ADO doesn't recognize the And operator
DAO lets you search for records using a complex criteria string. For instance, the following statement works just fine against a DAO Recordset:
rst.FindNext "SupplierID = 10 And CategoryID = 4"
For better or worse, the statement run against an ADO Recordset (using Find instead of FindNext) will return an error because ADO doesn't support the And operator in this way.
To apply multiple criteria to a search task in ADO, use the Filter property instead:
rst.Filter = "SupplierID = 10 AND CategoryID = 4"
DAO also has a Filter property, but it works differently. DAO works against subsequent Recordsets, while ADO works on the current Recordset.
#12: Close Recordset objects
When the code's finished with a Recordset object, close it as follows:
An open Recordset, especially in older versions of Access, can hang things up. In short, you might not be able to close the application.
Postscript: DAO vs. ADO
Within the Visual Basic world, there are two Recordset objects: Recordset and Recordset1 (new with 2007). It's important to remember that neither the DAO nor ADO versions of these objects are interchangeable, as they support different methods, properties, and events. That can be confusing if you think ADO is just an improved or later upgrade of DAO. In fact, the two libraries solve different problems. DAO was designed specifically for the Microsoft Jet database engine. ADO was designed for OLE DB providers and can be much simpler and more flexible than DAO. However, DAO is almost always more efficient when working directly with Jet.
Know how you plan to use a Recordset and then use the most appropriate library. My best advice is to choose one library and use it exclusively when possible. The similarities and differences are too numerous to list here, but you can find information online:
- Recordset Object Properties, Methods, and Events
- DAO Reference
- Appendix A: DAO to ADO Quick Reference