Provided by Allen Browne. Last updated April 2010.
VBA Traps: Working with Recordsets
This article highlights ten common traps with DAO recordsets in VBA code.
Most of the traps yield no syntax error; they are bugs lying dormant in your code until particular conditions are met. Your program then fails, or returns inaccurate results.
The DAO and ADO libraries both have a Recordset object, but with different methods, properties, and options.
DAO is the native Access library (what Access itself uses), whereas ADO is a more generic library (now superseded by the vastly different ADO.NET library.)
Different versions of Access default to different libraries. See Solving Problems with Library References for details.
This article assumes DAO recordsets.
To ensure your code works reliably:
- Set your references to use just the library you want.
- If you must use both, list your main one first.
- Disambiguate by specifying which library's recordset you want. Use:
Dim rs As DAO.Recordset
Dim rs As Recordset
There are different types of DAO recordset, with different methods.
When you OpenRecordset() on a query or attached table, Access defaults to a Dynaset type (dbOpenDynaset). When you OpenRecordset() on a local table, it defaults to a Table type (dbOpenTable.)
The Table type has different methods (e.g. Seek instead of FindFirst), but it cannot be used with attached tables. So if you later split your database so the tables are attached, the code fails when you use a method that no longer applies.
Always specify the type you want. Dynaset guarantees your code will work for all queries and tables, local and attached. Example:
Set rs = db.OpenRecordset("Table1", dbOpenDynaset)
While working with a Microsoft Access database using linked Microsoft SQL Server tables, you may receive the following error message:
You must use the dbSeeChanges option with OpenRecordSet when accessing a SQL Server table that has an IDENTITY column
Solution: Set rst = CurrentDb.OpenRecordset("SELECT * From tblName", dbOpenDynaset, dbSeeChanges)