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 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 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.

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.

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

“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.


 
Copyright © SS64.com 1999-2020
Some rights reserved