Here are 3 key points to keep in mind and keep in mind when coding with Access VBA using DAO or ADO to connect with other databases.
There are common pitfalls and useful tips to be aware of when using Access VBA code working with the recordsets object and in this article (part 1) I will cover the first three points right here and now.
1. Which library to use DAO or ADO?
If you’ve been using Access VBA for a while, you may have come across two library options (DAO or ADO). Both libraries support the recordset object but have different members (that is, properties, methods, and events).
DAO is the native reference to Microsoft Access databases, and is the natural choice when coding for other Access database systems, since it is an implicit connection to Access tables.
However, ADO is used for other types of databases outside of the Microsoft Access framework when you want to connect to external data sources, and is considered 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 can cause some confusion. If this is the case, the order in which they appear in the reference list prevails. To avoid ambiguous references, the best practice (if you insist on having both) is to be explicit in your coding. For example,
Dim rs as Recordset – you can reference any of the libraries 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 don’t code to fetch and test the collection of recordset objects and you try to navigate inside this array, it will throw an error.
methods like MoveFirst, MoveLast, MoveNextgold MovePrevious will cause errors when no records were found. So be sure to add a Yew test before iterating through records using something like:
If it is not rs.BOF and it is not rs.EOF, then
3. Default Recordset Types May Vary Between Table and Query Connections
If you use the OpenRecordset method for a query or attached table (a table not stored locally), the default argument type is dbOpenDynaset compared to a local single table that uses dbOpenTable.
If you developed your Access database in a separate environment where the default is dbOpenTable and then split the Access database into a front-end and back-end environment, your code will not run.
Therefore, change the default value of dbOpenTable to dbOpenDynaset.For example:
Set rs = db.OpenRecordset(“MyLocalTable”, dbOpenDynaset)
I have other pitfalls and tips to be aware of with Access VBA DAO and ADO and will post them in my next article, so stay tuned!
