DAO vs ADODB Recordsets
Here is the text of a post to the Microsoft Access Developer's forum that explains best practices for DAO vs ADODB.
QUESTION:
I have an Access application (2016) that has three types of tables:
(1) The ones I created in Access
(2) Linked tables to another Access database
(3) Linked tables to an ODBC connected non-Access database
When I use the query builder, can I assume that for items (1) and (2) above, it automatically uses DAO, and that for the linked ODBC database (3) it uses ADADB for the underlying query?
Also, when I use VBA, I define my database and record sets as follows, allowing Access to choose the default:
Dim db As Database
Dim recIn As Recordset
Does it choose automatically DAO for types (1) and (2) above, and ADADB for the ODBC linked database?
I tend to stay away from ADODB because in the past, it sometimes didn't read all the records in the tables when I would do a "IF recIn.EOF" and had other anomalies that were frustrating.
Regards,
Rich Locus, Logicwurks, LLC
http://www.logicwurks.com
ANSWER:
When you use the query builder, it uses neither DAO nor ADO.
In VBA, you can use either DAO or ADO for (1), (2) and (3). I'd use DAO for all three.
Dim db As Database is only DAO, for ADO doesn't have a Database object. To avoid possible confusion, you should always specify whether a recordset is DAO or ADO:
Dim recIn As DAO.Recordset
Just for consistency, I always use
Dim db As DAO.Database
although, as mentioned above, it is not essential to include DAO in this declaration.
Regards, Hans Vogelaar (http://www.eileenslounge.com)