How to Fix "User-Defined Type Not Defined" for ADODB in Access
Question details
The user is encountering a VBA compile error when attempting to use ADODB objects because the required library reference is missing from the project.
- Product
- Microsoft Access
- Device & OS
- not provided
- Scenario
- Writing, compiling, or running VBA code in Microsoft Access that initiates database connections using ADODB.
- Observed behavior
- The VBA editor halts execution, throws a "User-defined type not defined" error, and highlights the ADODB variable declaration.
Verify whether your VBA project is designed to connect to an external SQL database (which requires ADO) or local Access tables (where the default DAO library is recommended).
Enable the Microsoft ActiveX Data Objects Library
Add the missing ADO reference in the VBA editor to resolve the compile error and allow ADODB objects to function.
Microsoft Access does not include the ADO library by default in newer versions, opting for DAO instead. You must manually activate it in the VBA References menu.
Press Alt + F11 while inside Microsoft Access to open the Visual Basic for Applications (VBA) editor.
In the top menu bar, click on "Tools" and select "References" from the dropdown menu.
Scroll down the alphabetical list of available references and check the box next to "Microsoft ActiveX Data Objects 6.1 Library" (or the highest version available on your system).
Click "OK" to apply the changes, then navigate to "Debug" > "Compile [Project Name]" to ensure the error is completely resolved.
Switch from ADODB to DAO
Replace ADO objects with Access's default Data Access Objects (DAO) library, which handles local queries and multivalue fields natively.
Need a Free Alternative to Microsoft Office? Try WPS Office
If you frequently encounter complex VBA errors in Microsoft Office, you might appreciate a lighter, highly compatible alternative. WPS Office offers a powerful suite with native macro support, a familiar interface, and seamless performance—completely free.
- 1. Download the Installer: Visit the official WPS Office website and click the free download button for your operating system.
- 2. Install WPS Office: Run the downloaded setup file and follow the quick on-screen instructions to complete the installation.
- 3. Open Your Files: Double-click any of your existing Office documents or macro-enabled spreadsheets to open them directly in WPS Office.
Frequently Asked Questions
Why does Microsoft Access use DAO instead of ADO by default?
DAO (Data Access Objects) is optimized specifically for the Access database engine (Jet/ACE). It natively understands Access-specific features, such as multivalue fields and complex query designs, making it faster and more reliable for local database tasks.
What is the primary difference between ADO and DAO in VBA?
DAO is designed primarily for querying and managing local Access databases. ADO (ActiveX Data Objects) is a broader technology better suited for connecting to external data sources, such as SQL Server or web databases.
Can I use both DAO and ADO in the same Access database?
Yes, you can check both library references in the VBA editor. However, you must explicitly declare your objects (for example, using 'Dim rs As DAO.Recordset' or 'Dim rs As ADODB.Recordset') to prevent ambiguous reference errors in your code.
