DAO vs. ADO and ADODB in Microsoft Access Explained
Question details
The user needs to understand the functional differences between DAO, ADO, and ADODB libraries to properly manage and convert Microsoft Access databases.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Converting an Access MDB database to ACCDB format and managing database object libraries via VBA.
- Observed behavior
- When multiple libraries are present, overlapping database functionality causes naming conflicts unless objects are explicitly declared and qualified.
Open your Microsoft Access VBA editor and navigate to Tools > References to check which object libraries are currently loaded in your database project.
Differentiate and Explicitly Qualify Database Objects
Use explicit library prefixes in your VBA code to prevent conflicts between the overlapping features of DAO and ADO.
DAO (Data Access Objects) is natively optimized for Access and is the default library for modern ACCDB files. ADO (ActiveX Data Objects) provides broader connectivity to external data sources like SQL Server. ADODB is specifically the database object library component within ADO.
Because both libraries share common object names (like 'Recordset'), failing to specify which library you are calling will cause Access to default to whichever library is highest in the References list, often resulting in runtime errors.
Determine if your database relies primarily on local tables (DAO) or external server connections (ADO). For native ACCDB operations, DAO is the recommended default.
In your VBA modules, replace generic declarations like 'Dim rs As Recordset' with fully qualified names, such as 'Dim rs As DAO.Recordset' or 'Dim rs As ADODB.Recordset'.
When upgrading from MDB to ACCDB, ensure the 'Microsoft Office [Version] Access database engine Object Library' is checked in the References menu, as it replaces the older 'Microsoft DAO 3.6 Object Library'.

Analyze Your Exported Access Data with WPS Office
While Microsoft Access is required for complex VBA database management and handling DAO/ADO objects, WPS Office provides a lightweight, highly compatible, and free suite for all your document, spreadsheet, and presentation needs. You can seamlessly manage and analyze data exported from your databases without paying for a heavy subscription.
- 1. Export your Access data: In Microsoft Access, right-click the table or query you wish to analyze and export it as an Excel (.xlsx) or CSV file.
- 2. Open data in WPS Spreadsheet: Launch WPS Office and open your exported file in WPS Spreadsheet to view your database records in a clean grid.
- 3. Analyze and format: Use built-in pivot tables, formulas, and charts in WPS Spreadsheet to generate professional reports from your database exports.

Frequently Asked Questions
Can DAO and ADO be used together in the same Access database?
Yes, you can use both libraries simultaneously in the same Microsoft Access project. However, you must explicitly declare your variables (e.g., using DAO.Recordset or ADODB.Recordset) to prevent the VBA compiler from confusing the overlapping object names.
What happens if I do not qualify my Recordset declarations?
If you simply write 'Dim rs As Recordset', Access will automatically use the object from the library that appears highest in the VBA Tools > References list. This can cause unexpected 'Type Mismatch' errors if the priority does not match the library you actually intended to use.
Why did my older Access databases default to ADO instead of DAO?
Historically, Microsoft promoted ADO as the universal standard during the Access 2000 and 2002 eras (using the MDB format). However, starting with Access 2007 and the introduction of the ACCDB format, Microsoft returned to DAO (updated as the Access Database Engine) as the natively optimized library for Access.




