TLDR: For most purposes you should stick with the default DAO.
In all recent versions of Access, the default data access library is named Microsoft Office 1x.0 Database Engine Object Library
This provides Data Access Objects (DAO)
DAO is the native data access method for the Jet (Microsoft Access) data tables and also works for SQL Server.
ActiveX Data Objects (ADO) a part of MDAC was originally intended as a replacement for DAO, but it never fully met this goal and so in 2007 Microsoft reversed its roadmap and made DAO the preferred and recommended data access library for Access.
ADO "Active X Data Objects" is an industry friendly connection to almost all types of database. If your application needs to access multiple data sources such as Word documents, email messages or non conventional ISAM or ODBC databases then ADO may be a good choice.
ADO does have many useful features which you can read about here, but these come at a cost of reduced performance. DAO is almost always faster.
Many older/existing databases may still be using ADO and so it is still fully supported. It is also possible to mix both DAO and ADO libraries in same application. To use ADO you will need to manually add a reference to enable it, you may also need to install MDAC if not already installed.
References allow you to select another application's objects making them available in your code by setting a reference to that application's object library.
Open the VBA editor with Alt + F11 then choose Tools, References.
Only 4 are libraries required – if you create a new blank database, these will be set automatically.
- Visual Basic for Applications VBA (4.2)
- Microsoft Access 16 Object library Access (9.0)
FullPath: C:\Program Files (x86)\Microsoft Office\Office16\MSACC.OLB
- OLE Automation stdole (2.0)
- Microsoft Access 16 Access Database engine Object Library DAO (12.0)
FullPath: C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE16\ACEDAO.DLL
To add ADO, select Microsoft ActiveX Data Objects x.x Library from the list
The priority of References does make a difference, the four above should be the at the top of the list - highest priority.
Documenting a list of references used is important particularly if anything non-standard has been enabled. Invalid, 'broken' references will prevent your VBA code from running.
Print a list of current references to the VBA editor immediate window:
Dim ref As Reference ' Enumerate through References collection. For Each ref In References ' Check IsBroken property. If ref.IsBroken = False Then Debug.Print "Name (version): " & ref.Name & " (" & ref.Major & "." & ref.Minor & ")" Debug.Print "FullPath:", ref.FullPath Debug.Print "" Else Debug.Print "GUIDs of broken references:" Debug.Print ref.Guid End If Next ref
“The only meaningful definition of a “generalist” is a specialist who can relate his own small area to the universe of knowledge” ~ Peter Drucker
Create an ODBC connection to SQL Server.
connectionstrings.com - connection strings for various hosts.
Microsoft Data Access Components (MDAC) 2.8 SP1 - provides ADO, download from 2005.