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 Microsoft Data Access Components (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. The references that are set must match the application programs installed on the machine, if there is a mismatch then the code will fail to compile or run.
Object references allow a lot of additional functionality to be quickly added to an Access database but that somes at a cost of needing to have those extra applications installed (often the same version of the application) on all the machines where the database is used.
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
Make 100 copies of the current record with DAO.
When attached to an access form, this will add 100 new rows with values equal to the matching items on the current form. This simple example could also be written as an UPDATE query. A query will always run faster than a loop in code.
Dim db As DAO.Database Dim rs As DAO.Recordset Dim CreateNew As Integer Set db = CurrentDb Set rs = db.OpenRecordset("T_Assets", dbOpenDynaset, dbSeeChanges) For CreateNew = 1 To 100 Step 1 rs.AddNew rs("A_Asset_Type_ID") = Me!txtAsset_Type rs("A_description") = Me!txtDescription rs("A_Supplier_ID") = Me!txtSupplier_ID rs("A_Received_Date") = Me!txtReceived_Date rs("A_Model_No") = Me!txtModel_No rs("A_Order_No") = Me!txtOrder_No rs("A_Status_ID") = Me!cmbStatus_ID rs("A_Cost") = Me!txtCost rs.Update Next 'clean up rs.Close db.Close Set rs = Nothing Set db = Nothing
“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.