Introduction
This document is a guide to revising code that
uses Microsoft® Data Access Objects (DAO) into code that uses Microsoft
ActiveX® Data Objects (ADO). It also guides those who are writing new code
using ADO with the OLE DB Provider for Microsoft Jet (Microsoft Jet Provider).
It compares the general differences between DAO and ADO and details the mapping
of objects, properties, and methods from DAO to ADO. It also highlights
functional or semantic differences between similarly named methods or
properties.
This document assumes that you have already
decided to use ADO instead of DAO to access a Microsoft Jet database, or that
you are investigating what it will take to port your code from DAO to ADO. It covers some of the advantages of using ADO over
DAO. It describes many features of the Microsoft Jet Provider and demonstrates
how to use them with ADO. (The ADO documentation is meant to be
provider-neutral, so it lacks much of this information.) This document does not
attempt to provide in-depth detail on particular objects, properties, or
methods. Refer to the online documentation provided with DAO and ADO for
specific details on a particular item.
General Differences
Three distinct object models in ADO together
provide the functionality found in DAO. These three models are ADO (ADODB),
Microsoft ADO Extensions for DDL and Security (ADOX), and Microsoft Jet and
Replication Objects (JRO). The functionality of DAO is divided among these
three models because many applications will need just one of these subsets of
functionality. By splitting the functionality out, applications do not need to
incur the overhead of loading additional information into memory unnecessarily.
The following sections provide an overview of these three object models.
ADO: Data Manipulation
ADO enables your client applications to access
and manipulate data through any OLE DB provider. ADO contains objects for
connecting to a data source and reading, adding, updating, or deleting data.
{bmc ADOObjectModel.bmp}
The ADO Connection
object defines a session for a user of a particular data source. This differs
from DAO in which the Workspace object defines the
session for a user and the Database object defines the
data source.
The ADO Command object
is similar to the DAO QueryDef object in that both
objects can be used to execute an SQL statement against a data source.
Likewise, the Recordset object in both ADO and DAO can
be used to view the contents of a table or the results from executing an SQL
statement.
ADOX: Data Definition and Security
The ADOX model contains objects for data
definition (such as tables, views, and indexes) and creating and modifying
users and groups. With ADOX, an administrator can control database schema and
grant and revoke permissions on objects to users and groups.
{bmc ADOXObjectModel1.bmp}
The Catalog object is
the container for the data definition collections (Tables,
Procedures, and Views) and the
security collections (Users and Groups).
This differs from DAO in which the Database object
contains the data definition collections and the Workspace
object contains the security collections. Each Catalog
object is associated with only one Connection whereas a
DAO Workspace may contain multiple Databases.
The Table, Index, and Column objects in ADO are
roughly equivalent to the TableDef, IndexDef,
and Field objects in DAO. Each of these objects also
has a standard ADO Properties collection.
{bmc ADOXObjectModel2.bmp}
JRO: Replication
The JRO model contains objects, properties, and
methods for creating, modifying, and synchronizing replicas. It is designed
specifically for use with the Microsoft Jet Provider. Unlike ADO and ADOX, JRO
cannot be used with data sources other than Microsoft Jet databases.
The primary object in the JRO model is the Replica object. The Replica object is
used to create new replicas, to retrieve and modify properties of an existing
replica, and to synchronize changes with other replicas. This differs from DAO
in which the Database object is used for these tasks.
JRO also includes a JetEngine
object for two specific Microsoft Jet database engine features: compacting the
database and refreshing data from the memory cache.
{bmc JROObjectModel.bmp}
Getting Started
To run the code examples in this document, you
need references to the ADO, ADOX, and JRO type libraries in your database or
project. By default, new Microsoft Access 2000 databases have a reference to
ADO. However, to run these samples you'll need to add references to ADOX and
JRO. If you converted an existing database to Access 2000 or are programming in
Microsoft Visual Basic® or some other application, you will need to include all
of the references yourself.
To add these references in Access 2000:
1. Open a module.
2. From the Tools menu
select References…
3. From the list, select "Microsoft ActiveX
Data Objects 2.1 Library."
4. From the list, select "Microsoft ADO Ext.
2.1 for DDL and Security."
5. From the list, select "Microsoft Jet and
Replication Objects 2.1 Library."
6. Click OK.
To add these references in Visual Basic:
1.
Open a
project.
2. From the Project menu
select References…
3. From the list, select "Microsoft ActiveX
Data Objects 2.1 Library."
4. From the list, select "Microsoft ADO Ext.
2.1 for DDL and Security."
5. From the list, select "Microsoft Jet and
Replication Objects 2.1 Library."
6. Click OK.
If you include references to both ADO and DAO in
the same project, you need to explicitly specify which library to use when
declaring objects because DAO and ADO include several objects with the same
names. For example, both models include a Recordset
object, so the following code is ambiguous:
Dim rst as Recordset
To specify which object model you want to use,
include a qualifier as shown:
Dim rstADO As ADODB.Recordset
Dim rstDAO As DAO.Recordset
If the qualifier is omitted, Visual Basic for
Applications will choose the object from the model that is referenced first. So
if your list of references is ordered as follows in the References dialog box,
an object declared as Recordset with no qualifier would
be a DAO Recordset.
Visual Basic for Applications
Microsoft DAO 3.6 Object Library
Microsoft ActiveX Data Objects 2.1 Library
Microsoft ADO Ext. 2.1 for DDL and Security
Microsoft Jet and Replication Objects 2.1 Library
EmoticonEmoticon