Gaining a Perspective on Microsoft Data Access
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,
, 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
load is a symptom of lack of
. 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
object to generate XML data documents from SQL Server 2000's
FOR XML AUTO
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
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
Access power users and developers to adopt OLE DB and ADO, all traditional Microsoft database technologies (referred to by Microsoft as
, synonyms for "obsolete") are destined for maintenance mode. Maintenance mode is a technological purgatory in which Microsoft fixes only the worst
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 (
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
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.
Microsoft SQL Server Analysis Services (formerly OLAP Services) generates data cubes from online sources, such as transactional databases. Office 2003
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
came up with a new approach to data connectivity—OLE DB. OLE DB consists of three basic elements:
that abstract information contained in data sources into a tabular (row-column) format called a
. 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
), 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.
To see the list of OLE DB data sources installed on your computer, open the NorthwindCS.adp project, and choose
et External Data,
ink 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.
that display and/or manipulate rowsets, such as Access application objects or OLE DB service providers.
is the OLE DB object that ADO converts to a
OLE DB service providers
data from providers and, in
, 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
unruly installation programs from overwriting the ADO support files with an earlier or corrupt (hacked) version.
Some ADO 2.x support file
have a 1.5 version number, as in Msado15.dll; the
versioning of these files is required for backward compatibility with applications that used very early versions of ADO.
MDAC 2.x also supports Remote Data Services (RDS, formerly Advanced Database Connector, or ADC). RDS handles lightweight
for browser-based applications; RDS, which commonly is used for three-
, 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,
"Enabling Private or Public Internet Access,"
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:
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.
objects provide an environment for creating rowsets and isolating transactions,
with Microsoft Transaction Server (MTS), which runs under Windows NT. The COM+ components of Windows 2000+ provide MTS services.
objects include sets of functions to handle queries, usually (but not
) in the form of SQL statements or names of stored procedures.
objects can be created directly from
objects or as the result of execution of
objects deliver data to the consumer through the
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:
objects combine OLE DB's
objects to specify the OLE DB data provider, establish a connection to the data source, and isolate transactions to a specific connection. The
method of the
object can return a forward-only
objects are directly analogous to OLE DB's
objects accept an SQL statement, the name of a table, or the
of a stored procedure.
objects are used primarily for executing SQL
, and SQL Data Definition Language (DDL) queries that don't return records. You also can return an
by executing an
to OLE DB's
objects and have properties and
similar to Access 97's ODBCDirect
is an in-memory image of a table or a query result set.
prefix, the short name of the ADO type library, explicitly identifies ADO objects that share object names with DAO (
) and DAO's ODBCDirect (
). For clarity, all ADO code examples in this book use the
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
the ADO and DAO object hierarchies. The ADO object hierarchy, which can consist of nothing more than an
object, is much simpler than the collection-based object hierarchy of DAO. To obtain a scrollable, updatable
(dynaset), you must open an
object on an active
Figure 30.3. This diagram compares the ADO and DAO object hierarchies.
Access VBA provides a DAO shortcut,
, to bypass the first two collection
and open the current database, but
isn't available in VBA code for other
of Office 2003 or Visual Basic 6.0.
Access VBA provides a similar ADO shortcut,
, which points to a default
object with the Jet OLE DB Service Provider for the current database. Unlike
, which is optional, you must use
to the currently open database. If you try to open a new
to the current database, you receive a runtime error
that the database is locked.
Unlike DAO objects, most of which are members of collections, you use the
reserved word with the
instruction to create and the
, or both statements to remove instances of
objects independently of one another. The
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
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
and client/server RDBMSs with the Microsoft OLE DB Provider for ODBC (more commonly called by its beta code name,
). 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+
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.
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,
"References to VBA and Access Modules,"
Following are the new or
ADO objects, properties, and methods in ADO 2.6+:
objects can contain fields defined as
s of binary or text data, and child records of hierarchical
objects. Use of
objects is beyond the scope of this book.
objects can send T-SQL
queries to SQL Server 2000 and return result sets as XML documents.
objects also are used with the
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
object to return XML data from a
T-SQL query to a text box.
objects gain new
properties to support
objects, and a
property that applies to the Parameters collection.
ADOX objects add a
collection that contains Jet-specific
objects. This chapter doesn't cover ADOX programming with VBA, because ADOX applies only to Jet databases.
If you're interested in learning more about ADOX, open the VBA Editor, type
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.