Friday 22 June 2012

2. Recordset types in VBA


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.


Solution:

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)






No comments:

Post a Comment