Microsoft Office 2000/Visual Basic Programmer's Guide

Choosing ADO or DAO for Working with Access Databases

Although ADO provides access to a broader variety of data sources than DAO, and even exposes some features of the Jet 4.0 database engine that aren't available from DAO, there are some limitations to using ADO against Access databases that require you to continue to use DAO:

In the Access object model, the new Recordset property of a Form object can be used to request or specify a Recordset object for the data being browsed in a form. If you request the Recordset object for the current form in an Access database, Access always returns a DAO Recordset object. Therefore, you must continue to use DAO code to work with the Recordset object that is returned.
When you use the Recordset property to set the Recordset object of a Form object to a Recordset object you created, if you set the Form object to an ADO Recordset object, the data will be read-only. If you want the data to be writable, you must set the Form object to a DAO Recordset object.
To read and set database properties in an Access database, and to read and set certain table properties, such as the Description and Filter properties, you must continue to use DAO code.
It's not possible to exchange information between ADO and DAO code. For example, if a DAO procedure returns a Recordset object, there is no way to translate or pass that DAO Recordset object to ADO code, and vice versa — an ADO Recordset object can't be read by or translated to DAO. However, this doesn't mean that ADO can't work with saved database objects, such as tables and queries, that were created with DAO, and vice versa. But it does mean that although ADO and DAO can coexist in the same project, you can't use ADO code to work with objects returned by preexisting DAO code. You must either continue to use DAO code, or rewrite those procedures by using ADO code.

For more information about working with the Recordset property of an Access Form, and working with database properties, see Chapter 5, "Working with Office Applications." For more information about working with table properties, see "Creating and Modifying Access Tables" later in this chapter.

If you are updating an existing DAO data access component, or developing new data access components that will only be working with Access databases or other data sources supported by the I-ISAM drivers of the Jet database engine, you can continue to use DAO by establishing a reference to the Microsoft DAO 3.6 object library. All DAO code written for DAO 3.5 (with the exception of code that defines user-level security for code modules in Access 2000 databases) will continue to work with DAO 3.6.

There are only a small number of Jet database engine features that require ADO: passive shutdown, a setting that allows you to exclude all new connections and exclude current users once they exit the database; access to a schema rowset that lists all the users who are currently logged into the database; and control over page- or record-level locking. For information about these features, see Chapter 16, "Multiuser Database Solutions." If you don't require access to these Jet engine features and don't require other ADO-specific features, you can safely continue to write code that uses DAO until you encounter these requirements.

If you are creating new data access components, you should consider using ADO for its advanced features, simplified object model, and support for multiple data sources. In particular, ADO is a good choice if you are developing an Access database solution that will later be upgraded to SQL Server — you can write your ADO code to minimize the number of changes that will be required to work against a SQL Server database. In addition, ADO is a good choice for developing new data access components that work with SQL Server, multidimensional data, and Web applications.

The following table summarizes most of the functionality that is available when using Data Access Objects (DAO) and how that functionality compares to what is available in the Microsoft ActiveX Data Objects 2.1 (ADO), Microsoft ADO Extensions for DDL and Security 2.1 (ADOX), and Microsoft Jet and Replication Objects 2.1 (JRO) object models.

Note Unlike DAO, ADO and ADOX objects can perform the marked actions in databases other than Jet, as long as the providers for those databases support that action.

Functionality	DAO	ADO¹	ADOX²	JRO (.mdb only)
Create Recordset objects	X	X
Create new databases	X		X³
Edit database properties	X
Edit start-up properties	X	X⁴
Create custom database properties	X
Create tables	X		X
Set and edit table properties	X		X⁵
Set and edit field properties	X		X
Create table relationships	X		X³
Support for new Jet 4.0 SQL commands and syntax⁶		X	X
Support for new Jet 4.0 Decimal data type			X
Support for Compression attribute for field data			X
Create and edit saved queries that are accessible only through code⁶			X³
Create and edit saved queries that are accessible from both the Access Database window and code	X
Compact/encrypt database	X			X⁷
Refresh cache	X			X⁷
Make database replicable	X			X⁸
Make database replicas	X			X⁸
Synchronize replicas	X			X⁸
Synchronize replicas with SQL Server replicas				X⁸
Support for connection control to prevent current users from reopening a shared database after they exit		X
Retrieve a Recordset object that lists information about users in a shared database		X
Programmatic control over using page- or record-level locking for Recordset objects and SQL DML statements		X

¹Uses the Connection object to reference to database.

²Uses the Catalog object to reference database.

³Only available when working with Access database files (.mdb). Future versions of the SQL Provider may provide this functionality when working with SQL Server views from Access project files (.adp).

⁴Only available when working with Access project files (.adp).

⁵Limited support. For more information about which table properties are supported, see "Setting Additional Table Properties" later in this chapter.

⁶The native Jet 4.0 SQL has been extended to support more ANSI 92 SQL commands and syntax. These commands and syntax are only supported when they are run from ADO code. They aren't supported by DAO or from the SQL View window in Access. However, at the time of this writing, no stored queries created with ADOX, regardless of whether they use these commands and syntax, can be viewed or run from the Access Database window. For more information, see "Creating and Modifying Stored Queries" later in this chapter.

⁷Uses the JetEngine object to reference database.

⁸Uses the Replica object to reference database.

This chapter provides an overview of ADO and focuses on using ADO to create data access components that use the Microsoft Jet 4.0 OLE DB Provider to work with Access databases, with information about when DAO is required. DAO conversion issues and coexistence techniques are covered in "Using ADO to Work with Access Databases" later in this chapter. Using ADO for multiuser database applications is covered in Chapter 16, "Multiuser Database Solutions." For more information about using DAO, see Microsoft DAO 3.6 Help and the Microsoft Jet Database Engine Programmer's Guide, Second Edition (Microsoft Press, 1997).