ADO Components for Microsoft Access Developers

Microsoft Access developers are likely to reference any of three separate ADO object models: the ADODB library, the ADOX library, and the JRO library. However, I will use the term ADO to refer collectively to all three models.

• The ADODB library is a small, lightweight library that contains core objects and offers the basics for making connections, issuing commands, and retrieving recordsets; it also enables recordset navigation. You can use this library to perform basic database maintenance tasks, such as modifying, adding, and deleting records. Most of the library elements apply to other databases as well as Access databases.

• The ADOX library supports data definition language and security issues. It offers objects that let you examine and control a database's schema. For example, it lets you create tables and relations. The model includes support for referential integrity and cascading updates and deletes. It also offers the Procedures and Views collections, as well as the Users and Groups collections for user-level database security. Many features of the ADOX library especially target Microsoft Access database files. For example, Access developers programming data definition tasks for SQL Server databases are likely to find the SQL-DMO library more suitable than the ADOX library.

• The JRO library enables Jet database replication. Database replication is especially important for applications built with Access database files (.mdb files), because this capability can vastly extend their availability. This library is exclusively for Access database files. (For more information, see Chapter 13 in the previous edition of this book, which covered database replication in depth, including how to program replication via JRO.)

As mentioned in the chapter introduction, ADO has been readily available to Access developers since Access 2000. The following ADO version numbers correspond to MDAC versions. The ADO 2.1 version was widely available with the release of Access 2000. The 2.5 and 2.6 versions were current versions around the time of the release of Access 2002. Installing MSDE 2000 also installs ADO 2.6 on a computer. Access developers programming data access for any version of SQL Server 2000 require at least ADO version 2.6. The Microsoft Access team thoroughly tested Access 2003 with Windows XP, which includes the 2.7 version of the ADODB and ADOX libraries and the 2.6 version of the JRO library. This book uses the ADO libraries released with Windows XP. The most recently updated version of the JRO library shipped with the MDAC 2.6, but the other two ADO libraries were updated in the MDAC 2.7 version.

Note

Choosing a library version isn't always as simple as picking the reference with the highest version number. For example, if you need the same application to run on multiple machines, which have ADO versions 2.5 through 2.7, you should use the 2.5 version to eliminate the possibility of a failure due to a missing reference on computers without the 2.6 or 2.7 version of the ADODB library. Alternatively, you can upgrade the ADO version on machines running the 2.5 or 2.6 versions. This latter approach is especially appealing when some of your machines require an ADO version with a higher number, such as the 2.6 version for interfacing with SQL Server 2000. You can obtain information about how to download ADO libraries as part of various MDAC versions from www.microsoft.com/data/download.htm.

If you start a new blank database with Access 2003, the Access database file will have a reference to a version of the ADODB library. The version will depend on what is installed on your computer. If you have more than one version of the ADODB library, Access 2003 creates a reference to the earliest ADODB library version. Depending on what is installed on a computer, this might not be the most recent version of the ADODB library. Therefore, you might need to re-specify the reference to the ADODB library if you want a version other than the earliest release. A blank Access 2003 database does not initialize references to either the ADOX or JRO libraries. In addition, the sample Northwind database file ships with a reference to the DAO 3.6 library. If you want to program this sample database with ADO, you must add a reference to one or more of the appropriate ADO libraries.

You can manually specify references to the ADO libraries from the Visual Basic Editor (VBE) of an Access database file. Choose Tools, References, and then select the ADO libraries that you want your application to reference. Figure 1-1 shows the References dialog box with all three ADO libraries selected. The reference to the Microsoft ActiveX Data Objects 2.7 Library is for the ADODB library. The next reference to the Microsoft ADO Ext. 2.7 for DDL and Security is for the ADOX library. The last reference to the Microsoft Jet and Replication Objects 2.6 Library is for the JRO library.

Although the ADODB library reference installs by default, users can remove the reference from their workstation settings. You should account for this in your applications by either issuing a warning about installing the ADODB library when it is needed or by creating the reference programmatically. You can create references programmatically using the References collection in the Access library. You also can use the References collection to verify the ADO library references on a workstation.

OLE DB Providers and ADO Object Model Summary

OLE DB providers make ADO powerful by providing a consistent programming interface for different data sources. Providers operate in a consumer-provider framework. A consumer application—for example your procedure in an Access application—references an OLE DB provider. The data or service provider then assumes control and implements some capability as specified by the syntax for referencing the OLE DB provider. After the provider completes its task, control returns to the consumer application. Depending on the provider and the syntax you use, your procedure can open a connection to a data source, expose a subset of rows from the data source, or save a subset of rows from a data source to a local file.

There are two broad categories of OLE DB providers: data providers and service providers. Data and service providers offer easy ways to extend the kinds of data sources that you can reach with ADO programs. For example, ADO offers a variety of OLE DB data providers for traditional databases, including those for Jet, SQL Server, Oracle, and general ODBC data sources. In addition, Microsoft makes available other data providers that enable Access 2003 developers to process such nontraditional sources as pages and folders at a Web site. Just as with ODBC drivers, you can obtain OLE DB data providers from Microsoft or third-party sources. Table 1-1 lists selected OLE DB providers available from Microsoft, along with a brief description of when to use each one. Service providers extend how ADO can work with data through providers such as the Microsoft Cursor Service for OLE DB and the Microsoft OLE DB Persistence Provider.

Table 1.1: Selected Microsoft-Supplied OLE DB Providers

Provider Name	Description
OLE DB Provider for Microsoft Jet	Use this data provider to connect to Jet 4 databases and to data from Corel Paradox, dBASE, Microsoft Excel, Microsoft FoxPro, and other ISAM data sources.
Microsoft OLE DB Provider for SQL Server	Use this data provider to connect to databases on SQL Server.
Microsoft OLE DB Provider for Oracle	Use this data provider to connect to Oracle 7.3 and Oracle 8 databases.
Microsoft OLE DB Provider for ODBC	Use this data provider to connect to any ODBC-compliant data source for which you do not have a more specific OLE DB provider.
Microsoft OLE DB Persistence Provider	Use this service provider for saving data to a local file from ADO objects and for retrieving data from local files for storage in ADO objects.
Microsoft Cursor Service for OLE DB	Use this service provider to expand the functionality of native cursors for a service provider. For example, the Cursor Service for OLE DB can dynamically construct an index to speed searches with the Find method for ADO recordsets and allow specification of sort criteria for recordsets.
Microsoft OLE DB Provider for Internet Publishing	Use this data provider to access resources served by Microsoft FrontPage and Microsoft Internet Information Services (IIS). This provider enables you to manipulate and open Web site pages programmatically from an Access application.

A major objective for linking to OLE DB providers is to implement data access through the properties, methods, and events of ADODB objects. See the latest version of the ADODB library objects, shown in Figure 1-2. The chart depicts major collection objects along with nested collections and objects. The main object collections are Connections, Commands, Recordsets, Records, and Streams. Each of these major collections consists of individual objects. So a Connections collection consists of one or more Connection objects. Selected object collections nest hierarchically within the major objects. For example, a Command object can have a Parameters collection populated by one or more Parameter objects. The parameters for a command allow your application to specify the behavior of the command at run time. This chapter and the next will drill down on selected ADODB objects, along with their properties, methods, and events that are important for typical data access tasks.