Programming Microsoft Access Version 2002 (core reference)

Overview

Whether you're extending an existing database or developing a sophisticated custom solution, this detailed developer's guide can help you tap all the power, performance, and extensibility of Access 2002. Crack the covers to get the expert insights and rich, adaptable code samples you need to write extensible database applications quickly and intuitively. Along the way, you'll discover in-depth information about developer-empowering technologies such as Microsoft ActiveX Data Objects (ADO), Jet SQL, Microsoft SQL ...
See more details below
Available through our Marketplace sellers.
Other sellers (Paperback)
  • All (15) from $1.99   
  • New (1) from $0.00   
  • Used (14) from $1.99   
Close
Sort by
Page 1 of 2
Showing 1 – 10 of 14 (2 pages)
Note: Marketplace items are not eligible for any BN.com coupons and promotions
$1.99
Seller since 2014

Feedback rating:

(1827)

Condition:

New — never opened or used in original packaging.

Like New — packaging may have been opened. A "Like New" item is suitable to give as a gift.

Very Good — may have minor signs of wear on packaging but item works perfectly and has no damage.

Good — item is in good condition but packaging may have signs of shelf wear/aging or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Acceptable — item is in working order but may show signs of wear such as scratches or torn packaging. All specific defects should be noted in the Comments section associated with each item.

Used — An item that has been opened and may show signs of wear. All specific defects should be noted in the Comments section associated with each item.

Refurbished — A used item that has been renewed or updated and verified to be in proper working condition. Not necessarily completed by the original manufacturer.

Acceptable
Textbook may contain underlining, highlighting or writing. Infotrac or untested CD may not be included.

Ships from: Hillsboro, OR

Usually ships in 1-2 business days

  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$1.99
Seller since 2006

Feedback rating:

(60961)

Condition: Very Good
Great condition for a used book! Minimal wear. 100% Money Back Guarantee. Shipped to over one million happy customers. Your purchase benefits world literacy!

Ships from: Mishawaka, IN

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$1.99
Seller since 2014

Feedback rating:

(4185)

Condition: Acceptable
Free State Books. Never settle for less.

Ships from: Halethorpe, MD

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$1.99
Seller since 2009

Feedback rating:

(7741)

Condition: Good
Minimal damage to cover and binding. Pages show light use. With pride from Motor City. All books guaranteed. Best Service, Best Prices.

Ships from: Brownstown, MI

Usually ships in 1-2 business days

  • Canadian
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$1.99
Seller since 2009

Feedback rating:

(6935)

Condition: Very Good
Nice condition with minor indications of previous handling. Book selection as BIG as Texas.

Ships from: Dallas, TX

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$1.99
Seller since 2010

Feedback rating:

(5751)

Condition: Good
This book has a light amount of wear to the pages, cover and binding. Blue Cloud Books ??? Hot deals from the land of the sun.

Ships from: Phoenix, AZ

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$1.99
Seller since 2014

Feedback rating:

(109)

Condition: Acceptable
PAPERBACK Fair 0735614059 Fade marks in the cover Smudges on front/back cover.

Ships from: San Mateo, CA

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$2.39
Seller since 2009

Feedback rating:

(3725)

Condition: Good
01/01/2001 Paperback Used-Good Book in good or better condition. Dispatched same day from US or UK warehouse.

Ships from: Valley Cottage, NY

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$3.00
Seller since 2014

Feedback rating:

(6)

Condition: Good
2001 Paperback Good Connecting readers with great books since 1972. Used books may not include companion materials, some shelf wear, may contain highlighting/notes, and may not ... include cd-rom or access codes. Customer service is our top priority! Read more Show Less

Ships from: Lewisville, TX

Usually ships in 1-2 business days

  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
$11.94
Seller since 2014

Feedback rating:

(109)

Condition: Like New
PAPERBACK Fine 0735614059.

Ships from: San Mateo, CA

Usually ships in 1-2 business days

  • Canadian
  • International
  • Standard, 48 States
  • Standard (AK, HI)
  • Express, 48 States
  • Express (AK, HI)
Page 1 of 2
Showing 1 – 10 of 14 (2 pages)
Close
Sort by
Sending request ...

Overview

Whether you're extending an existing database or developing a sophisticated custom solution, this detailed developer's guide can help you tap all the power, performance, and extensibility of Access 2002. Crack the covers to get the expert insights and rich, adaptable code samples you need to write extensible database applications quickly and intuitively. Along the way, you'll discover in-depth information about developer-empowering technologies such as Microsoft ActiveX Data Objects (ADO), Jet SQL, Microsoft SQL Server, XML, and Web development. You'll also find best practices and real-world examples to enhance your understanding and expand your skills.
Read More Show Less

Editorial Reviews

From Barnes & Noble
The Barnes & Noble Review
Microsoft Press gave Rick Dobson a few extra months to finish Microsoft Access 2002 Core Reference, and the payoff is a book far deeper, richer, and more useful than the solid Access 2000 guide it replaces.

Where the previous edition contained one chapter on data access, this book dedicates nearly 250 pages to the subject, covering ADO, command and parameter objects, select and parameter queries, ADO event programming, the ADOX library, Jet SQL, and a whole lot more.

Also added: thorough coverage of using Access 2002 to build and manage SQL Server solutions. This one's a biggie. Access is, well, more access-ible than SQL Server, making it easier for more people to build enterprise solutions, and offering powerful RAD tools for creating forms, reports, and web pages. Dobson covers just about all of this -- including views, stored procedures, triggers, SQL-DMO programming, even security.

No book on Access 2002 development would be complete without web coverage, and there's plenty of it here: not just Data Access Pages but XML Data Representations and the FrontPage 2002 Database Wizard, too. Access is alive and well -- and with this book, Access programmers can push it further than ever before. (Bill Camarda)

Bill Camarda is a consultant, writer, and web/multimedia content developer with nearly 20 years' experience in helping technology companies deploy and market advanced software, computing, and networking products and services. His 15 books include Special Edition Using Word 2000 and Upgrading & Fixing Networks For Dummies®, Second Edition.

Read More Show Less

Product Details

  • ISBN-13: 9780735614055
  • Publisher: Microsoft Press
  • Publication date: 1/1/2001
  • Edition description: 2002 Edition
  • Pages: 1110
  • Product dimensions: 7.56 (w) x 9.62 (h) x 2.17 (d)

Meet the Author

Rick Dobson is highly regarded as a speaker and author on Microsoft Access and Web technologies. Founder and chief technologist for CAB, Inc., Rick is an MCP and a contributor to many computer publications, including Microsoft Office & Visual Basic® for Applications Developer and DBMS, as well as the author of Programming Microsoft Access 2000.
Read More Show Less

Read an Excerpt

Chapter 2: Data Access Models: Part I

Microsoft Access 2000 supports two data access models: the traditional Data Access Objects (DAO) and ActiveX Data Objects (ADO). DAO targets just the Jet database engine. Access 2000 was the first Access version to support ADO for data access to Jet as well as other databases in a single programming language. With Access 2002, Microsoft improved on past functionality and added new capabilities to ADO programming. Instead of being based on a single database engine, ADO uses a common programming model to access data universally. It relies on OLE DB providers for low-level links to data sources. OLE DB technologies will eventually make their ODBC predecessors obsolete, much as ADO will replace DAO. Therefore, if you invest in learning ADO now, you will get on the fast track to adopting future improvements in data access with upcoming versions of Access. And you’ll be able to programmatically manage more and different kinds of data sources.

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

Access 2002 installs ADO version 2.5 by default. The Microsoft DAO 3.6 Object Library is still available for those who prefer to use it. As with ADO 2.1 from Access 2000, developers can still reference any of three ADO models: the ADODB library, the ADOX library, and the JRO library. New Microsoft Visual Basic for Applications (VBA) projects associated with Access database files and Access projects have a default reference to the ADODB library, but you must explicitly create a reference to the other two ADO models. Microsoft ships the sample Northwind database with a reference to the DAO 3.6 library. If you want to program with ADO, you must add a reference to one or more of the appropriate ADO libraries. Their shorthand names appear in the References dialog box, respectively, as:

  • 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

Provider NameDescription
Microsoft OLE DB Provider for ODBCUse this provider to connect to any ODBC-compliant data source for which you do not have a more specific OLE DB provider, such as the SQLOLEDB provider for SQL Server.
Microsoft OLE DB Provider for Microsoft Indexing ServiceUse this provider to gain read-only access to file system and Web data indexed by the Microsoft Indexing Service.
Microsoft OLE DB Provider for Microsoft Active Directory ServiceUse this provider for read-only access to Microsoft Active Directory Service Interfaces (ADSI). Currently, this provider connects to Windows NT 4, Windows 2000, and Novell directory services, as well as any LDAP-compliant directory service.
OLE DB Provider for Microsoft JetUse this provider to connect to Jet 4 databases.
Microsoft OLE DB Provider for SQL ServerUse this provider to connect to databases on SQL Server.
Microsoft OLE DB Provider for OracleUse this provider to connect to Oracle databases.
Microsoft OLE DB Provider for Internet PublishingUse this provider to access resources served by Microsoft FrontPage and Microsoft Internet Information Services (IIS). In other words, you can manipulate and open Web pages at a Web site programmatically from an Access application.

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

Provider/Component NameDescription
Microsoft Data Shaping Service for OLE DBUse this service to construct hierarchical (or shaped) recordset objects. These shaped recordset objects expose data in the same style as main/sub forms.
Microsoft OLE DB Persistence ProviderUse this service to persist a recordset in a proprietary or an Extensible Markup Language (XML) file format. This capability is particularly valuable when computers must access data sources to which they are not always connected.
Microsoft OLE DB Remoting ProviderUse this service to invoke data providers on a computer with a database server from another computer. This provider offers Access developers the opportunity to build advanced three-tiered solutions by referring to a remote server.
Microsoft Cursor Service for OLE DBUse this service to expand the functionality of native cursors for a service provider. For example, the Cursor Service for OLE DB can expand the range of cursor types available for a data source, dynamically construct an index to speed searches with the Find method for ADO recordsets, and allow the specification of sort criteria for recordsets.

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 Show Less

Table of Contents

Introduction
Pt. I VBA Fundamentals
1 Introduction to VBA 3
Pt. II Data Access and Data Definition
2 Data Access Models: Part I 73
3 Data Access Models: Part II 145
4 The ADOX Libary and Tables 183
5 Jet SQL, the ADOX Library, and Queries 239
Pt. III User and Programmatic Interfaces
6 Forms, Controls, and Data 317
7 Reports, Controls, and Data 383
8 Designing and Using PivotTables and PivotCharts 459
9 Class, Form, and Report Modules 495
10 Microsoft Office Objects 543
11 Integrating Access with Other Office Applications 611
Pt. IV Multiuser Development for Jet, SQL, Server, and the Web
12 Working with Multiuser Databases 661
13 Replicating Databases 705
14 Using Access to Build SQL Server Solutions: Part I 745
15 Using Access to Build SQL Server Solutions: Part II 849
16 Access Does the Web: Part I 935
17 Access Does the Web: Part II 1017
App Microsoft Office XP Developer 1071
Index 1077
Read More Show Less

First Chapter

Chapter 2.|Data Access Models: Part I
  • ADO Overview
  • The Connection Object
    • Connecting to Databases
    • The Mode Property
    • The OpenSchema Method
  • The Recordset Object
    • Selected Recordset Properties
    • Selected Recordset Methods
    • Printing Recordset Rows
    • Adding, Editing, and Deleting Records
    • Finding Records
    • Seeking Records
    • Filtering Records
    • Persisting Recordsets
    • Sorting Recordsets
  • The Field Object
    • Name and Value Properties
    • The Type Property
    • Printing Field Data Types
    • Creating Fields for a Recordset

Chapter 2  Data Access Models: Part I

Microsoft Access 2000 supports two data access models: the traditional Data Access Objects (DAO) and ActiveX Data Objects (ADO). DAO targets just the Jet database engine. Access 2000 was the first Access version to support ADO for data access to Jet as well as other databases in a single programming language. With Access 2002, Microsoft improved on past functionality and added new capabilities to ADO programming. Instead of being based on a single database engine, ADO uses a common programming model to access data universally. It relies on OLE DB providers for low-level links to data sources. OLE DB technologies will eventually make their ODBC predecessors obsolete, much as ADO will replace DAO. Therefore, if you invest in learning ADO now, you will get on the fast track to adopting future improvements in data access with upcoming versions of Access. And you’ll be able to programmatically manage more and different kinds of data sources.

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

Access 2002 installs ADO version 2.5 by default. The Microsoft DAO 3.6 Object Library is still available for those who prefer to use it. As with ADO 2.1 from Access 2000, developers can still reference any of three ADO models: the ADODB library, the ADOX library, and the JRO library. New Microsoft Visual Basic for Applications (VBA) projects associated with Access database files and Access projects have a default reference to the ADODB library, but you must explicitly create a reference to the other two ADO models. Microsoft ships the sample Northwind database with a reference to the DAO 3.6 library. If you want to program with ADO, you must add a reference to one or more of the appropriate ADO libraries. Their shorthand names appear in the References dialog box, respectively, as:

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

NOTE:
If you install the optional SQL Server 2000 Desktop Engine that ships with Access 2000, Access automatically upgrades ADO 2.5 to version 2.6. If you experience difficulty connecting to a commercial release of SQL Server 2000, you will need the 2.6 version of ADO. See Chapter 14, "Using Access to Build SQL Server Solutions: Part I," for a description of how to install the SQL Server 2000 Desktop Engine.

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.

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

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.


NOTE:
Choosing a library version isn’t always as simple as picking the reference with the highest version number. For example, if you need the same application to run on multiple machines, some of which have the 2.6 version of ADO and others that have the 2.5 version, you should use the 2.5 version to eliminate the possibility of a failure due to a MISSING reference on computers without the 2.6 version of the ADODB library. Alternatively, you can upgrade the ADO version on machines running the 2.5 version by installing the Microsoft Desktop Engine that ships with Office XP. This latter approach is especially appealing when some of your machines require ADO 2.6 for a specific purpose, such as interfacing with SQL Server 2000.

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

Provider Name Description
Microsoft OLE DB Provider for ODBC Use this provider to connect to any ODBC-compliant data source for which you do not have a more specific OLE DB provider, such as the SQLOLEDB provider for SQL Server.
Microsoft OLE DB Provider for Microsoft Indexing Service Use this provider to gain read-only access to file system and Web data indexed by the Microsoft Indexing Service.
Microsoft OLE DB Provider for Microsoft Active Directory Service Use this provider for read-only access to Microsoft Active Directory Service Interfaces (ADSI). Currently, this provider connects to Windows NT 4, Windows 2000, and Novell directory services, as well as any LDAP-compliant directory service.
OLE DB Provider for Microsoft Jet Use this provider to connect to Jet 4 databases.
Microsoft OLE DB Provider for SQL Server Use this provider to connect to databases on SQL Server.
Microsoft OLE DB Provider for Oracle Use this provider to connect to Oracle databases.
Microsoft OLE DB Provider for Internet Publishing Use this provider to access resources served by Microsoft FrontPage and Microsoft Internet Information Services (IIS). In other words, you can manipulate and open Web pages at a Web site programmatically from an Access application.

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

Provider/Component Name Description
Microsoft Data Shaping Service for OLE DB Use this service to construct hierarchical (or shaped) recordset objects. These shaped recordset objects expose data in the same style as main/sub forms.
Microsoft OLE DB Persistence Provider Use this service to persist a recordset in a proprietary or an Extensible Markup Language (XML) file format. This capability is particularly valuable when computers must access data sources to which they are not always connected.
Microsoft OLE DB Remoting Provider Use this service to invoke data providers on a computer with a database server from another computer. This provider offers Access developers the opportunity to build advanced three-tiered solutions by referring to a remote server.
Microsoft Cursor Service for OLE DB Use this service to expand the functionality of native cursors for a service provider. For example, the Cursor Service for OLE DB can expand the range of cursor types available for a data source, dynamically construct an index to speed searches with the Find method for ADO recordsets, and allow the specification of sort criteria for recordsets.

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.

Figure 2-2 The ADODB object library. (Image Unavailable)

The Connection Object

The Connection object establishes a link to a database. You always use a Connection object implicitly or explicitly when you work with a database. When you explicitly create this object, you can efficiently manage one or more connections and reassign the roles that these connections serve in an application. By implicitly creating a Connection object, you can shorten your code. Each new object that you create with an implicit connection consumes more resources. If your application has only one or two objects, each requiring its own connection, implicit connections might serve your needs best. ADO lets you choose how to create and manage connections as you see fit.

Unlike DAO, ADO is a general data access language, so not all of its properties, methods, or even data types are appropriate for the Jet engine. There is, however, a special OLE DB provider for Jet 4. Microsoft introduced this provider with Access 2000, and it remains in Access 2002. Since Connection objects critically depend on provider specifications, the ability to set a Connection parameter that references the Jet 4 provider is valuable. This custom provider allows ADO to reflect many of the special strengths that Jet offers. When you refer to a database in another file, you might want to include a Data Source parameter, which points to the physical location of a database that is not in the current project.

Connecting to Databases

The following simple code sample, OpenMyDB, opens the familiar Northwind database. Notice that a Dim statement declares and creates a reference to cnn1 as a Connection object. The use of the Open method on cnn1 makes the database available to the rest of the procedure. Notice that the Provider and Data Source parameters appear within a single pair of double quotes. The Provider parameter points to the Jet 4 OLE DB provider, and the Data Source parameter points to the physical location of the Northwind database.

Sub OpenMyDB()
Dim cnn1 As New Connection
Dim rst1 As Recordset
‘Create the connection
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PMA Samples\Northwind.mdb;"
‘Create recordset reference, and set its properties
Set rst1 = New ADODB.Recordset
rst1.CursorType = adOpenKeyset
rst1.LockType = adLockOptimistic
‘Open recordset, and print a test record
rst1.Open "Customers", cnn1
Debug.Print rst1.Fields(0).Value, rst1.Fields(1).Value
‘Clean up objects
rst1.Close
cnn1.Close
Set rst1 = Nothing
Set cnn1 = Nothing
End Sub

After creating a reference to the connection, the code instantiates a Recordset object. Instantiating an object reference makes it available for use within the procedure. Next the procedure assigns values to a couple of properties for the recordset. The next-to-last block of code opens the recordset and prints a couple of fields from the current record when the recordset initially opens. The Open method for a Recordset object can reference a connection to a database and some source of records in the database. The previous code selects all of the records from the Customers table in the Northwind database. The Open method initially makes the first record available based on the order of records in a source, such as a table ordered by its primary key. Just before closing, the procedure closes the recordset and connection and sets their object references to Nothing.


NOTE:
You can specify the connection to the database for the current Access database file with the syntax CurrentProject.Connection. If you have a table or query named Customers in your current database file, you can reference it by replacing cnn1 in the preceding sample with CurrentProject.Connection. This eliminates the need for an explicit connection object when referring to the current database.

Although object references declared within a procedure normally go out of scope when the procedure closes, it is good programming practice to close objects and set them to Nothing before exiting a procedure. By setting an object reference to Nothing, you formally release the object’s memory. This practice can help avoid memory leaks, which can slow an application. If objects do not close successfully when your application exits a procedure, the memory remains reserved for the object. After repeatedly running a procedure, these memory reservations can slow down your application.

Closing an object and setting it to Nothing provides other benefits besides inoculating your applications from memory leaks. First, an object must be closed in order for you to reassign its property settings. Second, setting a Connection object to Nothing can guard against the impact of a silent error and recovery by ADO. If your application reuses a Connection object but you fail to set its properties correctly, the ADO provider automatically and silently reverts to the object’s prior setting. Setting a Connection object to Nothing after you are through with it forces a trappable run-time error for a Connection object reference with an improper setting. Implicitly creating a connection for an object also guards against errors that are not trappable, because the object has use of the connection just for its lifetime.

The next code sample, OpenMySQLDB, demonstrates two additional points about the behavior of the Connection object. First, this sample shows how to perform an analogous task for a SQL Server database instead of a Jet one. In this case, the database is the NorthwindCS database, a SQL Server database shipping with generally the same tables, queries, forms, reports, and data access pages as those in the Northwind Access database file.

Second, this sample highlights the similar programming ADO employs for two entirely different databases. In both this sample and the preceding one, you declare, instantiate, and open a Connection object in similar ways. The major difference is the connection string, which is always unique for individual data providers. This sample then instantiates a recordset, assigns it property settings, and uses the connection as a parameter for the recordset’s Open method. This code is identical in the two procedures—despite the fact that the earlier sample references a file-server database and this one references a client/server database.


NOTE:
The first time you open the NorthwindCS Access project, it automatically attempts to install the NorthwindCS database if you do not have a copy of the Northwind SQL Server database on your local server. You install the NorthwindCS Access project in the same manner that you would the Northwind Access database file—just make a different selection during the initial Access installation or when you update the installation.
Sub OpenMySQLDB()
Dim cnn1 As Connection
Dim rst1 As Recordset
Dim str1 As String
‘Create a Connection object after instantiating it,
‘this time to a SQL Server database
Set cnn1 = New ADODB.Connection
str1 = "Provider=SQLOLEDB;Data Source=cab2000;" & _
"Initial Catalog=NorthwindCS;User Id=sa;Password=password;"
cnn1.Open str1
‘Create recordset reference, and set its properties
Set rst1 = New ADODB.Recordset
rst1.CursorType = adOpenKeyset
rst1.LockType = adLockOptimistic
‘Open recordset, and print a test record
rst1.Open "Customers", cnn1
Debug.Print rst1.Fields(0).Value, rst1.Fields(1).Value
‘Clean up objects
rst1.Close
cnn1.Close
Set rst1 = Nothing
Set cnn1 = Nothing
End Sub

Notice that the connection string syntax is different between the first and second code samples. The Provider parameter in the second sample points at the "SQLOLEDB" provider. This is the ADO data provider specifically designed for SQL Server databases. When connecting to a SQL Server database, you must designate the server name, which is "cab2000" in this case, and the database name. As mentioned, this sample connects to the NorthwindCS database. The designation of a user ID and password depends on the type of authentication your SQL server uses. If your application uses SQL Server authentication, you must specify the user ID and password. If the SQL server for an application uses Windows NT authentication, you do not need to specify a user ID and password in your connection string. If your application ever runs on a Windows 98 computer or a computer disconnected from an office, department, or enterprise server, SQL Server authentication is a necessity. This is because your application will not necessarily have Windows NT or Windows 2000 available to authenticate the identification of users when it runs. Finally, notice that the call to the Open method for the Connection object uses a string variable rather than a string constant as an argument. This makes it easy for your application to construct connection strings based on user information and the availability of a Windows NT or Windows 2000 server.

The following sub procedure, OpenFast, also opens a recordset based on the Customers table in the Northwind Access database file and prints the first record. However, it uses fewer lines of code, and the code is less complicated than the preceding two samples because it implicitly creates a connection and accepts more default settings.

Sub OpenFast()
Dim rst1 As Recordset
‘Less code, but potentially greater resource consumption
Set rst1 = New ADODB.Recordset
rst1.Open "Customers", "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PMA Samples\Northwind.mdb;"
Debug.Print rst1.Fields(0), rst1.Fields(1)
‘Clean up objects
rst1.Close
Set rst1 = Nothing
End Sub

Since there is no explicit connection, the OpenFast procedure does not need to declare a Connection object (and therefore doesn’t have to open or close such an object). As you can see, the Open method for a Recordset object can include the essential connection information of a provider and a data source. Calling the Open method on a Recordset object instead of a Connection object requires only one additional parameter—the source for the recordset, which is the Customers table. The Open method relies on the default CursorType and LockType settings, which are, respectively, forward-only and read-only. These settings provide for very fast operations, but they do not offer a lot of functionality. Nevertheless, if they suit your needs and let you divert your attention to other aspects of application development, they might be the best choice.

The ReuseAConnection sub procedure shown next illustrates a couple of ADO programming features not yet covered and reinforces several others. The best way to discover what this sample offers is to step through it (for example, with the Step Into control on the Debug toolbar). First, the sample demonstrates how to reuse a Connection object (or any other ADO object). You must close an object before you can reset its properties so that the object can serve another purpose, such as to connect to a different database. If you step through the sample, you’ll see that the code generates and traps a 3705 run-time error. The code in the error trap closes the cnn1 connection so that the procedure can reuse the object reference to connect to another database.

Sub ReuseAConnection()
Dim cnn1 As ADODB.Connection
On Error GoTo connTrap
‘Assign the connection reference
Set cnn1 = New ADODB.Connection
‘Use Jet provider to connect to Northwind
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PMA Samples\Northwind.mdb;"
‘Run initially with the next statement commented;
‘then rerun with the next statement uncommented
‘cnn1.Close
‘Incrementally builds connection string.
‘Forces error when the Connection object is already open.
cnn1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;"
cnn1.ConnectionString = cnn1.ConnectionString & _
"Data Source=C:\PMA Samples\Northwind_backup.mdb;"
cnn1.Open
connExit:
‘Close any connection still open before exiting
cnn1.Close
Set cnn1 = Nothing
Exit Sub
connTrap:
If err.Number = 3705 Then
‘Close an open connection for its reuse
Debug.Print "Closing cnn1"
cnn1.Close
Resume
Else
Debug.Print err.Number; err.Description
Debug.Print cnn1.Provider
Debug.Print cnn1.Provider; cnn1.ConnectionString
End If
End Sub

The ReuseAConnection sub procedure demonstrates another other point worthy of your attention. Notice the cnn1 declaration includes an ADODB prefix before Connection. While not necessary, this is good programming practice. This is especially true when a VBA project has references to both the DAO and ADODB libraries. Since both libraries contain classes with the same name, you might end up with an object based on the wrong class if you do not use the library prefix in your declaration. As you work with more and more references (an easy way to expand the capabilities of your applications), it becomes increasingly desirable to use library name prefixes. The chance of your libraries having identical names for different classes increases as you reference more libraries.

If you are not highly experienced with VBA programming, take note of the syntax for declaring an error trap. Just like events, errors happen. In many cases, these errors do not necessarily indicate a problem with the code in a solution (other than the need for more code for an error trap). Error traps give your solutions a way to dynamically adapt to run-time errors.

Adding an error trap to a procedure requires three steps. First, you need an On Error statement somewhere before the first place in a procedure that an error is possible (for example, immediately after a Sub or Function statement). This On Error statement should direct the program flow to a portion of the procedure that diagnoses the error and responds appropriately. Building this section is the second step. In the sample, the connTrap label marks an area of the procedure that fixes a 3705 error by closing the connection and resuming where the error occurred. If another type of error occurs, the trap prints the built-in error diagnostics along with the connection string associated with the error before concluding. The third step to a successful error trap is the insertion of code to exit the procedure before the error-trap logic and after the last line of code in the normal body of the program. Failing to complete this step can cause your application to wander into the error-trap area when no error exists.


NOTE:
It is often a good practice not to close and remove objects when you detect an error. This is because run-time errors can result from an unanticipated problem associated with the instantiation, closing, or opening of object references. If such an error occurs, looping to a code segment that attempts to close a noninstantiated object can generate an infinite loop.

One of the major strengths of ADO is its ability to connect to remote data sources. The ThreeWaysToConnectRemotely procedure highlights three different syntaxes for connecting to remote databases:

Sub ThreeWaysToConnectRemotely()
Dim cnn1 As ADODB.Connection
On Error GoTo connTrap
‘Assign the connection reference
Set cnn1 = New ADODB.Connection
‘A connection based on SQLOLEDB
With cnn1
.Provider = "SQLOLEDB"
.ConnectionString = "data source = cab2000;" & _
"user id = sa; Password=password; initial catalog =Pubs"
.Open
End With
cnn1.Close
‘Use connection string parameters with MSDASQL provider.
‘Notice syntax difference from MSDASQL and SQLOLEDB providers.
cnn1.Open "Provider=MSDASQL;Driver=SQL Server;" & _
"Server=cab2000;Database=Pubs;uid=sa;pwd=password;"
cnn1.Close
‘Designation of the provider, or even the user ID and password,
‘might not be necessary with a DSN
cnn1.Open "DSN=Pubs; User ID=sa; Password=password"
‘The following close, in combination with the next one,
‘raises a 3704 error number
cnn1.Close
connExit:
‘Close any connection still open before exiting
cnn1.Close
Set cnn1 = Nothing
Exit Sub
connTrap:
If err.Number = 3704 Then
‘The connection is already closed; skip close method
Resume Next
Else
‘Unanticipated run-time error
Debug.Print err.Number; err.Description
Debug.Print cnn1.Provider; cnn1.ConnectionString
End If
End Sub

All three examples in the ThreeWaysToConnectRemotely procedure connect to the Pubs database on a server named "cab2000". The first connection uses the SQLOLEDB provider. Consider using this provider whenever you are connecting to a SQL Server database. The second example in the code reveals the correct syntax for using the MSDASQL provider, the default provider. If you omit the provider name from a connection string, ADO automatically uses this provider. This provider is appropriate for databases that do not have a specific OLE DB data provider. The third example in the procedure uses a data source name (DSN) to designate the connection string. This technique is very popular when using ADO on a Web server and in some large organizations that push the DSN onto each computer on a network. Use the ODBC Data Source Administrator available through the Control Panel in Windows 98 and Windows NT or the Administration Tools menu on Windows 2000 to create a DSN. The sample explicitly specifies a user ID and password. This is necessary because the connection string specifies SQL Server authentication. With Windows NT authentication, you do not need to specify a user ID and password.


NOTE:
You can readily hide the user ID and password with either of two approaches. First, VBA offers you the opportunity to lock projects for viewing except by those users with a password. Second, you can convert your .mdb file to an .mde format. This process removes all editable code. Both approaches receive more coverage in Chapter 12, "Working with Multiuser Databases."

The Mode Property

By default, the Connection object’s Open method creates a database for shared access. However, you can set the Connection object’s Mode property to any of eight other settings that grant various degrees of restricted access to a database. These mode settings for Connection objects pertain generally to recordsets and commands that inherit Connection object settings through their ActiveConnection property. Additionally, when you open a Recordset object on a Command object, the Recordset object inherits the mode setting that applies to the command. Record and Stream objects, like Connection objects, have a Mode argument for their Open methods.

The following code shows the impact of the read-only mode setting on the ability to update a recordset. Depending on the value of a conditional compiler constant named varPermitError, the procedure opens the Northwind database in either the default shared mode or read-only mode. Since the procedure attempts to update the Customers table in the Northwind database, opening the database in read-only mode forces a run-time error. Setting the varPermitError constant to True causes the error by setting the Mode property for cnn1 to read-only mode. The recordset inherits this setting through its ActiveConnection property. An error trap catches the error and shuts down the program gracefully with a custom error message.

Sub OpenLookOnly()
On Error GoTo LookOnlyTrap
Dim cnn1 As New ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim varPermitError As Variant
‘Set varPermitError to True to generate
‘error from Mode property restriction
#Const varPermitError = True
‘Instantiate a Connection object, and
‘conditionally set it to read-only data access
Set cnn1 = New ADODB.Connection
#If varPermitError = True Then
cnn1.Mode = adModeRead
#End If
‘Open the Connection object
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PMA Samples\Northwind.mdb;"
‘Use the next line to determine the default mode setting
Debug.Print cnn1.Mode
Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = cnn1
rst1.Open "Customers", , adOpenKeyset, adLockOptimistic, adCmdTable
‘An adModeRead setting for cnn1.Mode causes an error in this
‘procedure when you execute the next two lines
rst1.Fields("CustomerID") = "xxxxx"
rst1.Update
Debug.Print rst1.Fields("CustomerID")
LookOnlyExit:
‘Clean up objects
rst1.Close
cnn1.Close
Set rst1 = Nothing
Set cnn1 = Nothing
Exit Sub
LookOnlyTrap:
If err.Number = -2147217911 Then
‘Error -2147217911 signals an attempt to edit a database open
‘in read-only mode
MsgBox "Forced error by attempt to update with a " & _
"read-only connection.", vbInformation, _
"Programming Microsoft Access Version 2002"
Resume LookOnlyExit
Else
‘Another unanticipated error occurred
Debug.Print err.Number; err.Description
MsgBox "View Immediate window for error diagnostics.", _
vbInformation, "Programming Microsoft Access Version 2002"
End If
End Sub

Running the OpenLookOnly procedure with varPermitError equal to any value other than True opens the recordset in shared mode. This setting permits you to modify the value of table cells in the database. The first time you run the procedure it succeeds, unless you previously created a customer with a CustomerID field value of "xxxxx". The procedure fails the second time you run it because it attempts to create a duplicate value for the primary key field. The RestoreFirstCustomerID procedure, found on the companion CD, replaces the "xxxxx" CustomerID value with the original value of "ALFKI", which is the original CustomerID field value for the first customer in the table.

Besides demonstrating the impact of mode settings, the OpenLookOnly procedure is noteworthy for at least one other reason. The recordset Open method includes a parameter after the cursor-type setting of adLockOptimistic. This is the first sample to show this optional Open method parameter. In fact, the parameter has the name Options. You designate a value for the parameter with intrinsic constants that are members of either the CommandTypeEnum or the ExecuteOptionEnum enums. Recall from Chapter 1, "Introduction to VBA," that the Object Browser can help you with the member names and values of enums. The Options parameter can designate the type of source for a recordset and indicate how the recordset returns records (for example, synchronously or asynchronously). In the case of the OpenLookOnly procedure, the Options parameter of adCmdTable instructs the Open method to treat the first parameter designating Customers as a table accessed via an SQL query.

Table 2-3 describes the nine constants that you can use to set a connection’s Mode property. These constants control the type of editing that one or more users can do through a connection to a database.

Table 2-3 ConnectModeEnum Members

Constant Value Behavior
adModeUnknown 0 Permissions not set or determined
adModeRead 1 Read-only permission
adModeWrite 2 Write-only permission
adModeReadWrite 3 Read/write permission
adModeShareDenyRead 4 Prevents others from opening record source with read permissions
adModeShareDenyWrite 8 Prevents others from opening record source with write permissions
adModeShareExclusive 12 Prevents others from opening the connection
adModeShareDenyNone 16 Shared access (default)
AdModeRecursive 4194304 Can propagate share-deny restrictions to children of the current record

The OpenSchema Method

The Connection object’s OpenSchema method lets an application browse the objects in the collections available through a connection without the requirement of creating a reference to the ADOX library and enumerating the elements in a collection. The output from the OpenSchema method for an Access database file provides information about the design of a database, such as the names of user-defined tables, its queries, and even details such as column and table validation rules. The specific details depend on how a given OLE DB provider implements the general capabilities of the method. To discover additional details on the scope of this method, search Help in the VBE window for the term "SchemaEnum."


NOTE:
If you go to the SchemaEnum Help page in the ADO Help file, you will notice that the hyperlinks in the Description column do not work. The links only work from a Web page at the Microsoft Developer Network (MSDN) site. Open your browser to msdn.microsoft.com/library/psdk/dasdk/mdae0wfh.htm for a version of the page with working hyperlinks in the Description column. By the way, the information on the linked pages enumerates the field names, along with other useful information, for the recordset returned by the OpenSchema method.

The following code uses the OpenSchema method with the Jet 4 provider to list the user-defined tables available from a connection. These tables appear in the Database window when you select Tables from the Objects bar (and the default option of not showing system tables prevails). The procedure starts by declaring a connection and a recordset. The connection acts as the source for the output from the OpenSchema method. The recordset holds the output from the OpenSchema method. The argument for the OpenSchema method indicates that the method returns a rowset of all tables in the data source designated in the connection. By filtering the rows returned by the method, the procedure prints just the names of user-defined tables.

Sub OpenSchemaTableTables()
Dim cnn1 As ADODB.Connection
Dim rst1 As ADODB.Recordset
‘Connect to the Northwind database
Set cnn1 = New ADODB.Connection
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PMA Samples\Northwind.mdb;"

Set rst1 = cnn1.OpenSchema(adSchemaTables)
‘Print just tables; other selection criteria include
‘TABLE, ACCESS TABLE, and SYSTEM TABLE
Do Until rst1.EOF
If rst1.Fields("TABLE_TYPE") = "TABLE" Then
Debug.Print "Table name: " & _
rst1.Fields("TABLE_NAME") & vbCr
End If
rst1.MoveNext
Loop
‘Clean up objects
rst1.Close
cnn1.Close
Set rst1 = Nothing
Set cnn1 = Nothing
End Sub

New Access developers, and perhaps some experienced ones, might be surprised to learn that some types of queries can also be considered as tables. For example, you can use the OpenSchema method to discover the names of all stored queries that return rows and do not depend on parameters in an Access database file. Access database files typically refer to this type of query as a view. The following code sample is a simple adaptation of the preceding one; it enumerates all the views in a connection:

Sub OpenSchemaTableViews()
Dim cnn1 As ADODB.Connection
Dim rst1 As ADODB.Recordset
‘Connect to the Northwind database
Set cnn1 = New ADODB.Connection
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PMA Samples\Northwind.mdb;"

Set rst1 = cnn1.OpenSchema(adSchemaTables)
‘Print just views; other selection criteria include
‘TABLE, ACCESS TABLE, and SYSTEM TABLE
Do Until rst1.EOF
If rst1.Fields("TABLE_TYPE") = "VIEW" Then
Debug.Print "View name: " & _
rst1.Fields("TABLE_NAME") & vbCr
End If
rst1.MoveNext
Loop
‘Clean up objects
rst1.Close
cnn1.Close
Set rst1 = Nothing
Set cnn1 = Nothing

End Sub

The information provided by the OpenSchema method can be substantially richer than just the names of tables within a database. Examine members of the SchemaEnum enum to discover the valid arguments for the OpenSchema method. You use an enum member to specify a domain about which to gather schema-based information and then examine the column names for the rowset returned by the method to retrieve the detailed information available for that category. These columns vary according to the enum member.

The OpenSchemaConstraints sub procedure that appears next uses the adSchemaCheckConstraints argument value to return the set of validation rules in an Access database file. Before listing information in individual rows from the rowset returned by the method, the procedure shows the syntax for enumerating the field column names. As you can see, the columns are zero-based. You can use this enumeration to help decide which detailed information you want to examine. The sample that follows lists the CONSTRAINT_NAME, CHECK_CLAUSE, and DESCRIPTION columns. Some additional code in the sample spaces the string data for columns evenly across the Immediate window. I will review the logic to achieve this result after we examine the procedure’s output.

Sub OpenSchemaConstraints()
Dim cnn1 As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim int1 As Integer
Dim int2 As Integer
Dim i As Integer
‘Connect to a backup of the Northwind database with a
‘clean set of constraints
Set cnn1 = New ADODB.Connection
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PMA Samples\Northwind_backup.mdb;"
‘Open the constraints in the database
Set rst1 = cnn1.OpenSchema(adSchemaCheckConstraints)
‘Demonstrate syntax for enumerating the column names
‘in the recordset returned by the OpenSchema method;
‘the columns vary depending on the method’s parameter
For i = 0 To rst1.Fields.Count - 1
Debug.Print rst1.Fields(i).Name
Next i
‘Find the length of each of the first two columns
int1 = FindTheLongest(rst1, "CONSTRAINT_NAME") + 1
rst1.MoveFirst
int2 = FindTheLongest(rst1, "CHECK_CLAUSE") + 1
rst1.MoveFirst
‘Print contraint names, expressions, and descriptions
Do Until rst1.EOF
Debug.Print rst1(2) & String(int1 - Len(rst1(2)), " ") & _
rst1(3) & String(int2 - Len(rst1(3)), " ") & " " & rst1(4)
rst1.MoveNext
Loop
‘Clean up objects
rst1.Close
cnn1.Close
Set rst1 = Nothing
Set cnn1 = Nothing

End Sub
Function FindTheLongest(rst1 As ADODB.Recordset, _
FieldIndex As String) As Integer
Dim Length As Integer
‘Loop to return longest string in a field
Do Until rst1.EOF
Length = Len(rst1.Fields(FieldIndex))
If Length > FindTheLongest Then
FindTheLongest = Length
End If
rst1.MoveNext
Loop
End Function

The output from the OpenSchemaConstraints procedure appears in Figure 2-3. It begins by listing the names for the five columns from the OpenSchema method called with an adSchemaCheckConstraints argument. Next the output shows the constraints for the columns within the Access Northwind database file. The procedure succinctly provides that information by listing the table and column of each constraint along with the expression governing the input for a column.

Figure 2-3 The Immediate window output from the OpenSchemaConstraints procedure. (Image Unavailable)

Multicolumn string data of uneven lengths do not normally appear in neat columns within the Immediate window. The arrangement of columns is typically uneven because the length of a string in any column usually varies from row to row. This can make the results difficult to read. However, the preceding code sample circumvents this difficulty by padding each string after using a function procedure to determine the longest string in a field within a recordset. The main procedure spaces columns of string data evenly across the Immediate window, based on the longest strings in the CONSTRAINT_NAME and CHECK_CLAUSE columns.

The Recordset Object

As you’ve seen, a recordset is a programmatic construct for working with records. You can base your records on a table or a row-returning query stored in the current project or in another file, on an SQL statement, or on a command that returns rows. What you can do with a recordset depends on its OLE DB provider and on native data source attributes for the Connection object or connection string associated with the recordset’s ActiveConnection property.

While you can extract recordsets using other objects, such as connections and commands, the Recordset object’s rich mix of properties and methods make it a natural choice for doing much of your rowset processing. You can use recordsets to perform multiple actions against a set of rows: you can navigate between rows; print all or some of their contents; add, revise, and delete records; find records; and filter records to select one row or any subset of rows from a full recordset. There are two methods for finding records: Find and Seek. They offer related but not identical benefits, and they have contrasting requirements. Historically, recordsets are nonpersistent objects—they normally exist only while they are open in a program. Access 2000 and later versions of the program allow developers to persist recordsets to disk and then reopen them later.

Selected Recordset Properties

This section will introduce you to recordsets by describing some of their properties. I will briefly review a few properties of the Recordset. Use the Object Browser in the VBE to survey the full range of recordset properties and to further explore the properties that this section summarizes.

A recordset’s ActiveConnection property lets your application tap an open connection to support a recordset. You can set this property after instantiating the object reference for the recordset. However, the reference must be closed. Using this property simplifies your Open method statement for the recordset. Setting the recordset’s ActiveConnection property to a previously opened Connection object eliminates the need for including a connection string as part of the recordset’s Open method statement. When you set the ActiveConnection property before the calling the Open method, you do not even need to reference an existing connection in the Open method statement. In that case, the Connection object is implicitly created for you.

The cursor type is among the most basic features of a recordset. Use the CursorType property to designate a cursor for a recordset. The property setting determines the ways you can navigate through the recordset, the visibility of changes by other users, and the types of locks that you can impose on its records. ADO supports four cursor types:

  • Dynamic  This type of cursor lets users view changes to a data source made by other users. It enables recordset maintenance functions such as adding, changing, and deleting records, and it permits bidirectional navigation around a database. Users can see all changes to a database made by other users. Assign an intrinsic constant of adOpenDynamic to the CursorType property to specify this type of cursor.
  • Keyset  This cursor has many of the properties of a dynamic cursor, except it does not offer immediate access to records added by other users. Records deleted by other users are inaccessible, but they appear in the recordset with a marker. Invoke a recordset’s Requery method to view records added by other users and to clear the deleted markers for records removed by other users. Assign an intrinsic constant of adOpenKeyset to the CursorType property to designate this type of cursor.
  • Static  This cursor is a snapshot of a recordset at a particular point in time. It allows bidirectional navigation. Changes to the database by other users are not visible. This type of cursor is suitable when you do not need information about updates by other users, such as reports from a specific moment in time. Use an intrinsic constant setting of adOpenStatic to create this type of cursor.
  • Forward-only  Sometimes called the fire-hydrant cursor, this type moves in one direction only and can speed up cursor performance. This is the default ADO cursor type. If you need another type of cursor, you must set the CursorType property before opening the recordset. If you are changing a recordset’s cursor type back to the default setting, assign adOpenForwardOnly to its CursorType property.

NOTE:
Developers migrating from DAO will be surprised to learn that the keyset cursor is not the default ADO cursor type. You must explicitly designate adOpenKeyset as the CursorType property if your application requires a keyset cursor.

The LockType property partially interacts with the cursor type because it controls how users can manipulate a recordset. One lock-type setting (adLockReadOnly) specifically matches forward-only cursors. This is the default lock type. Table 2-4 describes the four possible settings for the LockType property. The adLockBatchOptimistic setting is used specifically for remote databases, such as SQL Server or Oracle, as opposed to a local Jet database.

Table 2-4 LockTypeEnum Members

Constant Value Behavior
adLockUnspecified  -1 Only for use with recordset clones. One of two possible clone lock-type settings; the other is adLockReadOnly.
adLockReadOnly  1 Read-only access (default).
adLockPessimistic  2 Locks a record as soon as a user chooses to start editing it.
adLockOptimistic  3 Locks a record only when a user chooses to commit edits back to the database.
adLockBatchOptimistic  4 Allows edits to a batch of records before an attempt to update a remote database from the local batch of records; use with the UpdateBatch method to propagate changes to a local cache back to a remote server.

NOTE:
A recordset’s cursor-type property setting interacts with its lock-type setting with lock type taking precedence over cursor type. If you designate a forward-only cursor type with a lock type other than read-only (adLockReadOnly), ADO overrides your cursor-type setting. Similarly, ADO automatically converts a forward-only cursor type to a keyset cursor type if you designate optimistic locking.

ADO supports two ways to update data in a data source. When you specify an adLockOptimistic or adLockPessimistic setting for the LockType property, your application immediately updates a record whenever it invokes the Update method for a recordset. By using the adLockBatchOptimistic intrinsic constant for the LockType property, your application can save up changes to one or more records until the application issues an UpdateBatch method for a recordset. The UpdateBatch method then transfers all changes from a local record cache to disk. If the update does not succeed because of conflicts with the underlying recordset, as in an attempt to revise a deleted record, ADO generates a run-time error. Use the Errors collection to view warnings and the Filter property with an adFilterAffectedRecords setting to locate records with conflicts.

Use the CursorLocation property to invoke the Cursor Service for OLE DB. Refer back to Table 2-2 for a short description of the capabilities that ADO makes available through this service. Set the CursorLocation property to the adUseClient intrinsic constant for a Recordset object or a Connection object. The CursorLocation property also enables the Seek method. You cannot use the Seek method to search a recordset unless its CursorLocation property equals the adUseServer intrinsic constant.

A recordset Sort property can affect the results of both the Find and Move methods. (See the next section for more on these methods.) This property designates one or more fields that can determine the order in which rows display. The Sort property setting allows the designation of an ascending or descending order for any field. The default is ascending order. The Sort property settings do not physically rearrange the underlying rows—they merely determine the order in which a recordset makes its rows available.

The Filter property for a recordset defines a subset of the rows from an existing recordset. While this property has specialized applications for database synchronization and batch updating of a remote data source, it can also be a simple alternative to defining a new recordset based on an SQL statement or other source. If you already have a recordset and you need only a subset for another purpose, this property can serve admirably. Filter rules can contain compound as well as simple criteria statements. Set a Filter property to adFilterNone to remove a filter setting from a recordset and restore the full set of original values.

Selected Recordset Methods

Recordset methods complement recordset properties as a means of manipulating the values in a recordset. This section offers a brief review of selected methods and will give you a general idea of the kinds of functions that methods enable you to perform with recordsets. Use the Object Browser to survey the total set of recordset methods. You can get detailed help for any method by clicking the Help control in the Object Browser window.

The recordset’s Open method is one common route for making a recordset available in a procedure. The source argument is the most critical one for this method. It designates the data source on which the method patterns the object that it opens. Typical options for the source argument include a table, an SQL statement, a saved recordset file, a Command object, or a stored procedure. Access 2002 introduces the option of a URL or a Stream object as a potential source for a recordset. You use the Open method’s Options argument to designate the source type when you open a recordset. When you designate a Stream object as the source for a recordset, you should not designate any other parameters.

Several methods enable recordset navigation. These navigation methods also allow you to specify the current record in a recordset, which is necessary or desirable for certain methods, such as the Find method. These four methods reflect functionality comparable to that of a standard built-in bound form:

  • MoveFirst  This method changes the current record position to the first record in a recordset. The order of records depends on the current index or, if no index exists, on the order of entry. This method functions with all cursor types. Its use with forward-only cursors can force a reexecution of the command that generated the recordset.
  • MoveLast  This method establishes the last record in a recordset as the current record position. It requires a cursor type that supports backward movement or at least movement based on bookmarks. Using this method with a forward-only cursor generates a run-time error.
  • MoveNext  This method relocates the current record position one record forward (that is, in the direction of the recordset’s final record). If the current record position is the last record, the recordset’s EOF property is set to True. If this method is called when the recordset’s EOF property is already True, a run-time error results.
  • MovePrevious  This method sends the current record position one record backward. If the current record position is the first record, the recordset’s BOF property is set to True. If this method is called when the recordset’s BOF property is already True, a run-time error results. This method also generates a run-time error if you use it with a forward-only cursor type.

The Move method works differently than the other four recordset navigation methods because it can move the current record position a variable number of records in either direction. You use a positive argument to indicate movement toward the last record and a negative argument to specify movement toward the first record. If a move will extend beyond the first or last record, the Move method sets the recordset’s BOF or EOF property to True. If that property is already True, the Move method generates a run-time error. Movement is relative to the current record unless you specify a Start parameter, in which case you specify movement from the first or last record.

You can enhance the Move method’s performance in a couple of ways by using it with a recordset’s CacheSize property set to greater than the default value, which is 1. CacheSize settings cause ADO to store a fixed number of records in the local workstation’s memory. Because it is much faster to retrieve records from local memory than from a provider’s data store, you can speed record navigation by using a larger cache size. With a forward-only cursor and a larger cache size, you can actually enable backward scrolling as well as forward scrolling. If your cache setting is equal to the number of records in a recordset, you can scroll the full extent of the recordset in both directions. The CacheSize property does not enable backward scrolling within the cache with the MovePrevious method when using a forward-only cursor. However, you can use the Move method with a negative argument to achieve backward scrolling.

The recordset’s Find method searches for the first record that matches a specified selection criterion. While this method bears a striking similarity to a collection of Find methods in Access 97 and earlier Access versions, the Access 2002 version of Find has a different syntax and behavior. Rather than attempt to map the similarities and differences, you should simply learn the syntax and behavior of the new version.

The new Find method takes as many as four arguments. The first argument is required and is the criterion for the search. Its syntax follows that of SQL statement WHERE clauses. If you do not specify any other arguments, the method searches from the current record through the last record to find a record that matches the criterion. Once the method finds a match, you must explicitly move off that record to find a subsequent match in the recordset. If there is no match, the method sets the recordset’s EOF property to True. See the online help for a description of the remaining three optional arguments. The ADO event programming sample you will see in Chapter 3 also illustrates the use of these other Find arguments.

Several requirements govern the operation of the Find method. You must set the current record in a recordset before invoking the Find method. The only required argument for the Find method is a criterion indicating what to search for. You can only search for a value in a single field at a time. The default start location for a search in a recordset is the current row. You can also specify the starting row by using the method’s arguments. This approach removes the need to reposition the current row. Otherwise, you must set the current record in a recordset before invoking the Find method. You can use one of the Move method variations to set the current record. If the CursorLocation property is adUseClient, the Find method can dynamically create indexes for fields that do not have an index specified in the recordset source. These are dynamic indexes that do not permanently update the source for a recordset.


NOTE:
Although the Find method works only for one field at a time, ADO readily permits the development of subsets from a data source based on two or more fields. For example, you can open a recordset using a Select statement with compound criteria or filter an existing recordset with compound criteria.

The AddNew method inserts a new record into a recordset. After you invoke the method, you set the values for the fields in a new row that you want to add. Then you either move off the record using a Move method or you call the Update method while still on the row. (You can modify the values in a field using a similar pair of techniques. You update fields by assigning them new values, and then you move off the record. Alternatively, you can remain on an edited record as long as you call the Update method. You can delete a record by simply navigating to it and then invoking the Delete method. The deleted record remains current until you move away from it.)

The GetRows method copies a recordset’s contents from disk to an array in memory. This can make retrieving recordset values much faster since an application can gather data from memory rather than reading it from a disk storage device. Optional parameters enable you to specify subsets of an original recordset for copying into memory. You can designate a starting row to indicate when to start copying records, and you can designate which columns to include in the memory-based version of a recordset.

The GetString method enables you to return rows from a recordset as a sequence of text lines. This method is especially convenient for displaying values from a recordset in the Immediate window. Use GetString so that you don’t need a loop to pass a group of rows from a recordset to the Immediate window. Optional parameters let you restrict the number of returned rows as well as the row and column delimiters for the values from a recordset. You can also designate a special value to represent fields with Null values.

Printing Recordset Rows

Printing recordset values to the Immediate window is a good tutorial for processing recordsets. As you first start to work with ADO recordsets, you will frequently be interested in testing whether you populated them with the intended values. The following procedure, EasyLoop, successively prints all the rows of a recordset. A loop passes through all the records and prints the first two fields of each record, CustomerID and CompanyName.

Sub EasyLoop()
Dim rst1 As ADODB.Recordset
‘Instantiate and open recordset
Set rst1 = New ADODB.Recordset
With rst1
.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PMA Samples\Northwind.mdb;"
.Open "Customers", , , , adCmdTable
End With
‘Loop through recordset
Do Until rst1.EOF
Debug.Print rst1.Fields(0), rst1.Fields(1)
rst1.MoveNext
Loop
‘Clean up objects
rst1.Close
Set rst1 = Nothing
End Sub

Notice that the procedure has three parts. First, it instantiates a recordset and opens the recordset based on the Customers table. This table is the source for the recordset. The ActiveConnection property setting points the recordset at the database containing this table. The Open method for the Recordset object makes the rows of the source available to the procedure. Notice there are three vacant parameter spaces between the recordset’s source and the Options parameter, adCmdTable. Since the statement specifies an Options parameter, it must include placeholders for the intervening parameters of the ActiveConnection, CursorType, and LockType properties. The procedure uses an assignment statement for the recordset’s connection property, which is then used as the default value for the ActiveConnection parameter in the Open method and the Connection object is implicitly created. By not including values for the CursorType and LockType settings, the procedure accepts the default values of adOpenForwardOnly and adLockReadOnly.

The second part of the procedure loops through all the rows in the recordset while printing the first and second field values in each row to the Immediate window. The sample uses a Do loop to navigate through the recordset until reaching an EOF flag after the last record. The MoveNext method advances the cursor one row with each pass through the loop. The code demonstrates how to reference fields by their index number. The reference to the Fields collection for a recordset is not strictly necessary. For example, you can replace rst1.Fields(0) with rst1(0). If you know the field names and prefer to use them, you can replace the column index numbers with names, such as rst1.Fields("CustomerID") or rst1("CustomerID").

The third part of the procedure merely closes the Recordset object and sets its reference to Nothing. There is no need to close a Connection object since the code sample assigns a connection string rather than a Connection object to the recordset’s ActiveConnection property.

One weakness of the EasyLoop procedure is that it prints only the values of the fields you specifically request. The EasyLoop2a procedure that follows circumvents this difficulty. No matter how many fields the data source for a recordset has, the procedure automatically prints all of them. In addition, it labels each value with its field name. The main trick to iterating through all the fields is to include a For…Each…Next loop inside the Do loop. The For…Each…Next loop iterates through the Fields collection for each row in the recordset while constructing a string that includes the field name and value for each field in the row. After passing through all the fields in the row, the code sample uses the Left function to trim the trailing two characters, which are the field delimiters added inside the For…Each…Next loop.

Sub EasyLoop2a()
Dim rst1 As ADODB.Recordset
Dim fldMyField As ADODB.Field
Dim strForRow As String
‘Instantiate and open recordset
Set rst1 = New ADODB.Recordset
rst1.Open "customers", "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PMA Samples\Northwind.mdb;"
‘Loop through recordset and fields within rows
Do Until rst1.EOF
strForRow = ""
For Each fldMyField In rst1.Fields
strForRow = strForRow & _
fldMyField.Name & " = " & fldMyField.Value & "; "
Next fldMyField
strForRow = Left(strForRow, Len(strForRow) - 2)
Debug.Print strForRow & vbCrLf
rst1.MoveNext
Loop
‘Clean up objects
rst1.Close
Set rst1 = Nothing
End Sub

Figure 2-4 shows the last two rows of output. You can scroll right in the Immediate window to view the whole record, but it would be more convenient if you could see the whole record without having to scroll the window.

Figure 2-4 The Immediate window output from the EasyLoop2a procedure. (Image Unavailable)

The following adaptation of the preceding code sample breaks the line for a row into lines of about 60 characters. An If...ElseIf statement inserts as many as four vbCrLf dividers into each line for a customer. We cannot use a Select…Case statement in this situation because the condition is a compound one based on independent numeric and Boolean values. The procedure resets the Boolean values to their default values of False at the end of each customer.

Sub EasyLoop2b()
Dim rst1 As ADODB.Recordset
Dim fldMyField As Field
Dim strForRow As String
Dim bolGT60 As Boolean
Dim bolGT120 As Boolean
Dim bolGT180 As Boolean
Dim bolGT240 As Boolean
‘Instantiate and open recordset
Set rst1 = New ADODB.Recordset
rst1.Open "customers", "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PMA Samples\Northwind.mdb;"
‘Loop through recordset and fields with rows.
‘Insert vbCrLF after exceeding each multiple of 60 characters.
Do Until rst1.EOF
strForRow = ""
bolGT60 = False
bolGT120 = False
bolGT180 = False
bolGT240 = False
For Each fldMyField In rst1.Fields
strForRow = strForRow & _
fldMyField.Name & " = " & fldMyField.Value & "; "
If Len(strForRow) > 60 And bolGT60 = False Then
strForRow = strForRow & vbCrLf
bolGT60 = True
ElseIf Len(strForRow) > 120 And bolGT120 = False Then
strForRow = strForRow & vbCrLf
bolGT120 = True
ElseIf Len(strForRow) > 180 And bolGT180 = False Then
strForRow = strForRow & vbCrLf
bolGT180 = True
ElseIf Len(strForRow) > 240 And bolGT240 = False Then
strForRow = strForRow & vbCrLf
bolGT240 = True
End If
Next fldMyField
strForRow = Left(strForRow, Len(strForRow) - 2)
Debug.Print strForRow & vbCrLf
rst1.MoveNext
Loop
‘Clean up objects
rst1.Close
Set rst1 = Nothing
End Sub

Figure 2-5 shows the improvement in the formatting of the output to the Immediate window.

Figure 2-5 The Immediate window output from the EasyLoop2b procedure. (Image Unavailable)

Looping is an easy way to perform an operation on the rows and columns within a recordset. However, it is not the most efficient way to retrieve field values from a recordset. The NoEasyLoop procedure that follows uses the GetString method to retrieve and print all the fields from the first five rows of a recordset in one step:

Sub NoEasyLoop()
Dim rst1 As ADODB.Recordset
‘Instantiate and open recordset
Set rst1 = New ADODB.Recordset
rst1.Open "customers", _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PMA Samples\Northwind.mdb;"
‘Print records without a loop
Debug.Print rst1.GetString(adClipString, 5, "; ")
‘Clean up objects
rst1.Close
Set rst1 = Nothing
End Sub

The GetString method returns a recordset as a string. It can take up to five arguments; the code uses three of those arguments. According to the documentation, you must designate the adClipString constant as the first argument in order to assign values to the other optional arguments, which designate the column delimiters, row delimiters, and expressions for Null values. The second argument specifies the number of recordset rows to return. The preceding code sample requests the return of five rows. Leaving the second argument blank tells the method to return all the rows in the recordset. The third argument in the code sample designates a semicolon followed by a space as the column delimiter. The default column delimiter is a tab. The fourth and fifth arguments, neither of which appears in the code, specify a row delimiter and a representation for Null values. The default values for these arguments are a carriage return and a zero-length string.


NOTE:
In my experience, you can omit assigning a value of adClipString to the first argument and still specify values for row and column delimiters, as well as representations for Null values.

The GetString method replaces a pair of nested loops. If the defaults are acceptable, you can use the method without any arguments. This makes for a simple way to extract values from a recordset. Although nested loops are the intuitive way to retrieve values from a recordset, the GetString method can achieve a similar result in a single line of code.

Another method, GetRows, offers several benefits when retrieving recordset values. First, it captures the recordset values to memory in an array. This makes it possible to delete a Recordset object and still work with its values. At the very least, this reduces the resource load of an application. In Web applications where a server collects the data for many users, this advantage can be important. Second, you can achieve performance gains by working with an array in memory rather than on disk. These gains depend on the recordset being sufficiently small to fit in physical memory. Third, the ADO GetRows method syntax lets you specify a starting row and an array that denotes a subset of columns to retrieve from a recordset. Because you are storing the recordset values in memory, you should include just the minimum set of rows and fields necessary for your needs.

In using the GetRows method, you must literally reorient your way of thinking about the data in a recordset. This is because GetRows transposes the rows and columns from disk storage to a two-dimensional Variant array. When you declare the Variant array for the method’s results set, you do not need to specify the number of rows or columns for the array. However, when retrieving data from the array, you must remember that recordset rows populate columns (instead of rows) in the Variant array.

The GettingRows procedure shown next demonstrates a couple of approaches to retrieving recordset contents with the GetRows method. The first application invokes the method with no arguments. This approach reveals just how simple it is to capture a recordset to memory with the GetRows method. The syntax for this approach copies all the rows and all their columns from disk to memory. The loop for printing values to the Immediate window shows the syntax for iterating through copied recordset rows as columns in the array. Review Chapter 1. for an introduction to processing arrays with VBA.

Sub GettingRows()
Dim rst1 As ADODB.Recordset
Dim varArray As Variant
Dim int1 As Integer
‘Instantiate and open recordset
Set rst1 = New ADODB.Recordset
rst1.Open "customers", "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PMA Samples\Northwind.mdb;"
‘Copy all rows with all columns to varArray in memory
varArray = rst1.GetRows
‘Loop through array based on recordset, printing first two fields
For int1 = 0 To UBound(varArray, 2)
Debug.Print varArray(0, int1), varArray(1, int1)
Next int1
‘Restore the pointer to the first record, and copy
‘the first five rows with CustomerID and CompanyName
‘fields into varArray in memory
rst1.MoveFirst
varArray = rst1.GetRows(5, , _
Array("CustomerID", "CompanyName"))
‘Loop through array based on recordset
For int1 = 0 To UBound(varArray, 2)
Debug.Print varArray(0, int1), varArray(1, int1)
Next int1
‘Clean up objects
rst1.Close
Set rst1 = Nothing
End Sub

The second application of the GetRows method in the GettingRows procedure demonstrates just how easy it is to specify a subset of the rows to copy to memory. The code instructs the ADO interpreter to copy only five rows from disk to memory. These rows are from the top of the recordset because the use of the MoveFirst method precedes the use of the second GetRows method. By default, the GetRows method copies records starting from the current row. However, you can designate a bookmark for another record as the second argument if you want the method to start from a location other than the current row. The second application of GetRows also illustrates how to use the built-in Array function to specify a subset of fields to copy from disk to memory. In particular, it copies just the CustomerID and CompanyName column values from the Customers table.


NOTE:
Often you will want to start copying with the GetRows method from the first row, even when the current row has a different position. In this situation, use the intrinsic constant adBookmarkFirst for the method’s second parameter to point to the first row.

Adding, Editing, and Deleting Records

The preceding set of samples for printing recordsets all accepted the default settings for the CursorType and LockType properties. Recall that the default settings are forward-only and read-only. In other words, you cannot change existing rows in a recordset or add new ones with these default settings. When all you want to do is print recordset values, these property settings are fast and efficient. However, by overriding these default settings to change the cursor type to keyset with optimistic locking, you can readily modify the field values within the rows of a recordset. Because the samples in this section all modify recordsets, they all use these nondefault settings.

One way to modify a recordset is by adding a new row of field values. The AddAShipper function procedure shown next demonstrates an approach to this task using the AddNew method for an ADO recordset. The sample applies this method to a recordset based on the Shippers table in the Northwind database. This table has an AutoNumber field named ShipperID and two string fields named CompanyName and Phone. Since Access automatically assigns AutoNumber field values, the procedure only has to assign values to the CompanyName and Phone fields. The procedure inserts these assignments between a statement that invokes the AddNew method and another one that launches the Update method.

Function AddAShipper()
Dim rst1 As ADODB.Recordset
‘Set your cursor so that it is not read-only
‘in order to add a row
Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PMA Samples\Northwind.mdb;"
rst1.Open "Shippers", , adOpenKeyset, adLockOptimistic, _
adCmdTable
‘Invoke the AddNew method
With rst1
.AddNew
.Fields("CompanyName") = "Access 2002 Delivers"
.Fields("Phone") = "(555) 123-4567"
.Update
End With
‘Return ShipperID of new shipper
AddAShipper = rst1("ShipperID")
‘Clean up objects
rst1.Close
Set rst1 = Nothing
End Function

Because the AddAShipper sample uses a function procedure, it can return a value, such as the ShipperID value that Access assigns automatically. This AutoNumber field is the primary key for the Shippers table. If your application inserts records into both a main table and a related table, such as Orders and Order Details, the AutoNumber primary key field value from the main table would be essential for setting the foreign key values in the related table.

Another task that you likely will want to perform with recordsets is deleting records. The following pair of procedures shows one approach to this task. While this approach might not be optimal in some respects, it has the benefit of not requiring any special SQL syntax or even advanced ADO functionality. Basically, you loop through a recordset until you find a match. Then you just delete the matching record.

The code sample in the CallDeleteARecordsetRow and DeleteARecordsetRow procedures lets a user specify any field as a criterion for the row to delete. If a recordset contains multiple rows with the same field value, it deletes just the first of these rows. The CallDeleteARecordsetRow procedure includes the code to delete a row based on CompanyName, Phone, or ShipperID field values. The sample leaves uncommented only the syntax for deleting a row based on CompanyName.

Sub CallDeleteARecordsetRow()
Dim rst1 As ADODB.Recordset
Dim int1 As Integer
‘Set your cursor so that it is not read-only
‘in order to delete
Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PMA Samples\Northwind.mdb;"
rst1.Open "Shippers", , adOpenKeyset, adLockOptimistic, _
adCmdTable
‘Delete a shipper indexed by CompanyName or Phone
DeleteARecordsetRow rst1, "CompanyName", "Access 2002 Delivers"
‘DeleteARecordsetRow rst1, "Phone", "(555) 123-4567"
‘Delete a shipper indexed by ShipperID
‘just after adding it
‘int1 = AddAShipper()
‘DeleteARecordsetRow rst1, "ShipperID", int1
‘Clean up objects
rst1.Close
Set rst1 = Nothing
End Sub
Sub DeleteARecordsetRow(rst1 As ADODB.Recordset, _
FieldName As String, FieldValue As Variant)
‘Designate rst1 as repeated value in
‘With...End With statement
With rst1
‘Loop through recordset to find target
‘field value for selected field
Do Until .EOF
If .Fields(FieldName) = FieldValue Then
.Delete
Exit Do
End If
.MoveNext
Loop
End With
End Sub

The calling procedure, CallDeleteARecordsetRow, passes a recordset, field name, and a field value. The called procedure, DeleteARecordsetRow, loops through the recordset. If the loop uncovers a row with a field value that matches the input parameters, it deletes the row and exits the loop.

The sample enables you to delete a recordset row based on the ShipperID field by adding a new row and saving the ShipperID value for that new row. It does this by saving the return value of the AddAShipper function procedure in a memory variable named int1. Then it uses the value of that memory variable as an argument when it calls DeleteARecordsetRow. This aspect of the sample demonstrates how to use the value of an automatically added primary key to control another action. In this case, the other action is to delete a row, but it could just as easily be to insert rows in a related table.

The third sample in this section demonstrates how to update an existing field value in a recordset row with a new field value. The calling procedure, CallUpdateAFieldValue, passes four arguments to the called procedure, UpdateAFieldValue. In this case, the passed parameters are the recordset, the name of the field to update, and the old and new field values. The UpdateAFieldValue procedure loops through the recordset’s rows until it finds a match. Then it assigns the new field value and invokes the Update method for the recordset. This commits the changed value to disk. After performing the update, the sub procedure exits the loop and restores control to the calling routine. If multiple records have field values that match the OldFieldValue input parameter, the sub procedure updates just the first record. You can update all the rows with matching values by removing the Exit Do statement inside the loop.

Sub CallUpdateAFieldValue()
Dim rst1 As ADODB.Recordset
‘Set your cursor so that it is not read-only
‘in order to udpate a record
Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PMA Samples\Northwind.mdb;"
rst1.Open "Shippers", , adOpenKeyset, adLockOptimistic, _
adCmdTable
‘Call updating routine, and pass it update info
UpdateAFieldValue rst1, "CompanyName", "Access 2002 Delivers", _
"Access 2002 Delivers More"
‘Clean up objects
rst1.Close
Set rst1 = Nothing
End Sub
Sub UpdateAFieldValue(rst1 As ADODB.Recordset, _
FieldName As String, _
OldFieldValue As Variant, _
NewFieldValue As Variant)
‘Designate rst1 as repeated value in
‘With...End With statement
With rst1
‘Loop through recordset to find target
‘field value for selected field
Do Until .EOF
If .Fields(FieldName) = OldFieldValue Then
.Fields(FieldName) = NewFieldValue
.Update
Exit Do
End If
.MoveNext
Loop
End With

End Sub

Finding Records

Another common task with a recordset is to find one or more records that meet specified criteria. Access offers several approaches to this task. With earlier versions of Access, many developers used one or more variations of the Find method. Access 2000 introduced a single Find method that consolidates the functionality of the earlier Find methods. If your applications used the earlier Find methods, you can achieve the same results with the consolidated method that Access 2000 introduced. Access 2002 adds a new capability to the Find method initially made available to Access developers with Access 2000.

The following code shows a simple application of the Find method that searches for a record with a customer ID that begins with the letter D. When it finds a record matching its criteria, the method relocates the current record to that location. The code prints the CustomerID, ContactName, and Phone fields to confirm exactly which record matches the criterion.

Sub FindAMatch()
Dim rst1 As Recordset
‘Instantiate and open recordset
Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PMA Samples\Northwind.mdb;"
rst1.Open "Customers", , adOpenKeyset, adLockPessimistic, _
adCmdTable
‘Find the first row with a CustomerID beginning with D,
‘and print the result
rst1.Find ("CustomerID Like ‘D*’")
Debug.Print rst1("CustomerID"), rst1("ContactName"), rst1("Phone")
‘Clean up objects
rst1.Close
Set rst1 = Nothing
End Sub

One drawback to the FindAMatch procedure is that it searches for a single match to the criteria and then stops immediately after finding it. The code that follows, which shows the FindAMatch2 procedure, discovers all the records that match the criterion statement. This simple application reveals more of the flexibility of the Find method:

Sub FindAMatch2()
Dim rst1 As Recordset

‘Instantiate and open recordset
Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PMA Samples\Northwind.mdb;"
rst1.Open "Customers", , adOpenKeyset, _
adLockPessimistic, adCmdTable
‘Open an infinite loop for all records matching
‘the criterion
Do
rst1.Find ("CustomerID Like ‘D*’")
If rst1.EOF Then
‘Exit the procedure when no more matches exist
Exit Do
End If
Debug.Print rst1.Fields("CustomerID"), rst1("ContactName"), _
rst1("Phone")
rst1.MoveNext
Loop
‘Clean up objects
rst1.Close
Set rst1 = Nothing
End Sub

The trick to finding all the records that match the search criterion is to embed the Find method in an infinite Do loop. When the Find method sets the recordset’s EOF property to True, there are no additional matching records. In this case, the code executes an Exit Do statement to exit the loop. As long as Find keeps discovering new matches, the procedure prints CustomerID, ContactName, and Phone fields in the Immediate window. After printing a matching record, the procedure advances the current record by one. Without this, the Find method would repeatedly return the same record.

One innovation available to those using Access 2002 is the dynamic index capability of the Cursor Service. Access 2002 ships natively with an ADO version that supports dynamic indexes. To speed the operation of the Find method, developers can create dynamic indexes on the fly for record sources that do not have an index on a search field. Creating a dynamic index is a two-step process. First, assign the adUseClient intrinsic constant to the recordset’s CursorLocation property. Second, set the Optimize property to True in the Properties collection so that the field will gain the dynamic index. The index goes out of scope when you close the procedure, but you can force the index’s removal by setting the field’s Optimize property to False.


NOTE:
You can acquire the dynamic index feature in Access 2000 by installing a more recent ADO version than the one initially shipped with Access 2000.

The following pair of procedures demonstrates how to use a dynamic index to speed the search for all the customers from a particular country. The first procedure, CallFindCustomersInACountry, assigns a search string value and passes it to the second procedure, FindCustomersInACountry. Notice that the sample code uses a pound sign (#) to delimit the country string within the criterion string. With Access 2002, you can use either # or an apostrophe (') for this purpose.

Before opening a recordset on the Customers table, the second procedure turns on the Cursor Service by setting the CursorLocation property to adUseClient. Then after the recordset is open, the procedure sets the Country field’s Optimize priority to True. This causes the creation of the index. Next the procedure performs a normal search to find all the customers from the country designated by strCriterion. In the cleanup process for the procedure, there is nothing special you need to do with the index. It goes out of scope automatically.

Sub CallFindCustomersInACountry()
Dim strCriterion As String
‘Set string for country criterion, and pass it
‘to the routine to do the search
strCriterion = "Country = #USA#"
FindCustomersInACountry strCriterion
End Sub
Sub FindCustomersInACountry(strCriterion As String)
Dim rst1 As ADODB.Recordset
Dim int1 As Integer
‘Instantiate and open recordset; invoke the Cursor Service by
‘setting the CursorLocation property to adUseClient
Set rst1 = New ADODB.Recordset
With rst1
.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PMA Samples\Northwind.mdb;"
.CursorLocation = adUseClient
.Open "customers", , adOpenKeyset, _
adLockPessimistic, adCmdTable
End With
‘Create a dynamic index on Country
rst1.Fields("Country").Properties("Optimize") = True
‘Find the longest ContactName field, and add one to it
int1 = FindTheLongest(rst1, "ContactName") + 1
‘Open an infinite loop for all records matching
‘the criterion
rst1.MoveFirst
Do
rst1.Find (strCriterion)
If rst1.EOF Then
‘Exit the procedure when no more matches exist
Exit Sub
End If
Debug.Print rst1.Fields("CustomerID"), rst1("ContactName") & _
String(int1 - Len(rst1("ContactName")), " ") & " " & rst1("Phone")
rst1.MoveNext
Loop
‘Clean up objects
rst1.Close
Set rst1 = Nothing
End Sub

Seeking Records

The Seek method is another recordset method that can facilitate finding records in a record source. This method is exclusively used for recordsets that specify a table as their source. In addition, you must set the Options parameter for the recordset Open method to adCmdTableDirect, instead of setting it to adCmdTable or leaving it blank. Furthermore, the table serving as a record source must have an index on the field or fields that your application searches to find matches for criterion values. ADO does not offer built-in support for creating dynamic indexes for the Seek method, but your application can programmatically create an index using the ADOX library for Jet and the SQL-DMO library for SQL Server. The Seek method also requires a server-side cursor. Using any value other than adUseServer for a recordset’s CursorLocation property disables the method.


NOTE:
The Seek method is not a core ADO data access feature. ADO providers can optionally offer it. Use the recordset Supports method with adSeek as an argument to return a Boolean indicating whether a provider offers the Seek method. To seek on an index, apply the method to a recordset after opening it.

The Seek method searches for a key value or values, and it optionally takes a SeekOption parameter that can guide the operation of the method. If the index for the Seek method has just one column, you can specify only a single criterion value. If the index for a recordset relies on multiple columns, use an Array function to specify values for all the columns in the index. This capability to search concurrently on multiple columns is one feature distinguishing the Seek method from the Find method. The Seek method makes available the recordset in the order of the index and positions the current record at the first record matching the criteria.

You can refine the search behavior of the Seek method with some settings for its SeekOptions parameter using one of the SeekEnum enums. The SeekOptions setting appears immediately after the key value or values for a Seek method. The syntax for the Seek method with both key values and SeekOptions is rst.Seek <Keyvalues>, <SeekOptions>. Table 2-5 lists the SeekEnum members with their values and behavior.

Table 2-5 Intrinsic Constants for the SeekEnum

Constant Value Behavior
adSeekFirstEQ 1 Seek the first record on the index or indexes matching the key value or values.
adSeekLastEQ 2 Seek the last record on the index or indexes matching the key value or values.
adSeekAfterEQ 4 Seek the first record (or the one after it when no match exists) on the index or indexes matching the key value or values.
adSeekAfter 8 Seek the first record after that potential match to the key value or values on the index or indexes.
adSeekBeforeEQ 16 Seek the first record (or the one before it when no match exists) on the index or indexes matching the key value or values.
adSeekBefore 32 Seek the first record before a potential match to the key value or values on the index or indexes.

The SeekingUnShippedOrders procedure that follows illustrates three ways to use the Seek method. Before actually invoking the Seek method, your application must properly prepare the recordset. First, assign the Index property so that it contains the values for which you want to search. Second, open the recordset with the adCmdTableDirect setting for the Options parameter. Third, ensure the CursorLocation property has a setting of adUseServer. This is the default setting, so you don’t need to assign the property a setting unless your application has changed its default setting. The following procedure demonstrates the first two steps. The third step is not necessary in this sample.

Sub SeekingUnshippedOrders()
Dim rst1 As ADODB.Recordset
‘Instantiate and open recordset; Seek method requires
‘index assignment, adCmdTableDirection Open option, and
‘adUserServer setting for CursorLoction (default)
Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PMA Samples\Northwind.mdb;"
rst1.CursorType = adOpenKeyset
rst1.LockType = adLockOptimistic
rst1.Index = "ShippedDate"
rst1.Open "Orders", , , , adCmdTableDirect
‘Print first order not shipped
rst1.Seek Null, adSeekFirstEQ
Debug.Print rst1("OrderID"), rst1("OrderDate")
‘Print last order not shipped
rst1.Seek Null, adSeekLastEQ
Debug.Print rst1("OrderID"), rst1("OrderDate")
‘Print all orders not shipped
rst1.Seek Null
Do
Debug.Print rst1("OrderID"), rst1("OrderDate")
rst1.MoveNext
Loop Until IsNull(rst1("ShippedDate")) = False
‘Clean up objects
rst1.Close
Set rst1 = Nothing
End Sub

The first two instances of the Seek method in SeekingUnShippedOrders both demonstrate the syntax for the Keyvalues and SeekOptions parameters. The first instance of the Seek method sets the cursor at the first record in the Orders table that is unshipped. This record has the order ID 11008. The steps for opening the rst1 recordset on the Orders table arrange the records in order on the ShippedDate index, so that the special ordering indicated by the Seek method’s Keyvalues and SeekOptions parameters will work. The second instance of the Seek method in the SeekingUnShippedOrders procedure sets the cursor at the last Orders table record with a Null value for the ShippedDate field. This record has the order ID 11077. The third instance of the Seek method in the preceding procedure searches for all unshipped orders without specifying a SeekOptions setting. Its following Do loop iterates through the reordered recordset to print the OrderID field and OrderDate field for each record with a Null ShippedDate field value.

The SeekWith2IndexValues procedure demonstrates the syntax for designating a seek for two criterion values: the OrderI D and ProductID fields of the primary key for the Order Details table. The procedure also demonstrates how to reuse a Recordset object. In the first use of the rst1 recordset, the procedure searches for all line items matching an OrderID input by the user. The procedure uses an InputBox function nested inside a CInt function to permit a user to input an OrderID field value. It then returns the OrderID and ProductID for the line items associated with that record. This first seek relies on the OrderID index for the Order Details table.

Sub SeekWith2IndexValues()
Dim rst1 As ADODB.Recordset
Dim int1 As Integer
Dim int2 As Integer
Dim int3 As Integer
‘Instantiate and open recordset; Seek method requires
‘index assignment, adCmdTableDirection Open option, and
‘adUserServer setting for CursorLocation (default)
Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PMA Samples\Northwind.mdb;"
rst1.CursorType = adOpenKeyset
rst1.LockType = adLockOptimistic
rst1.Index = "OrderID"
rst1.Open "Order Details", , , , adCmdTableDirect
‘Search for a user-specified OrderID;
‘save OrderID and ProductID of order’s last line item
int1 = CInt(InputBox("Input OrderID: "))
rst1.Seek int1
Do Until rst1("OrderID") <> int1
Debug.Print rst1("OrderID"), rst1("ProductID")
rst1.MoveNext
If rst1.EOF Then Exit Do
Loop
rst1.MovePrevious
int2 = rst1("OrderID")
int3 = rst1("ProductID")
‘Close and reopen Order Details to seek
‘last line item in the order
rst1.Close
rst1.Index = "PrimaryKey"
rst1.Open "Order Details", , , , adCmdTableDirect
rst1.Seek Array(int2, int3)
Debug.Print rst1("OrderID"), rst1("ProductID"), _
FormatCurrency(rst1("UnitPrice")), _
rst1("Quantity"), FormatPercent(rst1("Discount"), 0)
‘Clean up objects
rst1.Close
Set rst1 = Nothing
End Sub

The second instance of the Seek method in the SeekWith2IndexValues procedure seeks the last item for the OrderID that a user input. At the conclusion of the first Seek instance, the procedure saves the OrderID and ProductID field values for the last line item in the order a user referenced. Then the procedure closes the recordset to assign a new index. The index is the primary key that relies on both OrderID and ProductID. Next the procedure reopens the recordset. The new index is critical for permitting the use of a compound criterion that includes values for both the OrderID and ProductID field values. Notice the use of the Array function for indicating more than one key value. The cursor points to the record in the recordset for the line item sought. The procedure prints it while formatting the UnitPrice and Discount columns.

Filtering Records

The Find method can go through a recordset sequentially and disclose matches one at a time. The method does not create another version of the recordset that contains all the records that match the criterion values. When you need a new or alternate recordset containing just the matches, your application needs a different approach. The Seek method offers a partial solution to this Find method deficiency, but the Seek method has special requirements that restrict its applicability, such as the need for an index. In addition, the Seek method does not truly exclude nonmatching records. It merely positions the current record at the first matching record.

The recordset Filter property offers a second solution to the Find method’s inability to return a subset of a recordset based on one or more fields. This property lets you designate a simple criterion for a field, and it returns a filtered version of the original recordset with only those records that match the criterion value or values. By setting the Filter property to any of a series of constants instead of to a criterion string, you can achieve special effects for database replication or for updating a remote data source. One filter constant, adFilterNone, removes the filter setting from a recordset and restores the original rowset. The Filter property also resolves another shortcoming of the Find method: it can select records based on more than one field value. Furthermore, it does not require an index to accomplish this goal, as does the Seek method.

The following two procedures filter a recordset based on the Customers table in the Northwind database. The FilterRecordset procedure manages the overall use of the Filter property, prints the results set, clears the filter, and then prints the results set again. The FilterRecordset procedure relies on the FilterLikeField function. This custom function manages the setting of the Filter property based on parameters passed to it by the FilterRecordset procedure, and it returns the filtered recordset.

Sub FilterRecordset()
Dim rst1 As ADODB.Recordset
‘Instantiate and open recordset
Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PMA Samples\Northwind.mdb;"
rst1.Open "Customers", , , , adCmdTable
‘Filter recordset, and print filtered result
Set rst1 = _
FilterLikeField(rst1, "CustomerID", "D*")
Debug.Print rst1.GetString
‘Restore recordset, and print restored result
rst1.Filter = adFilterNone
Debug.Print rst1.GetString
‘Clean up objects
rst1.Close
Set rst1 = Nothing
End Sub
Function FilterLikeField(rst1 As ADODB.Recordset, _
strField As String, strFilter As String) As ADODB.Recordset
‘Set a filter on the specified Recordset object, and then
‘return the filtered recordset
rst1.Filter = strField & " LIKE ‘" & strFilter & "‘"
Set FilterLikeField = rst1
End Function

The FilterRecordset procedure starts by creating and opening the rst1 recordset. Next it applies a filter by calling the FilterLikeField function, which takes three arguments and returns a filtered recordset based on them. FilterRecordset assigns the filtered return set to rst1 and prints the filtered recordset to confirm the result.

The arguments to FilterLikeField include rst1, a field name on which to filter records, and a filter criterion value, which can include any legitimate expression for the Like operator used by FilterLikeField. FilterRecordset passes D* to find just the records that have a CustomerID beginning with the letter D. The Filter property does not restrict you to filtering with the LIKE operator. Other acceptable operators include <, >, <=, >=, <>, and =. You can also include AND and OR operators in your criteria expressions to combine two or more criteria expressions based on the other legitimate operators.

The Filter property restricts your criteria expressions to those of the form <FieldName> <Operator> <Value>. However, some Filter intrinsic constants that are members of the FilterGroupEnum enumenable special uses of the property. The FilterRecordset procedure uses the adFilterNone constant to restore a recordset by removing its filters. You can also use other FilterGroupEnum enum members to resolve conflicts associated with performing a batch update against a remote data source.

The sample demonstrating compound filters uses two procedures. The first procedure, CallFilterOnCountryAndCity, sets the criteria strings for both country and city, the two filter fields for the Customers table. Then the sample calls FilterOnCountryAndCity, the procedure that applies the filter, and it saves a reference to the filtered recordset. The main procedure concludes by printing selected fields. Before printing field values, however, it uses the FindTheLongest function procedure to determine the longest character for each of two fields.

Sub CallFilterOnCountryAndCity()
Dim rst1 As ADODB.Recordset
Dim strCountry As String
Dim strCity As String
Dim int1 As Integer
Dim int2 As Integer
‘Set Country and City criteria
strCountry = "USA"
strCity = "Portland"
‘Call Filter routine, and store reference to
‘filtered return set
Set rst1 = FilterOnCountryAndCity(strCountry, strCity)
‘Get the longest lengths for the
‘ContactName and CompanyName fields
int1 = FindTheLongest(rst1, "ContactName") + 1
rst1.MoveFirst
int2 = FindTheLongest(rst1, "CompanyName") + 1
‘Print selected fields from filtered recordset
rst1.MoveFirst
Do Until rst1.EOF
Debug.Print rst1("ContactName") & _
String(int1 - Len(rst1("ContactName")), " ") & _
rst1("CompanyName") & _
String(int2 - Len(rst1("CompanyName")), " ") & _
rst1("City"), rst1("Country")
rst1.MoveNext
Loop
‘Clean up objects
rst1.Close
Set rst1 = Nothing
End Sub
Function FilterOnCountryAndCity(strCountry As String, _
strCity As String) As ADODB.Recordset
Dim rst1 As ADODB.Recordset
‘Instantiate and open recordset
Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PMA Samples\Northwind.mdb;"
rst1.Open "Customers", , adOpenKeyset, adLockOptimistic, adCmdTable
‘Set Filter property
rst1.Filter = "Country = ‘" & strCountry & _
"‘ AND City = ‘" & strCity & "‘"
‘Return filtered recordset
Set FilterOnCountryAndCity = rst1
End Function

Persisting Recordsets

By using the Save method, you can persist a recordset. Later, with the Open method and the MSPersist provider, you can open a saved recordset. This capability serves a couple of purposes particularly well. First, this capability comes in handy when you need to take a snapshot of a record source for viewing later on a computer that is disconnected from the data source for the records. Second, when remote server access is either slow or unreliable, using a saved recordset based on a record source from a remote server gives an application fast, reliable data access. This is especially convenient for read-only record sources. In either context, you can modify a saved record source and later update the remote source with the UpdateBatch method.

You can persist a recordset’s contents with either of two formats, a proprietary format or an XML format. Designate your preferred format with a member of the PersistFormatEnum enum in the statement using the Save method. Saving a file in XML format enables another application to open and view the recordset’s data. The Open method, which inserts the file’s contents into a recordset, works equally well with either format. For example, you can readily view the saved XML file in the Microsoft Internet Explorer browser shipping with Office XP. Internet Explorer allows you to view the XML tags and data. The proprietary format is not so easy to view natively. However, users can readily open a file in the proprietary format and then examine its contents in the recordset to which the Open method deposits the file’s contents.

The following sample opens a recordset on the Shippers table from the Northwind database on the C share of a remote computer named cabarmada. It saves the recordset to a drive on the local computer. The Save method designates an XML file format by using the adPersistXML intrinsic constant. Specifying the adPersistADTG intrinsic constant saves the recordset in the proprietary format.

Sub SaveAccessDBRecordSource()
On Error GoTo SaveAccessDB_Trap
Dim cnn1 As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim varLocalAddress As Variant
‘Create the connection
Set cnn1 = New ADODB.Connection
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\cabarmada\c\Program Files\Microsoft Office\" & _
"Office10\Samples\Northwind.mdb;"
‘Open recordset on Shippers table in the copy of
‘Northwind.mdb on a machine named cabarmada
Set rst1 = New ADODB.Recordset
rst1.Open "Shippers", cnn1, adOpenKeyset, adLockOptimistic, adCmdTable
‘Specify a local address for the recordset based on Shippers table,
‘and save the recordset in XML format to that address
varLocalAddress = "C:\PMA Samples\Chapter 02\ShippersFromCabarmada.xml"
rst1.Save varLocalAddress, adPersistXML
SaveAccessDB_Exit:
rst1.Close
Set rst1 = Nothing
cnn1.Close
Set cnn1 = Nothing
Exit Sub
SaveAccessDB_Trap:
If err.Number = 58 Then
‘Kill the temporary file of table data because it already exists,
‘and resume writing to the file
Kill varLocalAddress
Resume
Else
MsgBox "Procedure failed with an error number = " _
& err.Number & ", " & vbCrLf & "and an " & _
"error description of """ & _
err.Description & """" & ".", vbInformation, _
"Programming Microsoft Access Version 2002"
End If
End Sub

The sample’s error trap is slightly more robust than necessary, but you will typically need an error trap with a procedure that saves a recordset to a file. This is because you will generate a run-time error if you attempt to save a recordset over an existing file. Since the number for this specific error is 58, the sample kills the old version of the file and resumes executing the line of code with the Save method.

Figure 2-6 shows the saved file open in Internet Explorer. Notice that the XML tags show the schema information, such as data type and length for recordset fields, as well as the data for the file’s contents.

The OpenSavedRecordset sample that appears next illustrates the syntax for opening and browsing the contents of the file saved in the previous code sample. After declaring and instantiating a recordset, the procedure invokes the recordset’s Open method. The source for the Open method is the path and filename for the recordset saved previously. In order to use the Open method this way, you must designate MSPersist as the provider for the connection argument. Notice that the Options argument is adCmdFile. I include this just to remind you of the general usefulness of specifying an Options argument. In this particular case, the argument is not necessary since ADO assumes an adCmdFile argument when you designate MSPersist as the provider.

Figure 2-6 The XML contents as seen from Internet Explorer for the file saved by the SaveAccessDBRecordSource procedure. (Image unavailable)

After opening the file as a recordset, the code sample loops through the columns of each row as it prints the field name and value of each element in the recordset. With this simple code, you can open the local file and view its contents even when you are disconnected from the original version of the Shippers table on the cabarmada computer.

Sub OpenSavedRecordset()
Dim rst1 As ADODB.Recordset
Dim int1 As Integer
‘Instantiate recordset, and open from file with Persistence provider
Set rst1 = New ADODB.Recordset
rst1.Open _
"C:\PMA Samples\Chapter 02\ShippersFromCabarmada.xml", _
"Provider=MSPersist;", _
, , adCmdFile
‘Loop through all rows; with each row loop through all columns
Do Until rst1.EOF
For int1 = 0 To rst1.Fields.Count - 1
Debug.Print rst1.Fields(int1).Name, rst1(int1)
Next int1
rst1.MoveNext
Loop
‘Clean up objects
rst1.Close
Set rst1 = Nothing
End Sub

As developers, we can use this technology to empower employees that must travel, enter data on a disconnected recordset, and then update the connected recorded source when they return to the office. By using the recordset’s UpdateBatch method along with the ability to persist recordsets, you can readily meet this need. The SaveEditInsertShippersAtNorthwindCS procedure demonstrates the syntax that can help you empower "road warrior" employees. The sample creates a local recordset based on the Shippers table in the NorthwindCS database on the cab2000 SQL server. (Recall that the NorthwindCS database ships with Office XP.) Then the procedure saves this recordset to a local drive.

Sub SaveEditInsertShippersAtNorthwindCS()
On Error GoTo EditInsertCS_Trap
Dim cnn1 As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim varLocalAddress As Variant
‘Local address for saved recordset
varLocalAddress = "c:\PMA Samples\Chapter 02\ShippersFromCab2000.xml"
‘Instantiate and open a recordset
Set rst1 = New ADODB.Recordset
rst1.Open "SELECT ShipperID, CompanyName, Phone FROM Shippers", _
"Provider=SQLOLEDB;Data Source=cab2000;User Id=sa;" & _
"Password=password;Initial Catalog=NorthwindCS;", _
adOpenDynamic, adLockOptimistic, adCmdText
‘Persist the recordset to disk as an XML file; this saves it
‘based on the Shippers table in the NorthwindCS database on the
‘cab2000 server
Kill varLocalAddress
rst1.Save varLocalAddress, adPersistXML
‘Open the XML file locally
Set rst1 = New ADODB.Recordset
rst1.Open varLocalAddress, "Provider=MSPersist;", , , adCmdFile
‘Find a record, and edit it in the local cache
rst1.Find "CompanyName = ‘Federal Shipping’"
If rst1.EOF Then
Debug.Print "Name not found."
Exit Sub
End If
rst1("CompanyName") = "Federal Shippers"
rst1.Update
‘Add a record to the local cache
rst1.AddNew
rst1("CompanyName") = "CAB Movers"
rst1("Phone") = "(555) 234.5678"
rst1.Update
‘After committing the edit locally, connect to the remote
‘data source and update the remote source
Set cnn1 = New ADODB.Connection
cnn1.Open "Provider=SQLOLEDB;Data Source=cab2000;User Id=sa;" & _
"Password=password;Initial Catalog=NorthwindCS;"
rst1.ActiveConnection = cnn1
rst1.UpdateBatch
EditInsertCS_Exit:
‘Clean up objects
rst1.Close
Set rst1 = Nothing
cnn1.Close
Set cnn1 = Nothing
Exit Sub
EditInsertCS_Trap:
If err.Number = 53 Then
‘Tried to kill a file that doesn’t exist, so simply resume
Resume Next
ElseIf err.Number = 58 Then
‘Kill the temporary file of table data if it already exists,
‘and resume writing to the file
Kill varLocalAddress
Resume
Else
MsgBox "Procedure failed with an error number = " _
& err.Number & ", " & vbCrLf & "and an " & _
"error description of """ & _
err.Description & """" & ".", vbInformation, _
"Programming Microsoft Access Version 2002"
End If
End Sub

A traveling employee can use this file-based version of a recordset to edit existing data and enter new data. The code sample simulates these tasks programmatically. The application of the Update method commits the changes to the recordset’s local cache, but another Save method could transfer those updates from memory to the disk-based version of the local recordset.

After returning from the field, employees will want to update the database at headquarters with their entries from the road. They can accomplish this goal by running a program you prepare that opens the file-based version of the recordset, connects the recordset to the server at headquarters, and invokes the UpdateBatch method for the local recordset. The UpdateBatch method, along with the recordset Filter property, enables developers to reconcile conflicts when they occur. See the Access Help files for more detailed coverage of conflict resolution issues. Database replication, another Access feature, offers more elaborate support for this capability, but some developers might care to build custom solutions using this technology.


NOTE:
If you run the same procedure as SaveEditInsertShippersAt-NorthwindCS, except for updating its connections so that it initially opens and later updates the Access Northwind database file, it will fail. The precise error description is "Cannot update 'ShipperID'; field not updateable." The solution to this problem for Access database files is to construct the initial file-based recordset without any primary keys that have an AutoNumber data type. Use an SQL statement as your source for the saved recordset to accomplish this. The companion CD includes a sample procedure, SaveEditInsertShippersAtNorthwind, with the detailed code to perform this task.

Sorting Recordsets

Many database applications benefit from a sorted recordset. Therefore, Microsoft added a Sort property to ADO recordsets. This is a very natural property for Access developers to use. Simply set the Sort property for a recordset to the list of field names on which you want the recordset sorted. Represent the field names as a comma-delimited string. The default sort order is ascending. However, you can explicitly set an ascending sort order for any field by trailing the field name with a blank followed by the ASC keyword. Replace the ASC keyword with DESC if you want a recordset sorted in descending order on the values within a field.

In order for Sort property assignments to operate, you must assign the adUseClient intrinsic constant to the recordset’s CursorLocation property. ADO takes advantage of user-defined indexes for tables when sorting recordsets. When no user-defined indexes exist, ADO constructs a temporary index to help speed the sorting of recordset rows on a field. You can clear any special sort (and delete temporary indexes that are automatically constructed) by assigning an empty string to the Sort property. You can also dynamically rearrange the order of rows within a recordset by designating a modified string of field names for the recordset’s Sort property.

The following code sample demonstrates how to set the Sort property, while revealing the syntax for most of its typical uses. The sample begins by creating a recordset based on the Customers table. Notice that the sample assigns the adUseClient intrinsic constant to the recordset’s CursorLocation property. This is essential. (Without making the setting, the Sort property assignments fail at run time.)

The procedure prints an excerpt of columns for the first five records in the currently sorted version of the recordset four different times. The first set of five records shows the default order, which is the order of the primary key or the order of entry for rows if no primary key exists. You don’t need to set the Sort property to return records in this order (unless you are clearing a prior sort setting). The second set of five records sorts in ascending order on the City field values. To impose this order, the procedure merely assigns the string "City" to the Sort property. The next set of five records also sorts records on City field values, but in descending order. The Sort property setting for this order merely trails the field name by a space and the DESC keyword. The final sort order is by City field values within Country field values. Place the outer sort key, Country, first in the string for the Sort property so that the City field values appear in ascending order within each country.

Sub SortCustomersDefaultCityUpDown()
Dim rst1 As ADODB.Recordset
Dim int1 As Integer
Dim int2 As Integer
Dim int3 As Integer
Dim int4 As Integer
‘Instantiate and open recordset based on customers;
‘set CursorLocation to adUseClient to support sorting
Set rst1 = New ADODB.Recordset
rst1.CursorLocation = adUseClient
rst1.Open "Customers", "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PMA Samples\Northwind.mdb;"
‘Find and save maximum length of CustomerID, CompanyName,
‘and ContactName fields
int2 = FindTheLongest(rst1, "CustomerID") + 1
rst1.MoveFirst
int3 = FindTheLongest(rst1, "CompanyName") + 1
rst1.MoveFirst
int4 = FindTheLongest(rst1, "ContactName") + 1
‘Print records in default order
rst1.MoveFirst
Debug.Print "Default Order" & vbCr
For int1 = 1 To 5
Debug.Print rst1("CustomerID") & _
String(int2 - Len(rst1("CustomerID")), " ") & _
rst1("CompanyName") & _
String(int3 - Len(rst1("CompanyName")), " ") & _
rst1("ContactName") & _
String(int4 - Len(rst1("ContactName")), " ") & _
rst1("Phone"), rst1("City"), rst1("Country")
rst1.MoveNext
Next int1
‘Sort by City and print
rst1.Sort = "City"
rst1.MoveFirst
Debug.Print String(2, vbCr) & "Order by City Ascending" & vbCr
For int1 = 1 To 5
Debug.Print rst1("CustomerID") & _
String(int2 - Len(rst1("CustomerID")), " ") & _
rst1("CompanyName") & _
String(int3 - Len(rst1("CompanyName")), " ") & _
rst1("ContactName") & _
String(int4 - Len(rst1("ContactName")), " ") & _
rst1("Phone"), rst1("City"), rst1("Country")
rst1.MoveNext
Next int1
‘Sort by descending City order and print
rst1.Sort = "City DESC"
rst1.MoveFirst
Debug.Print String(2, vbCr) & "Order by City Descending" & vbCr
For int1 = 1 To 5
Debug.Print rst1("CustomerID") & _
String(int2 - Len(rst1("CustomerID")), " ") & _
rst1("CompanyName") & _
String(int3 - Len(rst1("CompanyName")), " ") & _
rst1("ContactName") & _
String(int4 - Len(rst1("ContactName")), " ") & _
rst1("Phone"), rst1("City"), rst1("Country")
rst1.MoveNext
Next int1
‘Sort by City within Country and print
rst1.Sort = "Country, City"
rst1.MoveFirst
Debug.Print String(2, vbCr) & _
"Order by Country Ascending, City Ascending" & vbCr
For int1 = 1 To 5
Debug.Print rst1("CustomerID") & _
String(int2 - Len(rst1("CustomerID")), " ") & _
rst1("CompanyName") & _
String(int3 - Len(rst1("CompanyName")), " ") & _
rst1("ContactName") & _
String(int4 - Len(rst1("ContactName")), " ") & _
rst1("Phone"), rst1("City"), rst1("Country")
rst1.MoveNext
Next int1
‘Clean up objects
rst1.Close
Set rst1 = Nothing
End Sub

You can combine the Sort property for recordsets with other Access programming features to achieve useful results that are difficult to obtain programmatically any other way. For example, it is easy to list all the tables in an Access database file with any number of properties sorted however you need. The OpenSchema method for the Connection object returns recordsets with fields describing classes of objects within a database. This chapter demonstrated how to use this method earlier, but it did not show you how to customize the order of the output. This section demonstrates how to use the Save and Open methods in combination with the Sort property to arrange OpenSchema output to suit your needs.

When used with a parameter set to adSchemaTables, the OpenSchema method outputs a recordset of table properties with information ordered by table name. However, this list mixes information about all types of tables in rows adjacent to one another. Therefore, it is difficult to find information about a particular user-defined table, view, or system table. Figure 2-7 shows the output to the Immediate window from a VBA program that sorts tables by table name, within table type. Along with the other two columns, this feature makes it easy to look up when a table was last modified or created.

Figure 2-7 Program output from the SortedTableTypes procedure, showing a list of tables sorted by table name within table type from the Northwind Access database file. (Image unavailable)

The following pair of procedures generate the output shown in Figure 2-7. While this listing is long, you are already familiar with all the techniques that it integrates. The calling procedure, CallSortedTableTypes, designates a string that points at a database file and passes that string to the second procedure, SortedTableTypes. You can get a listing like the one in Figure 2-7 for any Access database file, just by changing the string.

The second procedure generates a recordset about the tables in a database file by invoking the OpenSchema method with the adSchemaTables intrinsic constant as an argument. You cannot sort the output from the OpenSchema method because it sets the CursorLocation property to adUseServer. Therefore, the procedure saves the recordset and opens it again. This two-step process creates a version of the recordset with a CursorLocation property equal to adUseClient. In addition, the process yields a local version of the recordset for future use (for example, when you are disconnected from the computer with the database file). After changing the CursorLocation property setting, the procedure just needs to set the Sort property and print the results to the Immediate window. By specifying TABLE_TYPE before TABLE_NAME, the procedure forces the output to show table names sorted alphabetically within the table type.

Sub CallSortedTableTypes()
Dim str1 As String
‘Specify database for which to generate a sorted list of tables
str1 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PMA Samples\Northwind.mdb;"
‘Call sub procedure to create sorted list of tables
SortedTableTypes str1
End Sub
Sub SortedTableTypes(str1 As String)
On Error GoTo SortedTableTypes_Trap
Dim cnn1 As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim rst2 As ADODB.Recordset
Dim int1 As Integer
Dim int2 As Integer
‘Instantiate and open connection
Set cnn1 = New ADODB.Connection
cnn1.Open str1
‘Save tables report in rst1
Set rst1 = cnn1.OpenSchema(adSchemaTables)
‘Since OpenSchema returns a server-based cursor, save original
‘returned recordset and reopen saved data with a new recordset;
‘the new recordset has a local cursor that you can sort
rst1.Save "C:\PMA Samples\Chapter 02\foo.adtg", adPersistADTG
Set rst2 = New ADODB.Recordset
rst2.Open "C:\PMA Samples\Chapter 02\foo.adtg", "Provider=MSPersist"
‘Sort copied data by table name within table type
rst2.Sort = "TABLE_TYPE, TABLE_NAME"
‘Compute maximum length of TABLE_NAME and TABLE_TYPE columns
int1 = FindTheLongest(rst2, "TABLE_NAME") + 1
rst2.MoveFirst
int2 = FindTheLongest(rst2, "TABLE_TYPE") + 1
rst2.MoveFirst
‘Loop through sorted results set from OPENSCHEMA query,
‘and print selected columns
Do Until rst2.EOF
Debug.Print rst2.Fields("TABLE_NAME") & _
String(int1 - Len(rst2("TABLE_NAME")), " ") & _
rst2.Fields("TABLE_TYPE") & _
String(int2 - Len(rst2("TABLE_TYPE")), " ") & _
rst2.Fields("DATE_MODIFIED"), _
rst2.Fields("DATE_CREATED")
rst2.MoveNext
Loop
SortedTableTypes_Exit:
rst1.Close
rst2.Close
cnn1.Close
Set rst1 = Nothing
Set rst2 = Nothing
Set cnn1 = Nothing
Exit Sub
SortedTableTypes_Trap:
If err.Number = 58 Then
‘Kill the temporary file of table data if it already exists,
‘and resume writing to the file
Kill "C:\PMA Samples\Chapter 02\foo.adtg"
Resume
Else
MsgBox "Procedure failed with an error number = " _
& err.Number & ", " & vbCrLf & "and an " & _
"error description of """ & _
err.Description & """" & ".", vbInformation, _
"Programming Microsoft Access Version 2002"
Resume SortedTableTypes_Exit
End If
End Sub

The Field Object

A field is a column of data containing entries with the same data type. In the ADODB library, the Fields collection used to belong exclusively to recordsets, and its members are Field objects. Field objects have properties and methods for storing and retrieving data. With Access 2002, the Fields collection also became a member of the Record object. This expands the scope of a field beyond relational database models so that it can also embrace the parent-child model suitable for working with child files as members of parent folders.

Recordsets use a Field object’s Value property to display the contents of a column in the current record. Many of the other Field properties contain metadata about the data in a record. The Name property is a handle by which your applications can reference a field. The DefinedSize property characterizes the maximum size of a field (in characters for Text fields). The ActualSize property is the actual length (in bytes) of the contents of a Field object’s value. The Attributes property contains an array of information features about a field. It can indicate whether a field’s value is updateable or whether it can contain Nulls.


NOTE:
The DefinedSize and ActualSize properties use different measurements for Text fields. DefinedSize is the maximum number of characters in the field, and ActualSize is the number of bytes in the field. Since aText field with Jet 4 represents characters with 2 bytes each, its ActualSize value can be up to twice the DefinedSize value. For numeric fields, and Text fields in databases that represent characters using a single byte (for example, a Jet 3.51 database), this difference does not exist. If you are migrating from Access 97 to Access 2002, you are probably using Jet 3.51. Therefore, you should be especially sensitive to this distinction.

The GetChunk and AppendChunk methods of the Field object facilitate the processing of large text or binary data fields in smaller chunks that fit into memory more conveniently. You use the GetChunk method to bring a portion of a large field into memory. The Size argument specifies the number of bytes to retrieve in one invocation of the GetChunk method. Each uninterrupted, successive invocation of the method starts reading new data from where the previous one finished. The AppendChunk method lets you construct a large text or binary data field in chunks from memory. Like the GetChunk method, AppendChunk writes new data into a field from where the previous AppendChunk method finished. To use either method correctly, a Field object’s adFldLong bit in the Attributes property must be set to True.

Name and Value Properties

The following pair of procedures demonstrates an application for the Name and Value properties of the Field object. The application enumerates the field names and values in any row of any table in any database. The first procedure, CallFieldNameValue, passes information that points at a row in a table of a database. The called procedure, FieldNameValue, constructs a single-record recordset based on the passed arguments. It then enumerates the field names and values for the row in the recordset.

Two alternate sets of passed arguments appear in first procedure. The set without comment markers is for a string criterion, such as the CustomerID field in the Customers table. The set with comments is for a numeric criterion, such as the ShipperID field in the Shippers table. Both sets rely on the Northwind database, but you can freely change all these arguments to specify the field names and values in any particular row of any table within any database. The value for str3 should be the name of a field that has a unique value for each row in the table, such as a primary key. The var1 variable should contain a string value or a number value for the field denoted by str3.

Sub CallFieldNameValue()
Dim str1 As String
Dim str2 As String
Dim str3 As String
Dim var1 As Variant
‘Specify data source for field name
‘and value data
str1 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PMA Samples\Northwind.mdb;"

‘Denote a specific record source
‘within the data source
str2 = "Customers"
‘str2 = "Shippers"
‘Designate a criterion field (str3) and a criterion
‘value (str4) for picking a particular row from
‘the record source
str3 = "CustomerID"
var1 = "BONAP"
‘str3 = "ShipperID"
‘var1 = 2
‘Call the procedure to enumerate field names
‘and values
FieldNameValue str1, str2, str3, var1
End Sub
Sub FieldNameValue(str1 As String, _
str2 As String, str3 As String, _
var1 As Variant)
Dim cnn1 As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim str5 As String
Dim fld1 As ADODB.Field
‘Open connection and recordset
Set cnn1 = New ADODB.Connection
cnn1.Open str1
Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = cnn1
If IsNumeric(var1) Then
str5 = "SELECT * FROM " & str2 & _
" WHERE " & str3 & "=" & var1
Else
str5 = "SELECT * FROM " & str2 & _
" WHERE " & str3 & "=‘" & var1 & "‘"
End If
rst1.Open str5, , , , adCmdText
‘Report field names and values for record
For Each fld1 In rst1.Fields
Debug.Print fld1.Name, fld1.Value
Next fld1
End Sub

The second procedure uses an SQL string to designate the source for the single-row recordset. It selects all the rows from any table where the field value in str3 equals var1. After constructing the recordset, a Do loop passes through each row. On each iteration, the loop prints the Name and Value properties for one field.

The Type Property

A Field object’s Type property indicates the kind of data it can contain. This property returns one of the data type constants in the DataTypeEnum values range. You can view these options in the Object Browser for the ADODB library. Figure 2-8 shows these constants in the Object Browser screen. By selecting the type for a field, you can determine legitimate values for its Value property.

Figure 2-8 The Object Browser showing a selection of data type constants for defining fields. (Image Unavailable)

Printing Field Data Types

The following two procedures work together to process data type constants with ADO. The FieldNameType procedure opens a recordset based on the Orders table in the Northwind database. This table has a reasonable variety of data types, so it makes a nice case study for examining data types. After opening a recordset, the procedure loops through the fields in the recordset and prints each Field object’s name and type. The FieldType function translates the numeric constant’s value to a string that represents the constant’s name. The adCurrency constant has a value of 6, for example, as shown in Figure 2-8. The FieldType function decodes the value 6 to the string "adCurrency". The FieldNameType procedure then prints each field’s name and data type constant name.

Sub FieldNameType()
Dim cnn1 As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim fld1 As ADODB.Field
Dim str1 As String

‘Open connection and recordset
str1 = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\PMA Samples\Northwind.mdb;"
Set cnn1 = New ADODB.Connection
cnn1.Open str1
Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = cnn1
rst1.Open "orders", , , , adCmdTable

‘Report field names and types for record
For Each fld1 In rst1.Fields
Debug.Print " Name: " & fld1.Name & vbCr & _
" Type: " & FieldType(fld1.Type) & vbCr
Next fld1
End Sub
Public Function FieldType(intType As Integer) As String
Select Case intType
Case adVarWChar
FieldType = "adVarWChar"
Case adCurrency
FieldType = "adCurrency"
Case adInteger
FieldType = "adInteger"
Case adDate
FieldType = "adDate"
End Select
End Function

You can easily run FieldNameType and FieldType against recordsets based on other data sources than the Orders table, but the FieldType function checks only for the four data types in the Orders table. When you use a record source other than the Orders table, you might encounter a data type other than the four in the list. In this case, the Type field in the report will be blank. You can fix this problem by determining the value of the field. You can do this by putting a breakpoint on the Debug.Print statement inside the Do loop in the FieldNameType procedure. You examine the value of fld1.Type for a field whose type doesn’t display and then match that constant value against the constant names in the Object Browser for DataTypeEnum. (See Figure 2-8.) Finally, you amend the Select Case statement in the FieldType procedure to decode the new constant.


NOTE:
Place your cursor over an expression or memory variable to determine its value at a breakpoint. You can also track the values of expressions and memory variables in the Watch window. See Chapter 1 for a discussion of how to use the Watch window.

Creating Fields for a Recordset

Using the Fields collection Append method, you can create fields for a recordset. In addition, your applications can assign values to those fields. Because it is possible to persist recordsets, you can save recordsets that your applications create on the fly to hold data originally stored as a disconnected recordset. You can leave the recordset on the workstation used to create it or transfer it later to a network-based database for sharing by many users.

The following procedure, CreateRecordset, demonstrates the key elements of this approach. First, the procedure instantiates an empty recordset. The procedure uses the Append method to populate the empty recordset with fields. As you can see, the procedure assigns the minimum number of Append arguments. These include the field name and data type. When you specify a string data type, such as adVarWChar, you must also designate its maximum length—ADO terminology calls this the DefinedSize argument. Use the third positional argument to specify a field’s DefinedSize. After defining the schema for the recordset, the procedure moves on to populating its two fields with two rows of data. Then it echoes the data to the Immediate window to confirm the data entry operation. Finally, the procedure persists the recordset to a file using the proprietary Microsoft format. So long as the data is exclusively for reading by the recordset’s Open method, there is no compelling factor that establishes a preference for either of the two formats available with the Save method.

Sub CreateRecordset()
On Error GoTo CreateRecordset_Trap
Dim rst1 As ADODB.Recordset
Dim strPath As String
Dim strFileName As String
Dim str1 As String
‘Instantiate a recordset
Set rst1 = New ADODB.Recordset
‘Specify recordset field name and data type.
‘Append to recordset object.
rst1.Fields.Append "LastName", adVarWChar, 10
rst1.Fields.Append "ContactID", adInteger
‘Add rows to the recordset
rst1.Open
With rst1
rst1.AddNew
rst1("LastName") = "Dobson"
rst1("ContactID") = 9
rst1.Update
rst1.AddNew
rst1("LastName") = "Edelstein"
rst1("ContactID") = 10
rst1.Update
End With
‘Echo new contact data to Immediate window
rst1.MoveFirst
Do While Not rst1.EOF
Debug.Print rst1("LastName") & _
" " & rst1("ContactID")
rst1.MoveNext
Loop
‘Specify path and file to hold persisted recordset,
‘and save recordset to it
strPath = "C:\PMA Samples\Chapter 02\"
strFileName = "NewContactData.adtg"
str1 = strPath + strFileName
rst1.Save str1, adPersistXML
CreateRecordset_Exit:
rst1.Close
Set rst1 = Nothing
Exit Sub
CreateRecordset_Trap:
‘Kill previous version of file to hold
‘persisted recordset if it exists already
‘Otherwise, present error info in a message box.
If err.Number = 58 Then
Kill str1
Resume
Else
MsgBox "Procedure failed with an error number = " _
& err.Number & ", " & vbCrLf & "and an " & _
"error description of """ & _
err.Description & """" & ".", vbInformation, _
"Programming Microsoft Access Version 2002"
Resume CreateRecordset_Exit
End If
End Su
b
Read More Show Less

Customer Reviews

Be the first to write a review
( 0 )
Rating Distribution

5 Star

(0)

4 Star

(0)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously

    If you find inappropriate content, please report it to Barnes & Noble
    Why is this product inappropriate?
    Comments (optional)