Here are 3 key points to note and be mindful of when coding with Access VBA using either DAO or ADO to connect with other databases.
There are common traps and useful tips to look out for when using Access VBA code working with the recordsets object and in this article (part 1) I will cover the first three points here and now.
1. Which library to use DAO or ADO?
If you have been using Access VBA for a while, then you may have come across two library options (DAO or ADO). Both libraries support the Recordset object but they have different members (i.e. properties, methods and events).
DAO is the native reference to Microsoft Access databases and is the natural choice when coding to other Access database systems as it an implicit connection to tables in Access.
ADO however is used for other database types outside the Microsoft Access framework when wanting to connect to external data sources and is deemed the flexible of the two.
There are pros and cons between the two library types but sometimes they can exist together in the same database environment which may cause some confusion. If this is the case, the order they appear in the references list takes precedence. To avoid ambiguous referencing, the best practice (if you insist on having both) is to be explicit in your coding. For example,
Dim rs as Recordset – can refer to either library but using
Dim rsDAO As DAO.Recordset
Dim rsADO As ADODB.Recordset – will make both objects explicit.
2. Trying to move between records when there are no records
If you do not code to catch and test the recordset object collection and you attempt to navigate within this array, it will throw an error.
Methods like MoveFirst, MoveLast, MoveNext, or MovePrevious will cause errors when no records have been found. Therefore, make sure you add an If test before iterating through records using something like:
If Not rs.BOF And Not rs.EOF Then
3. Recordset default Types can vary between table and query connections
If you use the OpenRecordset method to a query or an attached table (a table not locally stored), the default argument type is dbOpenDynaset compared to a local single table which uses dbOpenTable.
If you have developed you Access database in a stand-alone environment where the default is dbOpenTable and then split the Access database into a front and back-end environment, the code will fail to run.
Therefore change the default from dbOpenTable to dbOpenDynaset.For example:
Set rs = db.OpenRecordset(“MyLocalTable”, dbOpenDynaset)
I have other traps and tips to be mindful of with Access VBA DAO and ADO and will publish these in my next article so keep a look out!
