Microsoft Office 2000/Visual Basic Programmer's Guide

OLE DB and ADO

ADO is Microsoft's new high-level programming interface to a broad variety of data sources. ADO is designed as an easy-to-use, application-level programming interface to Microsoft's newest and most powerful data access technology, OLE DB.

OLE DB is Microsoft's system-level data access interface to data across the organization. OLE DB is an open specification designed to build on the success of Open Database Connectivity (ODBC) by providing an open standard for accessing an even broader variety of data. Whereas ODBC was created to access only relational databases, OLE DB is designed for both relational and nonrelational data sources, including mainframe and hierarchical databases; e-mail and file system stores; text, graphical and geographical data; custom business objects; and more.

OLE DB consists of a collection of COM interfaces to various database management system services. These interfaces define the underlying architecture for the creation of software components that implement these services. As an Office solution developer, the primary thing you need to know about OLE DB is that it provides access to a particular data source by using a COM component called a data provider, which is often referred to as an OLE DB provider. You can think of an OLE DB provider as being much like an ODBC driver for a particular data source, with two exceptions: OLE DB providers can support access to a broader variety of data sources, and similar ADO code can be used to work with data exposed by any OLE DB provider. If the system your solution is running on has the appropriate OLE DB provider installed (as well as the core ADO and OLE DB components, which are installed by Office or by downloading and installing the Microsoft Data Access Components [MDAC] from the Universal Data Access Web site at http://www.microsoft.com/data/), your solution can use ADO code to work with the data exposed by that provider.

The primary providers you will be working with as an Office developer are the Microsoft Jet 4.0 OLE DB Provider and the Microsoft OLE DB Provider for SQL Server:

• The Microsoft Jet 4.0 OLE DB Provider is used to work with the Microsoft Jet database engine, which provides access to data in Access databases as well as providing database access to the installable Indexed Sequential Access Method (I-ISAM) data supported by Jet: tabular data stored in Excel workbooks, Outlook or Microsoft Exchange mail stores, dBASE tables, Paradox tables, Lotus 1-2-3 spreadsheets, text, and HTML files.
  
  
• Microsoft OLE DB Provider for SQL Server provides access to databases stored on Microsoft SQL Server versions 6.5 and 7.0.

In addition, for data sources that don't currently have OLE DB providers, you can use the Microsoft OLE DB Provider for ODBC drivers to access data made available by the broad range of ODBC relational database drivers available today. For more information about the data formats supported by Office 2000 and the OLE DB providers installed with Office 2000, see FormatsAndProviders.doc in the ODETools\V9\Samples\OPG\Appendixes folder on the Office 2000 Developer CD-ROM.

The ADO programming model supports key features for building desktop, client/server, and Web-based applications, including the following:

• Independently created objects. Unlike DAO or Remote Data Objects (RDO), you no longer have to navigate through a hierarchy to create objects because most ADO objects can be independently created. This allows you to create and track only the objects you need, and also results in fewer ADO objects and thus a smaller memory footprint.
  
  
• Batch updating, which helps improve performance by locally caching changes to data, then writing them all to the server in a single update.

  Note   Although the Microsoft Jet 4.0 OLE DB Provider supports batch updating, there is no need to use batch updating with Access databases to improve performance because the Jet database engine runs locally — you won't see a performance gain when performing batch updates against an Access database. However, you should see a performance gain when performing batch updates against a SQL Server database because SQL Server's query processor can optimize and perform multiple SQL statements in a single operation on the server without requiring additional network round-trips.

• Support for stored procedures with in/out parameters and return values against a SQL Server database.
  
  
• Different cursor types, including the potential for support of back-end – specific cursors. For information about cursor types, see "Working with Records" later in this chapter.
  
  
• Support for limits on the number of returned records and other query goals for performance tuning.

  Note   The ADO MaxRecords property of a Recordset object, which is designed to limit the number of returned records, is not supported by the Microsoft Jet 4.0 OLE DB Provider or the Microsoft Access ODBC driver. However, if you require this functionality, you can use the TOP n predicate in a Jet SQL statement, or set the TopValues property of a query that is saved in an Access database.

• Support for multiple recordsets returned from stored procedures or batch statements.

  Note   Multiple recordsets can be returned for SQL Server databases. Access databases can't return multiple recordsets because Jet SQL statements don't support multiple SELECT statements.

• Free-threaded objects for efficient Web server applications.