Microsoft Office 2000/Visual Basic Programmer's Guide

Using ADO to Work with Access Databases

This section covers how to use ADO to perform some of the more common data access programming tasks for Access databases. Because the DAO programming model has been used for many years to work with Access databases and is familiar to many Office developers, this section describes the mapping between many DAO objects, properties, and methods and those in ADO. It also highlights areas where there are differences between apparently similar methods or properties. It is beyond the scope of this chapter to provide in-depth detail on particular ADO and DAO objects, properties, or methods. Refer to the online documentation provided with DAO and ADO for specific details.

There are three distinct ADO object models that together provide the functionality found in DAO. These three models are known as:

• Microsoft ActiveX Data Objects 2.1 (ADODB)
  
  
• Microsoft ActiveX Data Objects Extensions for DDL and Security 2.1 (ADOX)
  
  
• Microsoft Jet and Replication Objects 2.1 (JRO)

ADO functionality was divided among these three models because there are many applications that will need only a subset of the full set of functionality. By selecting only the object models required for a given task, you are only required to load into memory the objects necessary for that task.

Each of these three ADO object models corresponds to the following sets of functionality.

• Data Manipulation   The Microsoft ActiveX Data Objects 2.1 (ADODB) object model enables your client applications to access and manipulate data in a database server through any OLE DB provider. In particular, to work with Access databases (.mdb files), you should use the Microsoft Jet 4.0 OLE DB Provider.
  
  
• Data Definition and Security   The ADO Extensions for DDL and Security 2.1 (ADOX) object model contains objects, properties, and methods for creating and modifying the structure of databases, tables, and queries. ADOX also lets you create and modify user and group accounts for databases secured with user-level security, and grant and revoke permissions on objects. The ADOX object model is designed to work with any OLE DB provider that supports its interfaces. At the time of this writing, you can use ADOX for data definition in both Access and SQL Server databases; however, you can use ADOX to work with security only in Access databases. This chapter discusses the Data Definition Language (DDL) features of ADOX. For information about the security features of ADOX, see Chapter 18, "Securing Access Databases."

  The top-level object in the ADOX object model is the Catalog object. It provides access to the Tables, Views, and Procedures collections, which are used to work with the structure of the database, and also provides the Users and Groups collections, which are used to work with security. Each Catalog object is associated with only one Connection to an underlying data source.

  The ADOX model differs somewhat from the DAO model. DAO has a Workspace object that defines a session for a user but does not define the data source. The DAO Workspace object is also the container for the Users and Groups collections that are needed to work with security accounts. In DAO, a Workspace may be created and security information may be retrieved or modified without opening a database. When you work with the ADOX Catalog object, you must specify a connection to a database before you have access to security information.

• Replication and Jet Engine Services   The Microsoft Jet and Replication Objects 2.1 (JRO) object model contains objects, properties, and methods that let you create, modify, and synchronize replicas. It is designed specifically for use with the Microsoft Jet 4.0 OLE DB Provider. Unlike ADO and ADOX, JRO can't be used with data sources other than Access databases.

  The top-level object in the JRO object model is the Replica object. The Replica object is used to create new replicas, retrieve and modify properties of an existing replica, and to synchronize changes with other replicas. This differs from DAO, where the Database object is used for these tasks.

  The JRO object model also contains the freestanding JetEngine object, which provides access to two Jet database engine-specific features: compacting databases and refreshing data from the cache for connections to any kind of Access database.

  This chapter discusses only some of the features of the JetEngine object. For information about the replication features of JRO, see Chapter 16, "Multiuser Database Solutions."