Programming Microsoft Access Version 2002 (core reference)

Read an Excerpt

Chapter 2: Data Access Models: Part I

DAO was the main data access model for Access developers from Access 1.0 through Access 97. Therefore, it is finely tuned to the Jet database engine. This feature is both a strength and a weakness. While DAO can still perform a few selected tasks for the Jet engine that are not possible or are very difficult to do with ADO, accessing data outside Jet using DAO is very awkward. ADO, however, was conceived as a universal data access language, and it is highly suitable for developing solutions based on the Web or on a LAN (local area network). DAO does not process data when used in a Web-based solution. For these reasons, DAO will not play a role in this book. For information on DAO code, see the Access online documentation and Microsoft’s Support Online (support.microsoft.com/directory/). Click the Searchable Knowledge Base link for documents that discuss typical problems and their associated workarounds. Many of the articles include DAO code samples.

The subject of data access models is vast. To make this discussion more digestible, I’ve broken it into two chapters. This chapter and Chapter 3, "Data Access Models: Part II," focus primarily on data access for Jet with the ADO object model. The two chapters also include some coverage of ADO with other database engines—particularly Microsoft SQL Server. Numerous programming samples show you how to accomplish typical database chores.

This chapter begins with an overview of ActiveX Data Objects and then moves on to discuss the Connection, Recordset, and Field objects. In Chapter 3, you will learn about the Command and Parameters objects and the Errors collection. Chapter 3 also devotes considerable attention to techniques for managing access to the files on a Web server, as well as demonstrating the features of the new ADO Record and Stream objects. This topic also serves as a platform for exploring the new functionality of the Recordset object. Finally, Chapter 3 examines the subject of ADO event programming. Later chapters in this book will build on the information presented in Chapters 2 and 3 and will cover additional ADO topics, such as database replication, remote database access, and multiuser security.

ADO Overview

• Microsoft ActiveX Data Objects 2.5 Library
• Microsoft ADO Ext. 2.5 for DDL and Security
• Microsoft Jet and Replication Objects 2.5 Library

Recall that the ADODB library reference installs by default, but references for the other two libraries do not appear unless you specifically add them. By segmenting data access into three libraries, Access offers a smaller footprint for applications that do not require all three. Another major component of the Access 2002 data access strategy is reliance on OLE DB providers, which work with ADO to offer access to traditional data sources as well as new ones, such as e-mail and file directories. This vastly expands the power of database programming.

Although the ADODB library reference installs by default, users can remove the reference from their workstation settings. You should account for this in your custom applications by either issuing a warning about installing the ADODB library when you need it or automatically creating the reference programmatically. Chapter 9, "Class, Form, and Report Modules," provides a code sample that demonstrates how to add library references programmatically with the References collection in the Access library.

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. The nonhierarchical design of this library makes it easy for beginning programmers to use.

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. The elements of the Procedures and Views collections comprise what Access developers normally refer to as queries. The Catalog object is a container for the tables, groups, users, procedures, and views that compose a schema.

The JRO library enables Jet database replication. Access 2002 supports database replication with both Jet and SQL Server databases. Database replication is especially important for applications built with Access database files (.mdb files) because this capability vastly extends their availability. Chapter 13, "Replicating Databases," covers database replication in depth, showing you how to program it with the JRO library and how to manage replica collections with the Replication Manager in Microsoft Office Developer Edition.

Before you can use any of the ADO libraries, you must create a reference to at least one of them. You do this from the Visual Basic Editor (VBE) window by using the Tools-References command. Figure 2-1 shows the References dialog box with all three libraries selected. Notice that the screen shot uses the 2.6 versions of these libraries. If you do not load the SQL Server 2000 Desktop Engine shipping with Office XP, you will have the 2.5 versions available. Use them instead of the 2.6 versions.

Click to view graphic

Figure 2-1 You can use the References dialog box to add ADO libraries to an application.

While it might be more convenient to select all three ADO libraries, you can conserve resources by selecting just the libraries that you need. Experiment with the selections for your applications and the machines on which they run to determine what’s optimal for your environment. If you have a production application that runs on many types of machines, you should conserve resources for other application requirements. Keep in mind that you can load references at run time with the References collection from the Access Application object.

One major advantage that ADO offers is an event model. ODBCDirect, which is a part of the DAO 3.5 and DAO 3.6 libraries that targets programmatic access to SQL Server, permits asynchronous operations, but ADO provides events. This frees an application from polling an object and checking its StillExecuting property. Instead, you can create event handlers to respond to events whenever they occur. Unlike the simple event procedures that you invoke for forms and reports, ADO event programming is an advanced capability. You will typically use ADO events with unbound forms, and your application is likely to supply values for controls from an ADO recordset. Chapter 3 will discuss ADO event programming in more detail.

OLE DB data providers make ADO powerful by providing a consistent programming interface for different data sources. Data providers, as well as other types of providers, offer an easy way to extend the kinds of data sources that you can reach with ADO programs. Access 2002 ships with 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 2002 developers to process such nontraditional sources as pages and folders at a Web site, as well as Windows NT 4 and Windows 2000 directory services. Just as with ODBC drivers, you can obtain OLE DB data providers from Microsoft as well as third-party sources. Table 2-1 lists the OLE DB data providers available from Microsoft, along with brief descriptions of when to use them.

Table 2-1 Microsoft-Supplied OLE DB Data Providers

Enter the provider name from Table 2-1 into Access 2002’s Help to obtain more information about it. Standard information includes the purpose of the provider, as well as the syntax for referencing it. Code samples presented in this chapter will demonstrate the use of these providers.

At least two remaining issues affect the use of providers with ADO. First, ADO makes service providers (as well as data providers) available. Service providers can consume data from a data provider and offer services not directly available from a data provider, such as query processing. At the time of this writing, Microsoft offers three ADO service providers, which I’ll describe momentarily.

Second, the Microsoft Cursor Service for OLE DB enhances native cursor services available from data providers. Microsoft calls this a service component (as opposed to either a service provider or a data provider) because the Cursor Service for OLE DB focuses on the cursor element of a data provider. Table 2-2 includes a short summary of the three Microsoft service providers and the service component.

Table 2-2 Microsoft-Supplied ADO Service Providers and Components

The providers and component in Table 2-2 have many uses. This chapter specifically illustrates several of them. Because I emphasize other topics in the chapter, data shaping (and its provider) does not receive as much coverage. A hierarchically shaped recordset can more parsimoniously represent data in a one-to-many relationship than a standard SQL join can. If you find relational database representations unsatisfactorily expressing links between elements, you might find hierarchical-shaped recordsets useful. If you need to know more about this topic, enter the phrase data shaping into a Help prompt in the VBE to open this section in the Visual Basic Help file.

The ADODB object library has grown from seven objects in the release of Access 2000 to nine objects with Access 2002. The two new object classes are Record and Stream objects. While the number of collections stayed the same, you can now use the Fields and Properties collections with the new Record object. See the latest version of the ADODB library objects, shown in Figure 2-2. The object model still remains a relatively flat one in comparison to DAO. This makes it fast, lightweight, and easy to learn. In addition, ADO has vastly superior extensibility than DAO. For traditional database development tasks, you are still likely to find yourself using the objects provided with Access 2000. However, for those of you working in emerging nontraditional areas, such as file services management and Web file management, the new Record and Stream objects will have special appeal....

Read More