INFO: Using ActiveX Data Objects (ADO) via Microsoft Access 97

ID: Q184233

The information in this article applies to:

ActiveX Data Objects (ADO), versions 1.5, 2.0, 2.1 SP2
Microsoft Access versions 7.0, 97

SUMMARY

This article discusses what is necessary to take advantage of ActiveX Data Objects (ADO) with Microsoft Access 95 and 97.

NOTE: Microsoft Technical Support does not provide support for ADO used in conjunction with Microsoft Access 95 and 97. This article is provided for informational purposes.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to the "Building Applications with Microsoft Access 97" manual.

MORE INFORMATION

ADO and Microsoft Access 95/97

Both Microsoft Access 95 and 97 are built around the Jet database engine, whose functionality is exposed through Data Access Objects (DAO).

While it is possible to use ADO within Microsoft Access 95 and 97 just as you would any other OLE Automation Server, Microsoft Access itself is optimized for Data Access through DAO.

The only advantage of using ADO in lieu of DAO is when you request data through an OLE DB provider that does not have a corresponding ODBC Driver.

Using ADO also allows you to access a greater variety of datastores than DAO since ODBC accesses relational data only. OLE DB exposes non-relational data to ADO, allowing you to access and manipulate more data sources using ADO/OLE DB than possible with DAO/ODBC.

For example, the OLE DB Provider for Microsoft Index Server or the OLE DB Provider for Microsoft Active Directory allows you to access data through ADO. As ADO imposes no assumptions or restrictions on SQL Syntax, it offers an ideal object model for exposing data that otherwise would not be available to ODBC, and via ODBCDirect, Jet/DAO.

Installing and Redistributing ADO with Microsoft Access

The ActiveX Data Objects Library is available as part of the Microsoft Data Access Components (MDAC). MDAC includes ODBC, the OLE DB Provider for ODBC Drivers, ADO, the Remote Data Service, and various other ODBC Drivers. For more information on the MDAC Redistribution, please see the REFERENCES section.

You can use the Setup Wizard included in the Microsoft Office 97 Developer Edition (ODE) to include and run the MDAC redistribution setup program. Running the MDAC redistribution program installs MDAC components to other computers.

You must add the MDAC redistribution file to your application and then you may run it automatically using the options provided in the ODE Setup Wizard.

Referencing the ADO Type Library

Create a new Microsoft Access database.

In the Module window, click New.

From the Tools menu, click References.

In the References dialog box, select the Microsoft ActiveX Data Objects Library from the Available References list.

NOTE: If the Microsoft ActiveX Data Object Library does not appear in the Available References list, make sure you have installed the Microsoft Data Access Components (MDAC) available at the following World Wide Web URL:

http://www.microsoft.com/data/

Single versus Double Quotes with ADO

ADO imposes no restrictions on the syntax used to generate a recordset or execute a statement. These restrictions come from the underlying native OLE DB provider, such as the OLE DB Provider for Index Server, or the OLE DB Provider for ODBC Drivers.

When you use the OLE DB Provider for ODBC Drivers, the ODBC driver itself defines valid SQL syntax for use with ADO. You must keep this in mind when migrating SQL syntax to ADO.

The SQL statement below runs successfully when DAO is used to issue commands to the Jet database engine:


   INSERT INTO Authors ( Au_ID, Author ) VALUES (  54, "Record # 54" )

With ADO however, this syntax generates one of the following three errors:

0x80040E10

-or-

-2147217904

-or-

Too few parameters. Expected 1.

Instead, use this syntax:


   INSERT INTO Authors ( Au_ID, Author ) VALUES (  54, 'Record # 54' )

Note that this is only one example, and specific to DAO/Jet. Depending upon the underlying provider that ADO uses and the datastore it exposes, the syntax used to query and return data can vary.

Consider this example used to return data from the OLE DB Provider for Microsoft Index Server (that runs on a computer that has both Microsoft Index Server and Microsoft Internet Information Server installed):


   SELECT Filename, Path, Size, Write FROM Scope('"/"') WHERE
   CONTAINS('"32MB RAM"') > 0

The preceding statement retrieves the names of documents exposed by the Web server that contain the phrase "32MB RAM."

REFERENCES

For additional information, please refer to:

http://msdn.microsoft.com/library/techart/msdn_redistmdac.htm

You may also download the MDAC components from the following World Wide Web URL:

http://www.microsoft.com/data/

Additional query words:

Keywords : kbAccess95 kbAccess97 kbADO kbADO200 kbDatabase kbGenInfo kbGrpVBDB kbGrpMDAC kbDSupport kbADO210sp2 kbMDAC210SP2
Version : WINDOWS:1.5,2.0,2.1 SP2,7.0,97
Platform : WINDOWS
Issue type : kbinfo