ADO Architecture

With Access 2000, Microsoft introduced a more generic set of data engine object models to provide references not only to objects stored by the ACE but also to data objects stored in other database products such as SQL Server. These models are called the ADO architecture. With Access 97 (version 8.0), you could download the Microsoft Data Access Components from the Microsoft website to be able to use the ADO model. Access 2000 and Access XP (2002) provided direct support for ADO with built-in libraries and direct references to key objects in the model from the Access Application object. As noted earlier, Access 2003, Access 2007, and Access 2010 provide a default reference to the DAO library, not ADO.

Because these models are designed to provide a common set of objects across any data engine that supports the ADO, they do not necessarily support all the features you can find in the DAO architecture that was specifically designed for the ACE. For this reason, if you are designing an application that will always run with the ACE, you are better off using the DAO model. If, however, you expect that your application might one day "upsize" to an ActiveX data engine such as SQL Server, you should consider using the ADO architecture as much as possible. If you create your Access application as an Access project (.adp) linked to SQL Server, you should use only the ADO models.

The two major models available in the ADO architecture. The basic ActiveX Data Objects (ADODB) model lets you open and manipulate recordsets via the Recordset object and execute action or parameter queries via the Command object. The ADO Extensions for DDL and Security (ADOX) model allows you to create, open, and manipulate tables, views (non-parameter unordered queries), and procedures (action queries, parameter queries, ordered queries, functions, triggers, or procedures) within the data engine Catalog object (the object that describes the definition of objects in your database). You can also examine and define Users and Groups collections defined in the Catalog object with ADOX.

To use the ADODB model, you must instruct Visual Basic to load a reference to the Microsoft ActiveX Data Objects Library. For objects in the ADOX model, you need the Microsoft ADO Extensions for DDL and Security Library. (You should normally find only one version on your computer. If you find multiple versions in the list, select the latest one.) To verify that your project includes these references, open any module in Design view and click References on the Tools menu. If you don't see the check boxes for these libraries selected at the top of the References dialog box, scroll down the alphabetical list until you find the library you need, select its check box, and click OK to add the reference. Access 2010 does not automatically create a reference to the ADODB library for you in any new database that you create.

Note that there are some objects in common between DAO, ADODB, and ADOX. If you use multiple models in an application, you must be careful to qualify object declarations. For example, a Recordset object type in the DAO model is DAO.Recordset, whereas a Recordset in the ADODB model is ADODB.Recordset. You cannot freely interchange a DAO recordset with an ADODB recordset-they are completely different objects.

The link to ADODB and ADOX is via the CurrentProject.Connection property. After you open an ADODB.Connection object, you can work with other collections, objects, and properties within the ADODB model. Likewise, by establishing an ADOX.Catalog object and setting its Connection property, you can work with any collection, object, or property within the ADOX model.

For all objects within either ADODB or ADOX, you must first establish a base object (connection or catalog, respectively). For example:

Dim cn As ADODB.Connection, rst As New ADODB.Recordset
Set cn = CurrentProject.Connection
rst.Open = "tblContacts", cn

or

Dim catThisDB As New ADOX.Catalog, tbl As ADOX.Table
Set catThisDB.ActiveConnection = CurrentProject.Connection
Set tbl = catThisDB.Tables("tblContacts")

Note: One of the extensive examples at the end of this tutorial uses ADO exclusively to manipulate recordsets in the Conrad Systems Contacts sample database.