Gaining a Perspective on Microsoft Data Access Components

Integrated data management is the key to Access's success in the desktop RDBMS and client/server front-end market. Access and its wizards let you create basic data-bound forms, reports, and pages with minimal effort and little or no VBA programming. Linked tables provide dynamic access to a wide range of data sources. As your Access applications grow larger and more complex, automation with VBA code in class and public modules becomes essential. When networked Access applications gain more users, performance may suffer as a result of Jet record-locking issues or multiple connections to client/server back ends. Decreasing performance with increasing user load is a symptom of lack of scalability. Achieving scalability requires VBA code to manage your application's database connections. This advanced chapter shows you how to write the VBA code that's required to improve the scalability of Access front ends. You also learn how to use the Stream object to generate XML data documents from SQL Server 2000's FOR XML AUTO queries.

Access 2003 continues Microsoft's emphasis on "Universal Data Access" for VBA and Visual Basic 6.0 programmers. Microsoft wants Access developers to abandon Jet's Data Access Objects (DAO), Access 97's ODBCDirect, and the venerable Open Database Connectivity (ODBC) Application Programming Interface (API) in favor of a collection of Component Object Model (COM) interfaces called OLE DB and ActiveX Data Objects (ADO). To encourage Access power users and developers to adopt OLE DB and ADO, all traditional Microsoft database technologies (referred to by Microsoft as downlevel or legacy, synonyms for "obsolete") are destined for maintenance mode. Maintenance mode is a technological purgatory in which Microsoft fixes only the worst bugs and upgrades occur infrequently, if ever. In 1999, OLE DB, ADO, and, for Jet programmers, ActiveX Data Object Extensions (ADOX), became Microsoft's mainstream data access technologies.

Microsoft's primary goals for Universal Data Access were to

• Provide the capability to accommodate less common data types unsuited to SQL queries, such as directory services (specifically Active Directory), spreadsheets, email messages, and file systems

• Minimize the size and memory consumption of the dynamic link libraries (DLLs) required to support data access on Internet and intranet clients

• Reduce development and support costs for the multiplicity of Windows-based data access architectures in common use today

• Extend the influence of COM in competition with other object models, primarily Common Object Request Broker Architecture (CORBA) and its derivatives

This chapter introduces you to the fundamentals of Universal Data Access and Microsoft Data Access Components (MDAC). MDAC makes connecting to databases with OLE DB practical for Access users and developers. MDAC includes ADO and ADOX for conventional relational data, plus ADOMD for multidimensional expressions (MDX) to create and manipulate data cubes.

Note

Microsoft SQL Server Analysis Services (formerly OLAP Services) generates data cubes from online sources, such as transactional databases. Office 2003 installs Msadomd.dll and other supporting files for MDX and data cubes. Microsoft provides OLE DB for OLAP and the PivotTable Service to enable Excel 2003 PivotTables to manipulate data cubes. MDX and PivotTable services are beyond the scope of this book.

Redesigning from the Bottom Up with OLE DB

To accommodate the widest variety of data sources, as well as to spread the gospel of COM and Windows XP/2000+'s COM+, Microsoft's data architects came up with a new approach to data connectivity OLE DB. OLE DB consists of three basic elements:

• Data providers that abstract information contained in data sources into a tabular (row-column) format called a rowset. Microsoft currently offers native OLE DB data providers for Jet, SQL Server, IBM DB2, IBM AS/400 and ISAM, and Oracle databases, plus ODBC data sources. (Only Microsoft SNA Server installs the providers for IBM data sources.) Other Microsoft OLE DB providers include an OLE DB Simple Provider for delimited text files, the MSPersist provider for saving and opening Recordsets to files (called persisted Recordsets), and the MSDataShape provider for creating hierarchical data sets. The MSDataShape provider also plays an important role in ADP and when using VBA to manipulate the Recordset of Access forms and reports.

  Tip

  To see the list of OLE DB data sources installed on your computer, open the NorthwindCS.adp project, and choose File, Get External Data, Link Tables to start the Link Table Wizard. With the Linked Server option selected in the first dialog, click Next to open the Select Data Source dialog, and double-click the +Connect to New Data Source.odc file to open the second Wizard dialog. With the Other/Advanced item selected in the data source list, click Next to open the Data Link Properties dialog. The Providers page lists all currently installed OLE DB data providers. Click Cancel three times to return to the Database window.

  
  

• Data consumers that display and/or manipulate rowsets, such as Access application objects or OLE DB service providers. Rowset is the OLE DB object that ADO converts to a Recordset object.

• Data services (usually called OLE DB service providers) that consume data from providers and, in turn, provide data to consumers. Examples of data services are SQL query processors and cursor engines, which can create scrollable rowsets from forward-only rowsets. A scrollable cursor lets you move the record pointer forward and backward in the Datasheet view of a Jet or SQL Server query.

Figure 30.1 illustrates the relationship between OLE DB data providers, data consumers, and data services within Microsoft's Universal Data Access architecture. You should understand the relationships between these objects, because Microsoft commonly refers to them in ADO documentation, help files, and Knowledge Base articles. Database front ends written in C++ can connect directly to the OLE DB interfaces. High-level languages, such as VBA, use ADO as an intermediary to connect to OLE DB's COM interfaces. Msado15.dll, which implements ADO 2.x, has a memory footprint of about 327KB, about 60% of Dao360.dll's 547KB.

Figure 30.1. This diagram shows the relationships between front-end applications, ADO and ADOX, and OLE DB service and data providers.

ADO support files install in your \Program Files\System\Ado folder. If you're running Windows XP/2000+, the ADO support files are subject to Windows File Protection (WFP), which places a copy of the file in the DLL cache and prevents you from permanently deleting or moving the ADO support files. WFP also prevents unruly installation programs from overwriting the ADO support files with an earlier or corrupt (hacked) version.

Some ADO 2.x support file names have a 1.5 version number, as in Msado15.dll; the strange versioning of these files is required for backward compatibility with applications that used very early versions of ADO.

Note

MDAC 2.x also supports Remote Data Services (RDS, formerly Advanced Database Connector, or ADC). RDS handles lightweight ADOR.Recordsets for browser-based applications; RDS, which commonly is used for three-tier, Web-based applications, is required to make Data Access Pages (DAP) accessible safely over the Internet.

For more information on the use of RDS with DAP, see "Enabling Private or Public Internet Access," p. 1058.

Mapping OLE DB Interfaces to ADO

You need to know the names and relationships of OLE DB interfaces to ADO objects, because Microsoft includes references to these interfaces in its technical and white papers on OLE DB and ADO. Figure 30.2 illustrates the correspondence between OLE DB interfaces and the highest levels of the ADO hierarchy.

Figure 30.2. This diagram illustrates the correspondence between OLE DB interfaces and ADO Automation objects.

The OLE DB specification defines a set of interfaces to the following objects:

• DataSource objects provide a set of functions to identify a particular OLE DB data provider, such as the Jet or SQL Server provider, and determine whether the caller has the required security permissions for the provider. If the provider is found and authentication succeeds, a connection to the data source results.

• Session objects provide an environment for creating rowsets and isolating transactions, especially with Microsoft Transaction Server (MTS), which runs under Windows NT. The COM+ components of Windows 2000+ provide MTS services.

• Command objects include sets of functions to handle queries, usually (but not necessarily) in the form of SQL statements or names of stored procedures.

• Rowset objects can be created directly from Session objects or as the result of execution of Command objects. Rowset objects deliver data to the consumer through the IRowset interface.

ADO maps the four OLE DB objects to the following three top-level Automation objects that are familiar to Access programmers who've used ODBCDirect:

• Connection objects combine OLE DB's DataSource and Session objects to specify the OLE DB data provider, establish a connection to the data source, and isolate transactions to a specific connection. The Execute method of the ADODB.Connection object can return a forward-only ADODB.Recordset object.

• Command objects are directly analogous to OLE DB's Command object. ADODB.Command objects accept an SQL statement, the name of a table, or the name of a stored procedure. Command objects are used primarily for executing SQL UPDATE, INSERT, DELETE, and SQL Data Definition Language (DDL) queries that don't return records. You also can return an ADODB.Recordset by executing an ADODB.Command object.

• Recordset objects correspond to OLE DB's Rowset objects and have properties and methods similar to Access 97's ODBCDirect Recordset. A Recordset is an in-memory image of a table or a query result set.

The ADODB prefix, the short name of the ADO type library, explicitly identifies ADO objects that share object names with DAO (Recordset) and DAO's ODBCDirect (Connection and Recordset). For clarity, all ADO code examples in this book use the ADODB prefix.

Tip

To make ADOX 2.7 accessible to VBA, you must add a reference to Microsoft ADO Ext. 2.7 for DDL and Security to your application. Access 2003 doesn't add the ADOX reference automatically to new projects.

Comparing ADO and DAO Objects

Figure 30.3 is a diagram that compares the ADO and DAO object hierarchies. The ADO object hierarchy, which can consist of nothing more than an ADODB.Connection object, is much simpler than the collection-based object hierarchy of DAO. To obtain a scrollable, updatable Recordset (dynaset), you must open an ADODB.Recordset object on an active ADODB.Connection object.

Figure 30.3. This diagram compares the ADO and DAO object hierarchies.

Access VBA provides a DAO shortcut, Set dbName = CurrentDB(), to bypass the first two collection layers and open the current database, but CurrentDB() isn't available in VBA code for other members of Office 2003 or Visual Basic 6.0.

Note

Access VBA provides a similar ADO shortcut, CurrentProject.Connection, which points to a default ADODB.Connection object with the Jet OLE DB Service Provider for the current database. Unlike CurrentDB(), which is optional, you must use CurrentProject.Connection as the ADODB.Connection to the currently open database. If you try to open a new ADODB.Connection to the current database, you receive a runtime error stating that the database is locked.

Unlike DAO objects, most of which are members of collections, you use the New reserved word with the Set instruction to create and the Close method, the Set ObjectName = Nothing, or both statements to remove instances of ADODB.Connection, ADODB.Command, and ADODB.Recordset objects independently of one another. The Set ObjectName = Nothing instruction releases memory consumed by the object.

DAO supports a variety of Jet collections, such as Users and Groups, and Jet SQL Data Definition Language (DDL) operations that ADO 2.7 alone doesn't handle. ADOX 2.7 defines Jet-specific collections and objects that aren't included in ADO 2.x. The "Provider-Specific Properties and Their Values" section later in the chapter describes how to roll your own cross-reference table to aid in migrating your DAO code to ADO.

The most important functional difference between DAO and ADO is that ADO supports Web-based applications and DAO doesn't. Thus, DAP bind to ADODB.Recordset objects. The continuing trend toward Internet-enabling everything means that Windows database programmers must make the transition from ODBC, ODBCDirect, RDO, and DAO technologies to ADO and OLE DB, so this book covers VBA programming of ADO, not DAO, objects. ADO supports ODBC connections to shared-file and client/server RDBMSs with the Microsoft OLE DB Provider for ODBC (more commonly called by its beta code name, Kagera). ODBC introduces another layer into the database connection, so it's less efficient than OLE DB. The examples of this chapter use only native OLE DB providers.

Upgrading from ADO 2.5 and Earlier to Version 2.6+

ADO 2.x in this chapter refers collectively to ADO 2.1, 2.5, 2.6, and 2.7. Windows XP and Office 2003 install ADO 2.7, which includes type libraries for ADO 2.0, 2.1, 2.5, 2.6 for backward compatibility. Windows 2000 Service Pack (SP) 1 or later installs ADO 2.5 SP1, which includes type libraries for for prior versions. Installing the SQL Server 2000 Desktop Engine (MSDE2000) from the Office 2003 distribution CD-ROM or any other version of SQL Server 2000 upgrades Windows 2000's ADO 2.5 to 2.6. Version 2.7 is required only to support Intel's 64-bit Itanium processors. Upgrading from ADO 2.6 to 2.7 doesn't add new features or alter existing features.

Note

As mentioned in Chapter 27, "Learning Visual Basic for Applications," the default VBA reference for new ADP is ADO 2.1 for Access 2000 database format. If you change the default database version to Access 2002 in the Options dialog, the reference changes to ADO 2.5. Use of non-current references is required for backward compatibility with Access 2000 and 2002 ADP.

To review use of the VBA editor's References dialog, see "References to VBA and Access Modules," p. 1157.

Following are the new or altered ADO objects, properties, and methods in ADO 2.6+:

• Record objects can contain fields defined as Recordsets, Streams of binary or text data, and child records of hierarchical Recordset objects. Use of Record objects is beyond the scope of this book.

• Stream objects can send T-SQL FOR XML queries to SQL Server 2000 and return result sets as XML documents. Stream objects also are used with the Record object to return binary data from URL queries executed on file systems, Exchange 2000 Web Folders, and email messages. The "Programming Stream Objects" section, near the end of the chapter, provides a simple example of the use of a Stream object to return XML data from a FOR XML T-SQL query to a text box.

• Command objects gain new CommandStream and Dialect properties to support Stream objects, and a NamedParameters property that applies to the Parameters collection.

• Group and User ADOX objects add a Properties collection that contains Jet-specific Property objects. This chapter doesn't cover ADOX programming with VBA, because ADOX applies only to Jet databases.

Tip

If you're interested in learning more about ADOX, open the VBA Editor, type adox in the Ask a Question text box, select the ADOX methods option, click See Also in the "ADOX Methods" help page, and select ADOX API Reference in the list.