How-to: DAO and ADO

TLDR: For most purposes you should stick with the default DAO.

Data Access Objects (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.

ActiveX Data Objects (ADO)

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.

What are Object References?

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.

Where are they set?

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)
  FullPath: C:\PROGRA~2\COMMON~1\MICROS~1\VBA\VBA7.1\VBE7.DLL
  
  
• Microsoft Access 16 Object library Access (9.0)
  FullPath: C:\Program Files (x86)\Microsoft Office\Office16\MSACC.OLB
  
  
• OLE Automation stdole (2.0)
  FullPath: C:\Windows\SysWOW64\stdole2.tlb
  
  
• 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.

Enumerating References

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

Examples

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

Related Links:

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.