Microsoft Access 2000: Data Access Models
This content is no longer actively maintained. It is provided as is, for anyone who may still be using these technologies, with no warranties or claims of accuracy with regard to the most recent product version or service release.
Rick Dobson
CAB, Inc.
Applies to: Microsoft Access 2000
July 1999
Excerpted from Programming Microsoft® Access 2000. Copyright © 1999 by Rick Dobson. Reprinted with permission of Microsoft Press.
Rick Dobson, Ph.D., is Chief Technology Officer for CAB, Inc. CAB offers Office, Web, and database services as well as support for Office-based automated accounting packages. Rick is also the author of Programming Microsoft Access 2000 from Microsoft Press. CAB maintains two sites. One targets Access/Office 2000 development (www.programmingmsaccess.com), and the other (www.cabinc.net/) focuses on Access/Office 97 and Dynamic HTML. Both sites offer samples, presentations, either full-length articles or tutorials, and prizes.
Summary: This chapter reviews the DAO and ADO data access models, with the primary emphasis on ADO as a programming model. It focuses primarily on the ADO object models for Jet in Access and the ADODB and ADOX libraries. Extensive programming examples show you how to accomplish typical database tasks. (74 printed pages)
Contents
Introduction DAO Overview Jet Workspaces ODBCDirect Workspaces Objects Common to Jet and ODBCDirect Workspaces Jet Workspace Objects ODBCDirect Workspace Objects ADO Overview The ADODB Library The ADOX Library
Introduction
Microsoft® Access 2000 supports two data access models: the traditional Data Access Objects (DAO) and ActiveX® Data Objects (ADO). DAO targets the Microsoft Jet database engine to enable quick and easy database programming. Access 2000 is the first version of Access to also support ADO for manipulating Jet databases. Instead of being based on a single database engine, ADO uses a common programming model to deliver access to universal data. 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 use more and different kinds of data sources
This chapter reviews the DAO and ADO data access models, with the primary emphasis on ADO as a programming model. The brief DAO coverage introduces core development concepts and provides a historical perspective on data access within Access. Since DAO will not play a critical role in any subsequent chapters, this chapter will cover using Jet and remote databases.
This chapter will focus primarily on the ADO object models for Jet in Access and the ADODB and ADOX libraries. Extensive programming examples will show you how to accomplish typical database tasks. Other chapters in this book will build on the information in this chapter and will cover additional ADO topics, such as database replication, remote database access, and multiuser security.
DAO Overview
Access 2000 includes the 3.6 version of the DAO library, a maintenance upgrade to the 3.5 version that shipped with Access 97. (The basic architecture and functionality is very similar between versions.) DAO relies on a workspace object model for types of data access. The workspace object can contain session, security, and transaction information. (A workspace object defines how your application interacts with data.)
There are two types of workspaces: Microsoft Jet workspaces and ODBCDirect workspaces.
Jet Workspaces
Jet workspaces are for Jet, Jet-connected ODBC, and installable ISAM data sources. Jet-connected ODBC data sources let you link to remote data sources in a familiar DAO environment. Unfortunately, this type of connection requires the full DAO model, and it loads Jet even when no data access is required. Installable ISAM data sources come in a variety of formats, such as Paradox and Lotus 123.
Traditional Jet workspaces offer a set of advantages, which include the following:
- Updating data in recordset objects.
- Joining tables from different data sources into a common recordset.
- Creating tables based on familiar DAO methods instead of SQL Data Definition Language (DDL) conventions.
- Binding data to forms and reports.
Jet workspaces include Groups and Users collection objects, while ODBCDirect workspaces do not because remote database sources such as Microsoft SQL Server™ can manage their own security.
ODBCDirect Workspaces
ODBCDirect is a relatively new DAO technology that was introduced with Microsoft Office 97 and DAO version 3.5. Because the security-related DAO objects are not used with remote ODBC data sources, and because other DAO objects work best with locally connected data sources, Microsoft created the ODBCDirect object model, which is available from a separate type of workspace. The ODBCDirect workspace offers fast, direct access to remote ODBC data sources (such as SQL Server) and can bypass Jet. You still get the richness of an object model without having to rely exclusively on SQL commands, as with SQL passthrough queries.
Some of the chief benefits of ODBCDirect workspaces are as follows:
- Use of remote data sources without loading the Jet engine.
- Asynchronous queries.
- Better access to remote database functionality, including cursors and stored procedures.
- Batch updating of remote sources from a local cache.
- Returning multiple result sets from a single query.
ODBCDirect workspaces have a richer cursor library than Jet workspaces, and they support dynamic as well as batch update cursors, which are not available in Jet workspaces. The dynamic cursor lets a session view changes made to a database by other users without having to requery the data source. The batch update cursor permits asynchronous updates to a remote data source. This improves performance by removing the need for holding locks on records.
**Note **Cursors define the type and location of access to a data source. Several data source properties can go into defining a cursor. For example, cursors can be updatable or not. They can permit forward-only movement or bidirectional navigation. Cursors can automatically update to reflect edits and other database modifications or they can require an explicit refresh operation to show the most recent version of a database. You can designate a cursor that works on a remote database server or on a local workstation.
You can enjoy the best of both workspaces by having multiple workspaces of both types open at the same time. By using concurrent, multiple workspaces, you can benefit from the simplicity of bound forms with data that originates in an ODBCDirect workspace. You simply use the returning records in a Jet workspace.
Objects Common to Jet and ODBCDirect Workspaces
Within both types of workspaces, DAO objects generally follow a hierarchical organization. Figure 2-1 shows the hierarchy of DAO collections and objects in Jet workspaces, and Figure 2-2 shows the hierarchy of DAO collections and objects in ODBCDirect workspaces.
Figure 2-1. DAO collections and objects for Jet workspaces
Figure 2-2. DAO collections and objects for ODBCDirect workspaces
The DBEngine object
The top-level DAO object for both workspaces is DBEngine. You use its CreateWorkspace method to open a session. An optional Type argument for the method lets you designate either a Jet workspace or an ODBCDirect workspace. You can also set the DefaultType property of the DBEngine object so that either type of workspace opens in the absence of a specific CreateWorkspaceType setting. The Jet workspace type is the native default. Any setting for the CreateWorkspaceType argument overrides either a native or an explicitly set DefaultType property.
The DBEngine properties and methods available to both workspace types provide a core set of DAO functions. You can use DBEngine to both create and manage databases.
The CreateDatabase method creates new workspaces, and you can use arguments for this method to set a database's sort order and encryption status. You can also set the version format for a database to programmatically create databases compatible with prior versions of Access. The OpenDatabase method opens an existing database in a workspace object. After you create a database, you can apply the Compact and Repair methods to manage the database.
DAO enables transaction processing through DBEngine with three methods: BeginTrans, CommitTrans, and Rollback. A transaction is a set of operations that occur in all-or-none fashion. If any link in a chain of database operations fails, you can roll back all the operations. For example, if a bank transfers money from a checking to a savings account, both the debit to checking and the credit to savings must be successful in order for the books to balance. If either part fails, the bank's code should roll back both parts. Transactions can also speed up database processing by batching sets of disk writes. You can nest transactions up to five levels deep.
Some of the properties and methods available depend on the type of workspace used. However, no matter which workspace you use, DBEngine contains the Errors and Workspaces collections.
The Errors collection
You use the Errors collection to process data access errors. The Errors collection contains one or more error numbers and descriptions from the last failed statement. Multiple errors from a single failure are common with ODBC data sources because different ODBC layers can each report an error for the same failure, such as a remote database source that is not operational.
The Errors collection, like other DAO collections, is zero-based. Its Count property denotes the number of errors in a collection, and the individual errors have item numbers of 0 through Count –1. The last entry in the Errors collection corresponds to the Microsoft Visual Basic® for Applications (VBA) Err object. As you debug your applications, you might find it helpful to enumerate the Errors collection to simplify your search for the source of an error or the solution to an error.
The Workspaces collection
You use the properties and methods of the Workspaces collection to reference individual workspace sessions. Because the Workspaces collection is always available, Access can maintain multiple sessions concurrently and your applications can open and manage any combination of Jet and ODBCDirect workspaces. While these sessions do not persist beyond a logon session, they can exist for a whole session. You can use the CreateWorkspace method's Name argument to uniquely reference individual workspaces within a collection by using one of these formats:
DBEngine.Workspaces (0)
DBEngine.Workspaces("Name")
DBEngine.Workspaces![Name]
All DAO objects have a referencing syntax similar to these examples. The first two styles listed above match ADO conventions. You should adopt one of these whenever possible to enhance your migration skills when you start coding in ADO.
Workspace objects share several important methods with the DBEngine object, such as CreateDatabase, OpenDatabase, and BeginTrans. Other methods, such as CreateUser and CreateGroup, are unique to the Workspace object. These two methods help manage user-level security within a workspace.
The Databases collection
Within any workspace, you can programmatically open multiple databases. Using DAO with VBA offers a distinct advantage over the user interface that enables the opening of a single database at a time. You use variable names to reference database objects to speed access. This same approach enhances speed for many DAO objects.
The Name property of an individual database in a Jet workspace is the path to the database file. You reference the database for the current project in your VBA code with the CurrentDb function. Access also supports the alternative syntax of DBEngine (0) (0) to reference the current database. The CurrentDb syntax creates another instance of the current project's database, but the DBEngine syntax refers to the open copy of the current database. You can open database objects on non-Jet data sources, such as ISAM databases (for example, dBASE or Microsoft FoxPro®). While it is also possible to use an ODBC data source in this way, you generally get better performance by using an ODBCDirect workspace (as explained in the "ODBCDirect Workspace Objects" section).
The Recordsets collection
A Recordset object represents the records in a table or those resulting from a row-returning query. You create new recordsets by using the OpenRecordset method. You can invoke this method from several objects, including both databases and TableDef objects, to create a recordset. Other objects that have an OpenRecordset method include QueryDef objects and even other recordsets. This method adds a new recordset to the Recordsets collection. You can create five types of recordsets, as shown in the following table.
Recordset Objects
| Type | Description |
|---|---|
| Table | This type refers to a record in a table, such as one you create by using the CreateTableDef method. It always refers to a single Jet table. You can update the field values as well as add and delete records. There is no corresponding ODBC cursor. |
| Dynaset | This type is a dynamic collection of records that can result from one or more tables. Selected fields might be updatable, so you can add, delete, and modify records. You use a field's DataUpdatable property to determine whether a field is updatable. In a multiuser database, you can view selected changes made by other users. This recordset type corresponds to the ODBC keyset cursor. |
| Snapshot | With this type, you can examine records based on one or more tables, but you cannot change the underlying records. Once you load a snapshot into memory, it does not reflect any further modifications to the underlying tables. This recordset type corresponds to the ODBC static cursor. |
| Forward-only | This type is identical to the snapshot-type recordset, except that you can only scroll forward. This type corresponds to an ODBC forward-only cursor. |
| Dynamic | This type represents a query result based on one or more underlying tables. Users can update the recordset by adding, deleting, and modifying records. This type also shows changes made by other users in a multiuser environment. It is available only in ODBCDirect workspaces and corresponds to the ODBC dynamic cursor. |
The OpenRecordset method
The OpenRecordset method requires a source argument that specifies the source of the resulting recordset. As mentioned, this is typically a table name, a query name, or an SQL statement. You can also specify the recordset type; if you don't, DAO returns a table, dynaset, or forward-only type, depending on the source.
An options argument lets you specify any of several recordset characteristics, such as prohibiting users from reading or writing to the recordset. A final argument lets you set the LockEdits property for a recordset. This property indicates the type of locking that is in effect when an application revises, adds, or deletes records from a recordset. With Jet workspaces, your settings for this final argument are likely to be read-only, pessimistic locks, and optimistic locks. With a read-only setting, no recordset revisions are possible. With pessimistic locks, invoking the Edit method locks the page containing the record. With optimistic locks, other users can revise the record until your application invokes the Update method. This can yield a faster-performing recordset, but it can also lead to conflicting updates.
Recordset methods
You can modify the records in a recordset by using the Edit, AddNew, Update, and Delete methods. You use the Edit and Update methods together to revise the values in a recordset. The Edit method opens a record for editing, and the Update method commits the new values to the recordset's underlying tables. The AddNew and Update methods operate as a team. You signal that your code will be adding a record by invoking the AddNew method. You save the new record with the Update method. The Delete method removes the current record from a recordset. After you delete a record, that record remains current until you navigate to a new record. The Delete method does not require the Update method.
You can use a set of Move methods to navigate through a recordset once you add it to the Recordsets collection. The MoveNext and MovePrevious methods navigate forward and backward one record. If you are already at the first record and your application invokes the MovePrevious method, DAO returns a "beginning of file" (BOF) marker from the recordset. You can use this marker to flag a movement beyond the first record. Similarly, DAO returns an "end of file" (EOF) marker when an application invokes the MoveNext method from the last record. Any attempt to move beyond either the BOF or the EOF marker generates a run-time error. The Move method lets you specify a fixed number of rows to move. You can also designate a starting position other than the current record. The MoveFirst and MoveLast methods move directly to the first and last record in a recordset, respectively. With very large recordsets, there might be a significant pause until you reach the last record in a recordset.
Another set of methods navigates to a new record that meets specified criteria. These methods are FindFirst, FindLast, FindNext, and FindPrevious. You designate the criteria for a Find method with the same syntax as the WHERE****clause in an SQL statement. If no records meet the specified criteria, these methods set the recordset's NoMatch property to True. Otherwise, they simply move to the record that meets the criteria. Both FindNext and FindPrevious move from the current record. FindFirst and FindLast search from the first or last records, respectively. When you work with TableDef objects, the Seek method can yield faster results than the Find methods. In general, you get better search performance by using SQL statement searches in OpenRecordset methods. With Find, Move, and Seek methods, it is often desirable to set a recordset's Index property so that you can put the records in the precise order designated by the index fields.
Jet Workspace Objects
Database objects in Jet workspaces hold and can activate selected elements of a database schema. For example, you can open recordsets for manipulation or you can run action queries that update, append, or delete records. Database methods also let you create and manage replicas. The five hierarchical collections for a database are listed below. The Database object has methods for adding new elements to all of these collections:
- TableDefs
- Recordsets
- QueryDefs
- Relations
- Containers
The TableDefs collection
The TableDefs collection accesses the individual TableDef objects within a database. TableDef objects contain Fields and Indexes collections, so you can define a table by using a TableDef object. You use the CreateField and CreateIndex methods to compose the definition of a table. When you use the CreateField method, you first specify your field by designating its name, type, and size. Then you invoke the Append method to add your new field to the Fields collection for a TableDef object. If a collection already contains a field with the name you designate as an argument, your code generates a trappable run-time error. You can use this to manage your TableDef object by, for example, removing the old field with the Delete method.
When you create indexes, you invoke the CreateIndex method and append one or more fields to the index. Then you append the new index to the Indexes collection for the TableDef object. If an index already exists with the name you specify, a run-time error occurs. You can use these errors to manage the process of indexing a TableDef object.
The TableDef object can also manage links to tables in ISAM and ODBC data sources. Your code requires the Connect and SourceTableName properties and the CreateTableDef methods for this activity. You invoke the CreateTableDef method to set a reference in a variable to the linked table. Then you set the Connect and SourceTableName properties for the variable. The Connect property specifies the data source type, such as dBase 5.0 or Paradox 5.x, and the path to the specific data source that you want to link. The SourceTableName property is the table name that you link. After setting these properties for the variable referencing the linked table, you complete the process by appending the TableDef object to the TableDefs collection.
The QueryDefs collection
The QueryDefs collection stores the individual QueryDef objects in a database. A QueryDef object is an SQL statement that typically returns a row set or performs an action, such as updating, adding, or deleting records in a recordset. When the SQL statement for a QueryDef object returns rows, it can have a Fields collection of individual fields. If your SQL statement accepts arguments that specify its criteria at run time, a QueryDef object can have a Parameters collection. When the QueryDef object runs, you can specify these parameters programmatically or at run time by means of a dialog box.
You can create new QueryDef objects by using the CreateQueryDef method for either the Database object in the Jet workspace or the Database or Connection object in an ODBCDirect workspace. If you name the QueryDef object as a non-zero-length string, DAO automatically enters it in the QueryDefs collection and saves the QueryDef object to disk for permanent storage along with the database. Use the Delete method to remove an item from the QueryDefs collection. Any QueryDef object created with a Name property equal to a zero-length string is temporary. DAO does not save these. Temporary QueryDef objects are convenient when your applications need to create QueryDef objects dynamically.
Two methods allow you to activate a QueryDef object: The OpenRecordset method returns the rows in a QueryDef object with a SELECT****statement, while the Execute method runs an action query. The dbFailOnError option can allow your application to determine whether a QueryDef object fails to perform its designated action for all records that meet its criteria. As long as a QueryDef object is syntactically correct, it does not generate an error—even if it fails to perform its action. The dbFailOnError option rolls back any changes if the QueryDef object cannot perform all the changes. This option also generates a run-time error to help you perform any associated processing, such as providing feedback to a user.
The Relations collection
You use the Relations collection and its individual relations to define links between tables programmatically. The CreateRelation method for the Database object can initially specify relations; it lets an application define one-to-one or one-to-many relations between any pair of tables. This method can also designate referential integrity as well as cascading deletes and updates. (See Chapter 4 for an in-depth discussion of these terms.) You define relations between tables based on common fields in both tables. The Relation object has a Fields collection to support this function. The Relations collection and individual Relations objects are unique to the Jet workspaces. They are unavailable in ODBCDirect workspaces because remote database engines typically maintain their own relations between tables.
The Containers collection
The Containers collection defines a set of container objects for database documents. Some of the container objects follow from the Database window: Forms, Reports, Scripts for Macros, and Modules. These are all Access objects, not Jet database objects. Other container objects are Jet-based, including Databases, Tables, and Relationships. The Tables container object includes information about both tables and queries. There is an additional container object for saved relationship layout information.
Container objects hold documents, which consist of all saved elements of a type, such as forms or relationships. These documents provide administrative, not content, information about the objects in a container object. Selected properties for documents include the date created and the date last updated as well as the owner, user, and permissions. Jet uses the information in documents to manage security for Access objects as well as its own native tables and queries.
It is important to understand that the documents in container objects are different from the elements in a collection. Documents consist of all saved objects—whether or not they are open. Collections are groups of objects that are open. If an object is not open, it is not part of a collection. However, it can belong to a container object. Also, documents hold administrative information about objects, but elements in a collection have information about the content, layout, and sub-elements of the objects of a collection.
The Users and Groups collections each have their matching objects. These collections and objects complement the container documents to help Jet manage user-level security. Documents have permissions. Users belong to groups. Document permissions describe levels of access by users and groups. Chapter 10 includes detailed coverage of user-level security, including users, groups, and permissions.
ODBCDirect Workspace Objects
By comparing Figure 2-2 with Figure 2-1, you can see that the ODBCDirect model is much more parsimonious because ODBCDirect workspaces hand over to remote database servers some of the functions that Jet manages. For example, remote database servers manage their own security, so Users and Groups collections aren't needed. There is also no TableDefs collection because remote database servers manage their own tables. The story is similar for relations.
The two workspace models are different in other ways. The ODBCDirect model has a new Connections collection with its corresponding objects. Also, in the ODBCDirect model the role of the Database object is different, and QueryDef objects do not have a Fields collection. You can derive a Recordset object from a QueryDef object via the OpenRecordset method.
Although both workspaces have a Database object, it behaves somewhat differently in the ODBCDirect workspace than it does in the Jet workspace. In the ODBCDirect workspace, that object has a Connect property that returns a reference to a Connection. The Connection object has a Database property that returns a reference to a Database object. In DAO models, Connection and Database objects are different ways of referencing the same thing. These properties simplify migration from Jet workspace models to ODBCDirect workspaces and back again.
The Connections collection
The Connections collection of a workspace and its objects are critical when you work with remote databases. In ODBCDirect workspaces, you use the OpenConnection method to establish a connection to a remote database. You need as many as four arguments, three of which are optional. The one required argument is the name. Naming a connection adds it to the Connections collection. The other three arguments define the nature of the connection. Since these are optional arguments, you can designate them when you create the connection or later. However, you must define a connection before you can use it to extract data or otherwise work with the data in a remote data source. One key remaining argument is the Connect argument that sets the Connect property of the Connection object. You use the Connect argument to specify the connection string. It starts with ODBC and a semicolon followed by other connection information needed to link to the remote data source. This can include a data source name (DSN) and a database name and is also likely to include a user ID and corresponding password. You delimit each type of information with a semicolon. Your application can examine and reset the connection string through the Connect property of a Connection object.
A second optional OpenConnection parameter controls two distinct types of behavior: how a connection reacts to incomplete connection string information, and how to open a connection asynchronously. In the case of incomplete connection string information, you can let the connection fail and generate a run-time error or you can trap the error and prompt for complete information. You can also use this parameter to designate that a connection is to open asynchronously. The application can open a connection and then go on to other tasks. Your Access application can serve the local user by opening forms or even interacting with the user. At the same time, the remote database server processes the request for a new connection. With the Connection object's StillExecuting property, your application can poll the connection to determine when it is available for use.
The Connection object has five methods:
- OpenRecordset. This method returns a set of rows from a remote data source. It has more features in an ODBCDirect workspace than in a Jet workspace. Perhaps the most profound difference is that, when using this method in an ODBCDirect workspace, you can specify more than one SQL statement so that a single OpenRecordset statement can provide multiple recordsets for local use.
- Close. This method closes an open connection.
- CreateQueryDef. This method also has additional features in an ODBCDirect workspace. Perhaps the most obvious is that QueryDef objects do not have fields. If you want to view the rows returned by a row-returning QueryDef object, you invoke the OpenRecordset method for the object. All QueryDef objects in ODBCDirect workspaces are temporary. In the ODBCDirect object model, you cannot create stored procedures in a remote data source. QueryDef objects can belong only to Connection objects. There is no CreateQueryDef method for Database objects in an ODBCDirect workspace, as there is in a Jet workspace. You can open a Recordset object from a Database object in either kind of workspace.
- Execute. This method runs action, parameter, and select queries. You designate the dbRunAsync constant to specify that a QueryDef object should run asynchronously. Just as for the OpenConnection method, users can perform other tasks simultaneously as the QueryDef object runs. The StillExecuting property enables an application to check on the completion status of the QueryDef object.
- Cancel. This method terminates an asynchronous query. It returns a run-time error if you invoke it without specifying asynchronous operation. You free the resources consumed by a QueryDef object by applying the Close method or by setting the QueryDef object reference to Nothing.
Batch updating
One of the more powerful innovations in ODBCDirect workspaces is batch updating, which enables an application to download a set of records, perform updates locally, and then update the original as a single batch instead of one record at a time. Batch updating has obvious concurrency advantages over single-record locking. Because of the potential for conflicts, it is best used when a database is unlikely to be changed by multiple users. However, some features for handling collisions are built into batch processing. For example, a BatchCollisions property returns bookmarks that point at collisions in a recordset after you upload the recordset. You can also force a remote database to match your update or accept the value in the remote data source. Three Field properties let you examine the original value before downloading, the updated value in the local recordset version, and the new field value in the remote data source.
There are five steps to implementing batch updating in an ODBCDirect workspace:
- Set the DefaultCursorDriver property for the workspace to dbUseClientBatchCursor.
- Create a Connection object or a Database object.
- Invoke the OpenRecordset method for the object from step 2 with a dbOptimisticBatch setting for the LockEdits argument.
- Edit the fields locally as needed.
- Invoke the Update method for the recordset from the third step with a dbUpdateBatch setting for the type argument. If you have no collisions, you are done. If there are collisions, you need additional logic to reconcile them.
ADO Overview
Access 2000 supports ADO version 2.1, which includes three ADO data access models: the ADODB library, the ADOX library, and the JRO library. 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 2000 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 directories. This vastly expands the power of database programming.
The ADODB library is a small, lightweight library that contains core objects and offers the basics for making connections, issuing commands, and retrieving recordsets, and it also enables recordset navigation. You can use it to perform basic maintenance tasks, such as modifying, adding, and deleting records. The nonhierarchical design of this library makes it easy for beginners to use.
The ADOX library supports data definition language and security issues. It offers objects that put you in touch with a database's overall schema. For example, it lets you create tables and relations. The model includes support for referential integrity and cascading updates and deletes, and it offers procedures and views as well as Users and Groups collections for user-level database security.
The JRO library enables Jet database replication. Access 2000 supports database replication with both Jet and SQL Server databases. Chapter 11 covers database replication in depth.
One major advantage that ADO offers is an event model. ODBCDirect permits asynchronous operations, but ADO provides events. This frees an application from polling an object and checking its StillExecuting property. Instead, you can simply create event handlers to respond to events whenever they happen. (Subsequent chapters will explain how to design event handlers.)
OLE DB providers help make ADO powerful. They offer a new way to access remote data that embraces and extends ODBC, and they provide access to both relational databases and nontraditional data sources with a consistent ADO interface. Access 2000 ships with a variety of OLE DB providers, including ones for Jet, SQL Server, Oracle, general ODBC data sources, and such nontraditional sources as Microsoft Active Directory Service and Microsoft Index Server. You can expect more of these providers over time.
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 ToolsReferences command. Figure 2-3 shows the References dialog box with all three libraries selected. While it might be more convenient to select all three, you can conserve resources by selecting just the libraries that you need. Experiment with the selections for your applications and the machines that they run on to determine what makes sense for your environment. If you have a production application that runs on many different types of machines, you should conserve resources for other application requirements.
Figure 2-3. You use the References dialog box to add ADO libraries to an application.
The ADODB Library
The ADODB object library has seven main objects. Four of these objects have collections. The Connection object appears at the top of the hierarchy, but you can create connections implicitly by using other objects. The Connection, Command, Recordset, and Field objects have Properties collections.
Figure 2-4. The ADODB object library
The Connection object
The Connection object establishes a link to a database. You always use a Connection object either implicitly or explicitly when you work with a database. When you explicitly create one, you can efficiently manage one or more connections and reassign the roles that they serve in an application. By implicitly creating one 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 that each requires 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 and methods are appropriate for the Jet engine. There is, however, a special OLE DB provider for Jet 4.0, which is the latest version of Jet that ships with Access 2000. Since Connection objects depend critically on provider specifications, the ability to set a Connection parameter that references the Jet 4.0 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 when it is not in the current project.
The following simple code sample opens the familiar Northwind database. Notice that a Dim statement declares and creates a reference to cnnNorthwind as a Connection object. The use of the Open method on cnnNorthwind makes the database available to the rest of the procedure. Notice also that the Provider and Data Source parameters appear within a single pair of double quotes. The Provider parameter points to the Jet 4.0 OLE DB provider and the Data Source parameter points to the physical location of the Northwind database.
Sub OpenMyDB()
Dim cnnNorthwind As New Connection
Dim rsCustomers As Recordset
'Create the connection.
cnnNorthwind.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\Office\" & _
"Samples\Northwind.mdb;"
'Create recordset reference and set its properties.
Set rsCustomers = New ADODB.Recordset
rsCustomers.CursorType = adOpenKeyset
rsCustomers.LockType = adLockOptimistic
'Open recordset and print a test record.
rsCustomers.Open "Customers", cnnNorthwind, , , adCmdTable
Debug.Print rsCustomers.Fields(0).Value, rsCustomers.Fields(1).Value
rsCustomers.Close
cnnNorthwind.Close
End Sub
After creating a reference to the connection, the code creates a Recordset object. It sets a reference to the object variable denoting the recordset, and then it assigns values to a couple of properties for the recordset. The last block of code opens the recordset and prints a couple of fields from the first record. The Open method for a Recordset object can reference a connection to a database and some source of records in the database. The code above selects all of the records from the Customers table in the Northwind database. The Open method initially makes the first record available to an application.
The final two lines in the last block of code close the recordset and then the connection. Closing a connection makes all objects that reference it, such as a Recordset object, inoperable. Any attempt to set properties or invoke methods for a recordset that references a closed connection generates a run-time error. For this reason, implicitly creating a connection might be a better choice because the object has use of the connection for its lifetime.
The following code also opens a recordset based on the Customers table in the Northwind database and prints the first record. However, it uses fewer lines of code and the code is less complicated because it implicitly creates a connection and accepts more default settings.
Sub OpenFast()
Dim rsCustomers As Recordset
Set rsCustomers = New ADODB.Recordset
'Less code, but potentially greater resource consumption.
rsCustomers.Open "customers", "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\Office\" & _
"Samples\Northwind.mdb;"
Debug.Print rsCustomers.Fields(0), rsCustomers.Fields(1)
rsCustomers.Close
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 one or close one). As you can see, the Open method for a recordset object can include the essential connection information of a provider and a data source. The code above has only one other 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 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 seven other settings that grant various degrees of restricted access to a database. These mode settings pertain to all the recordsets and commands that assign a connection to their ActiveConnection property. The following code shows the impact of the read-only mode setting on the ability to update a recordset.
Sub OpenLookOnly()
Dim cnn1 As New Connection
Dim rsCustomers As Recordset
' cnn1.Mode = adModeRead
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\Office\" & _
"Samples\Northwind.mdb;"
Set rsCustomers = New ADODB.Recordset
rsCustomers.Open "Customers", cnn1, adOpenKeyset, _
adLockPessimistic
'An adModeRead setting for cnn1.Mode causes an error in this procedure.
'Remove the comment from the cnn1.Mode line to see an error here.
rsCustomers.Fields("CustomerID") = "xxxxx"
rsCustomers.Update
Debug.Print rsCustomers.Fields("CustomerID")
rsCustomers.Close
End Sub
The OpenLookOnly procedure declares a new Connection object in its first line. The third line, if uncommented, sets the connection's Mode property to adModeRead for read-only access. Two more lines into the procedure, an Open method makes the rsCustomers recordset available. The next pair of lines attempts to update the value of the CustomerID field for the first record. If you remove the comment in the third line, these updates will cause an error because you can't update a read-only database.
The following table describes the eight constants that you can use to set a connection's Mode property. You can use these constants to control the type of editing that one or more users can do through a connection to a database.
Constants Used to Set the Connection Object's Mode Property
| 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). |
The OpenSchema method
The Connection object's OpenSchema method lets an application browse the objects in the collections available through a connection without enumerating the elements in a list. The output from the OpenSchema method can contain information about tables, views, procedures, indexes, and more. The specific details depend on how a given OLE DB provider implements the general capabilities of the method. The following code uses the OpenSchema method with the Jet 4.0 provider to list the views available through a connection.
Public Sub OpenSchemaX()
Dim cnn1 As New ADODB.Connection
Dim rstSchema As ADODB.Recordset
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\Office\" & _
"Samples\Northwind.mdb;"
Set rstSchema = cnn1.OpenSchema(adSchemaTables)
'Print just views; other selection criteria include
'TABLE, ACCESS TABLE, and SYSTEM TABLE.
Do Until rstSchema.EOF
If rstSchema.Fields("TABLE_TYPE") = "VIEW" Then
Debug.Print "View name: " & _
RstSchema.Fields("TABLE_NAME") & vbCr
End If
rstSchema.MoveNext
Loop
rstSchema.Close
cnn1.Close
End Sub
The procedure starts by declaring a connection and a recordset. The recordset holds the output from the OpenSchema method. The argument for the OpenSchema method indicates that elements of the Tables domain for the database schema will make entries in the records. However, the OpenSchema method tracks several types of tables, including views, normal user tables, special system tables, another table of Access objects, and linked tables. The code above prints the output from the method just for views.
The Recordset object
A recordset is a programmatic construct for working with records. You can base your records on a table or a view in the current project or on another file, an SQL statement, or a command that returns rows. What you can do with a recordset depends on its OLE DB provider and on native data source attributes.
While you can extract recordsets by 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 row-set 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 or any subset from a full recordset. Recordsets have historically been nonpersistent objects—they normally exist just for the time that they are open in a program. The 2.10 version of ADO that ships with Access 2000 offers persistent recordsets, which you can save to disk and then open again later.
The ActiveConnection property
A recordset's ActiveConnection property lets your application tap an open connection to support a recordset. You can set this property any time after setting the object for the recordset. Its use simplifies your Open method statement for the recordset by removing the need to include the connection information. When you preset the property, you do not even need to reference an existing connection in the Open method statement.
The Open method
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, or a stored procedure. You use the Open method's Options argument to designate the source type when you open a recordset.
The cursor type
The cursor type is among the most basic features of a recordset. It determines how you can navigate through the recordset and the types of locks that you can impose on it. 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 without relying on bookmarks.
Keyset. This cursor has most of the properties of a dynamic cursor, except you do not have ready access to changes by other users of a data source. One way to view changes made by others is to invoke a recordset's Requery method.
Static. This cursor is a snapshot of a recordset at a point in time. It allows bidirectional navigation. Changes to the database by other users are not visible. Microsoft Internet Explorer 4 and later supports this type as its most flexible client-side cursor.
Forward-only. Sometimes called the fire hydrant cursor, this type goes in one direction 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.
Note
The cursor type setting interacts with lock type settings. If you designate a forward-only cursor type with a lock type other than read-only (adLockReadOnly), ADO overrides your cursor type setting. For example, ADO automatically converts a forward-only cursor type to a keyset cursor type if you designate optimistic locking.
The LockType property
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. The following table describes the four possible settings for the LockType property. The adLockBatchOptimistic setting is specifically for remote databases, such as SQL Server or Oracle, as opposed to a local Jet database. This topic will receive more attention in Chapter 12.
Constants Used to Set the Connection Object's LockType Property
| Constant | Value | Behavior |
|---|---|---|
| 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. |
**Note **You can determine whether the recordset you are using provides a particular type of functionality by using the Supports method. You simply put the constant that represents that functionality in parentheses when you invoke Supports. A return value of True indicates that the recordset provides that functionality. The online Supports documentation describes the names of the constants. Search the Object Browser for CursorOptionEnum to see a list of constants for which Supports returns True or False.
Recordset navigation
Four methods enable recordset navigation by changing the current record position:
- 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 there is no index, on the order of entry. This method functions with all cursor types. Its use with forward-only cursors can force a re-execution 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 the method with a forward-only cursor generates a run-time error.
- MoveNext. This method relocates the current record position one record forward (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 from 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 moves toward the last record and a negative argument to identify moves 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 that can enable 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. Since it is much faster to retrieve records from memory than from a provider's data store, you can speed record navigation with Move by using a larger CacheSize. With a forward-only cursor and a larger CacheSize, you can actually enable backward 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 with the MovePrevious method. (You can use the Move method with a negative argument.)
The Find method
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 earlier versions of Access, the Access 2000 version 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 move off that record to find a new 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 Sort property
A recordset Sort property can affect the results of both the Find and Move 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 rows—they merely determine the order in which a recordset makes its rows available.
The Filtered property
The Filtered property for a recordset defines a new recordset that is a filtered version of the original 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. If you already have a recordset and you need only a subset for another purpose, this property can serve admirably.
The AddNew method
The AddNew method adds new records to 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 by using a Move method or you call the Update method while still on the row. (You can modify the values in a field by 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 calling the Delete method. The deleted record remains current until you move away from it.)
Printing field values
The following simple procedure opens a data source and then successively prints out the rows of the database. A loop passes through all the records and prints the first two fields of each record.
Sub EasyLoop()
Dim rsCustomers As Recordset
Set rsCustomers = New ADODB.Recordset
rsCustomers.Open "customers", & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\Office\" & _
"Samples\Northwind.mdb;"
'Loop through recordset.
Do Until rsCustomers.EOF
Debug.Print rsCustomers.Fields(0), rsCustomers.Fields(1)
rsCustomers.MoveNext
Loop
rsCustomers.Close
End Sub
One weakness of the first EasyLoop procedure is that it prints only the values of the fields you specifically request. The EasyLoop2 procedure below circumvents this difficulty. No matter how many fields are in the data source for a recordset, the procedure automatically prints all of them.
Sub EasyLoop2()
Dim rsCustomers As Recordset
Dim fldMyField As Field
Dim strForRow As String
Set rsCustomers = New ADODB.Recordset
rsCustomers.Open "customers", & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\Office\" & _
"Samples\Northwind.mdb;"
'Loop through recordset and fields with rows.
Do Until rsCustomers.EOF
strForRow = ""
For Each fldMyField In rsCustomers.Fields
strForRow = strForRow & fldMyField & "; "
Next fldMyField
Debug.Print strForRow
rsCustomers.MoveNext
Loop
rsCustomers.Close
End Sub
The first several and last several lines in each procedure are identical. The EasyLoop2 procedure nests a For loop inside a Do loop. This inner For loop enumerates the fields in a row and builds a string with all the field values on each row. (The string is cleared at the top of the loop to start the process over again for another row.)
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 the field values of a recordset. The NoEasyLoop procedure below uses the GetString method to retrieve and print all the fields on all rows of a recordset in one step. The GetString method returns a recordset as a string. It can take up to five arguments; the code uses three of those arguments. You designate the adClipString constant as the first argument—this is your only choice. It specifies the format for representing the recordset as a string. The second argument specifies the number of recordset rows to return. This code returns five rows. Leaving this argument blank enables the method to return all the rows in the recordset. The third argument designates a semicolon delimiter for the columns within a row. The default column delimiter is a tab. The fourth and fifth arguments, neither of which appears below, specify a column delimiter and an expression to represent null values. The default values for these arguments are a carriage return and a zero-length string.
Sub NoEasyLoop()
Dim rsCustomers As Recordset
Set rsCustomers = New ADODB.Recordset
rsCustomers.Open "customers", _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\Office\" & _
"Samples\Northwind.mdb;"
'Print records without a loop.
Debug.Print rsCustomers.GetString(adClipString, 5, "; ")
rsCustomers.Close
End Sub
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.
Adding a record
The following code tackles a new task—adding a new record to a data source.
Sub AddARecord()
Dim rsMyTable As Recordset
'Set your cursor so that it is not readonly to delete.
Set rsMyTable = New ADODB.Recordset
rsMyTable.ActiveConnection = CurrentProject.Connection
rsMyTable.Open "MyTable", , adOpenKeyset, adLockOptimistic, _
adCmdTable
'Invoke the AddNew method.
rsMyTable.AddNew
rsMyTable.Fields("Column1").Value = 16
rsMyTable.Fields("Column2").Value = 17
rsMyTable.Fields("Column3").Value = 18
rsMyTable.Update
End Sub
While EasyLoop, EasyLoop2, and NoEasyLoop all accept the Open method's default cursor type and lock type settings, the AddARecord procedure does not. Recall that the defaults are a forward-only cursor and a read-only lock type. These settings are acceptable for merely printing the contents of a recordset. However, you need a cursor type and a lock type that permit updates to a recordset when your task requires adding, editing, or deleting records. The adOpenKeyset and adLockOptimistic arguments to Open allow you to add new rows to a recordset. Also, notice that the ActiveConnection setting in the code above does not reference the Northwind sample project. It instead references the connection for the current project. When you need to reference a data source in the current project, use this syntax. The connection statement also explicitly designates a table in the current project as the data source for the recordset. There are several alternative sources, including the text for an SQL statement, a stored procedure, an external file saved in a special format, and more.
To use the AddNew method to add a record, you call the method, issue assignment statements to populate the new record with values, and then invoke the Update method. The call to Update is not strictly mandatory; you can simply move off the new, current record. For example, you can invoke MoveFirst or another method to navigate to a new record.
Editing or deleting a record
The following code edits or deletes a record. It does not use the Edit and Update methods to save the edited records. Instead, it moves off the record. If it is impractical to move off the record or if your application needs to commit the changes before moving, use the recordset's Update method instead.
Sub DeleteOrUpdateARecord()
Dim rsMyTable As Recordset
'Use a nonreadonly lock type to be able to delete records.
Set rsMyTable = New ADODB.Recordset
rsMyTable.ActiveConnection = CurrentProject.Connection
rsMyTable.Open "MyTable", , adOpenKeyset, adLockOptimistic, _
adCmdTable
'Loop through recordset.
Do Until rsMyTable.EOF
If rsMyTable.Fields("Column1") = 16 Then
' rsMyTable.Fields("Column1") = 88
rsMyTable.Delete
End If
rsMyTable.MoveNext
Loop
rsMyTable.Close
End Sub
A loop such as the one in the DeleteOrUpdateARecord procedure can help you select records for deleting or editing. The procedure examines each Column1 field value in a recordset, searching for one with a value of 16. When it finds one, it deletes the row. Notice that the loop contains a commented line. To switch from a delete routine to an updating routine, you simply transfer the comment mark from the assignment line to the Delete method line.
Finding records
Another common use for a recordset is to find one or more records that meet specified criteria. Access 2000 offers several approaches to this task. With earlier versions of Access, many developers used one or more variations of the Find method. As mentioned earlier, Access 2000 offers a single Find method that works somewhat differently from the earlier Find methods. If you liked the earlier Find methods, you can use the new Find method with its similar functionality.
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 criterion, the method relocates the current record to that location. The code prints the CustomerID and ContactName fields to confirm exactly which record matches the criterion.
Sub FindAMatch()
Dim rsCustomers As Recordset
Set rsCustomers = New ADODB.Recordset
rsCustomers.ActiveConnection = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\Office\" & _
"Samples\Northwind.mdb;"
rsCustomers.Open "Customers", , adOpenKeyset, adLockPessimistic, _
adCmdTable
rsCustomers.Find ("CustomerID Like 'D*'")
Debug.Print rsCustomers.Fields("CustomerID"), _
rsCustomers.Fields("ContactName")
End Sub
One drawback to this approach is that it searches for a single match to the criterion, and then stops immediately after finding it. The code below discovers all the records that match the criteria statement. This simple application reveals more of the flexibility of the Find method.
Sub FindAMatch2()
Dim rsCustomers As Recordset
Set rsCustomers = New ADODB.Recordset
rsCustomers.ActiveConnection = & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\Office\" & _
"Samples\Northwind.mdb;"
rsCustomers.Open "Customers", , adOpenKeyset, _
adLockPessimistic, adCmdTable
Do
rsCustomers.Find ("CustomerID Like 'D*'")
If rsCustomers.EOF Then
Exit Sub
End If
Debug.Print rsCustomers.Fields("CustomerID")
rsCustomers.MoveNext
Loop
End Sub
The trick to finding all the records that match the search criteria is to embed the Find method in a 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 Sub statement to end the subroutine. As long as Find keeps discovering new matches, the procedure prints the customer IDs 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.
The Find method goes through a recordset sequentially and discloses matches one at a time. It does not create another version of the recordset that contains all the records that match the criteria. When you need a new or alternative recordset containing just the matches, your application needs a different approach. The recordset Filter property might be the answer. 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. By setting the Filter property equal to any of a series of constants, 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 values.
Filtering records
The two following 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 result set, clears the filter, and then prints the result set again. The FilterRecordset procedure relies on the FilterLikeField function to manage the setting of the Filter property based on parameters passed to it by the FilterRecordset procedure.
Sub FilterRecordset()
Dim rsCustomers As Recordset
'Create recordset variable.
Set rsCustomers = New ADODB.Recordset
rsCustomers.ActiveConnection = & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\Office\" & _
"Samples\Northwind.mdb;"
'Open recordset.
rsCustomers.Open "Customers", , , , adCmdTable
'Filter recordset.
Set rsCustomers = _
FilterLikeField(rsCustomers, "CustomerID", "D*")
Debug.Print rsCustomers.GetString
'Restore recordset.
rsCustomers.Filter = adFilterNone
Debug.Print rsCustomers.GetString
rsCustomers.Close
End Sub
Function FilterLikeField(rstTemp As ADODB.Recordset, _
strField As String, strFilter As String) As ADODB.Recordset
'Set a filter on the specified Recordset object and then
'open a new Recordset object.
rstTemp.Filter = strField & " LIKE '" & strFilter & "'"
Set FilterLikeField = rstTemp
End Function
The FilterRecordset procedure starts by creating and opening the rsCustomers 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 rsCustomers and prints it****to confirm the result.
The arguments to FilterLikeField include rsCustomers, a field name on which to filter records, and a filter criterion value, which can include any legitimate expression for the Likeoperator 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 Likeoperator. Other acceptable operators include <, >, <=, >=, <>, and =. You can also include Andand Oroperators in your criteria expressions to combine two or more criteria expressions based on the other legitimate operators.
The Filter property does restrict your criteria expressions to those of the form FieldNameOperatorValue. However, some Filter constants enable special uses of the property. The FilterRecordset procedure uses the adFilterNone property to restore a recordset by removing its filters.
Using SQL to create a recordset
You should know one final thing about recordsets: how to generate recordsets based on SQL statements. SQL statements are often nothing more than "SELECT * FROM TABLENAME", but you can tap the full functionality of SQL to generate recordsets. You can even use complex multi-table SELECTstatements with computed fields that use either inner or outer joins and that constrain or organize return sets with WHERE, GROUP BY, and ORDER BYclauses. One easy way to create a custom recordset based on SQL statements is by using WHERE****clauses. You can selectively extract records from an existing source by using expressions that are more complicated than when you use the Filter property.
The following code uses an Open method with an SQL statement. When you base a recordset on an SQL statement instead of an existing table, you pass your SQL statement and use the optional adCmdTable argument instead of adCmdText. That's all there is to it. You can then use the recordset to construct any simpler recordset based on an individual table. More complicated SQL statements do not alter how you declare or use the recordset with ADO.
Sub SQLRecordset()
Dim rsCustomers As Recordset
'Create recordset variable.
Set rsCustomers = New ADODB.Recordset
rsCustomers.ActiveConnection = & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\Office\" & _
"Samples\Northwind.mdb;"
'Open the recordset.
rsCustomers.Open "SELECT * FROM Customers", , adOpenForward-only, _
adLockReadOnly, adCmdText
Debug.Print rsCustomers.GetString
rsCustomers.Close
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 belongs exclusively to recordsets, and its members are Field objects. Field objects have properties and methods for storing and retrieving data.
Recordsets use a Field object's Value property to display the contents of a column in the current record. When you change the record, this value can change to reflect the contents of the new record. Many of the other Field properties contain metadata—data 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 updatable 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 Text fields with Jet 4.0 represent characters with 2 bytes each, their ActualSize value can be as much as twice the DefinedSize value. For numeric fields, and Text fields in databases that represent characters with 1 byte (for example, a Jet 3.51 database), this difference does not exist.
The Field methods GetChunk and AppendChunk facilitate processing of large text or binary data fields in smaller chunks that more conveniently fit into memory. You use the GetChunk method to bring into memory a portion of a large field. 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, it 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.
The Name and Value properties
The following procedure shows common uses for the Name and Value properties. It lists all the field names with their corresponding values. This procedure creates its single-record recordset based on an SQL statement.
Sub FieldNameValue()
Dim cnn1 As ADODB.Connection
Dim rsCustomers As ADODB.Recordset
Dim fldLoop As ADODB.Field
'Open connection and recordset.
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office" & _
"\Office\Samples\Northwind.mdb;"
Set cnn1 = New ADODB.Connection
cnn1.Open strCnn
Set rsCustomers = New ADODB.Recordset
rsCustomers.ActiveConnection = cnn1
rsCustomers.Open "SELECT * FROM Customers " & _
"WHERE CustomerID='BONAP'", , , , adCmdText
'Report field names and values for record.
For Each fldLoop In rsCustomers.Fields
Debug.Print fldLoop.Name, fldLoop.Value
Next fldLoop
End Sub
The procedure begins by opening a connection and then creating a recordset on the connection. The SQL statement extracts the record for the customer with a CustomerID field equal to BONAP. The Do loop that follows the creation of the recordset loops through the recordset's fields. Printing the Name property along with the Value property helps readability.
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-5 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-5. The Object Browser showing a selection of data type constants
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. The adCurrency constant has a value of 6. 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 rsOrders As ADODB.Recordset
Dim fldLoop As ADODB.Field
'Open connection and recordset.
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office" & _
"\Office\Samples\Northwind.mdb;"
Set cnn1 = New ADODB.Connection
cnn1.Open strCnn
Set rsOrders = New ADODB.Recordset
rsOrders.ActiveConnection = cnn1
rsOrders.Open "orders", , , , adCmdTable
'Report field names and types for record.
For Each fldLoop In rsOrders.Fields
Debug.Print " Name: " & fldLoop.Name & vbCr & _
" Type: " & FieldType(fldLoop.Type) & vbCr
Next fldLoop
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
Figure 2-6 shows an excerpt from the output from FieldNameType. This excerpt includes at least one field from each of the data types decoded in the FieldType function. You can easily run FieldNameType and FieldType against recordsets based on other data sources than the Orders table. You might encounter another data type besides 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 do this by putting a breakpoint on the Debug.Print statement inside the Do loop in the FieldNameType procedure. You examine the value of fldloop.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-5.) Finally, you amend the Select Case statement in the FieldType procedure to decode the new constant.
Figure 2-6. An excerpt from the output for the FieldNameType procedure
Finding the longest field entry
The FieldSizes procedure below applies the ActualSize property to find the longest entry in the CompanyName field of the Shippers table in the Northwind database. The procedure begins by creating a connection to the Northwind database, and then it opens a recordset based on the Shippers table. The second portion of the routine finds the longest shipper's name and displays a message box showing the number of characters in the name and the name itself.
Sub FieldSizes()
Dim cnn1 As ADODB.Connection
Dim rsShippers As ADODB.Recordset
Dim fldLoop As ADODB.Field
Dim intMaxChars As Integer, strMsg As String
Dim strName As String
'Open connection and recordset.
strCnn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office" & _
"\Office\Samples\Northwind.mdb;"
Set cnn1 = New ADODB.Connection
cnn1.Open strCnn
Set rsShippers = New ADODB.Recordset
rsShippers.ActiveConnection = cnn1
rsShippers.Open "SELECT * FROM Shippers" _
, , , , adCmdText
'Find longest shipper's name.
intMaxChars = 0
Do Until rsShippers.EOF
If rsShippers!CompanyName.ActualSize / 2 _
> intMaxChars Then
intMaxChars _
= rsShippers!CompanyName.ActualSize / 2
strName = rsShippers.Fields("CompanyName")
End If
rsShippers.MoveNext
Loop
strMsg = "The longest shipper's name is '" & _
strName & "' (" & intMaxChars & " characters)."
MsgBox strMsg, vbInformation, "Programming Microsoft Access 2000"
rsShippers.Close
End Sub
The intMaxChars variable tracks the longest field length. The second portion of FieldSizes initializes the variable to 0 before opening a loop that goes through all of the records in the recordset. It is not strictly necessary to set intMaxChars to 0 because VBA does that automatically. However, doing so helps to make the procedure self-documenting. Any shipper's name containing more characters than the current value of intMaxChars is the longest name up to that point. When the procedure finds such a name, it updates intMaxChars and saves the name. Notice that the procedure uses two different syntax conventions for referencing a field. Several statements use an exclamation point (!)—also called a "bang character"—which is an older Access notation. In this notation, the bang character separates the recordset name and the name of the field. The newer and more common way is to reference the field by using the Fields collection. You can use the numerical index if you know it, or you can include the field name in quotes.
Command and Parameter objects
Within the ADODB library, Command objects deliver three major benefits:
- They can perform a select query to return a set of rows from a data source.
- They execute a parameter query so that you can input run-time search criteria.
- They support action queries against a data source to perform such operations as the updating, deleting, and adding of records.
The Command object can serve additional roles with other libraries, as discussed in later sections.
You must designate a Connection object on which to run a command. You can either implicitly create a Connection object when you specify a command or explicitly assign an existing Connection object to a command. These are the same options as for recordsets.
The CommandTimeout property determines how long ADO waits for the execution of a command to conclude. This property takes a Long****value that specifies the maximum wait time in seconds. Its default value is 30. If the timeout interval elapses before the Command object completes execution, ADO cancels the command and returns an error. The Connection object also supports a CommandTimeout property. It has the same name, but it is independent of the Command object's CommandTimeout property. The Command object's CommandTimeout property does not inherit the setting of the Connection object's CommandTimeout property.
The CommandType property
There are actually several different types of Command objects. The CommandType property sets the type of Command object. You can base your command on an SQL statement, a table, or a stored procedure, as shown in the following table. One main reason for resorting to a CommandType property setting is to enable the creation of a Command object based on an SQL statement. Changing the CommandType constant from its default setting can speed up the operation of a command. Therefore, if you know the source, you should set this constant.
The CommandText property
To write an SQL statement for the command to execute, you use the Command object's CommandText setting. You can also set this property to the name of a stored procedure. When you run an SQL statement, you can use the Prepared property to indicate that the statement is to be compiled and stored on the database server. This slows the first execution of the command but speeds up subsequent executions. You assign True to the Prepared property to invoke compilation of an SQL statement.
CommandType Constants
| Constant | Value | Behavior |
|---|---|---|
| adCmdText | 1 | Lets you run a command based on an SQL statement, a stored procedure, or even a table. Usually, you reserve this setting for an SQL statement. |
| adCmdTable | 2 | Bases the return set on a previously designed table. Returns all columns from a table based on an internally generated SQL statement. |
| adCmdStoredProc | 4 | Runs a command based on text for a stored procedure. |
| adCmdUnknown | 8 | There is no specification of the type of command text. This is the default. |
| adCmdFile | 256 | Evaluates a command based on the file name for a persistent recordset. |
| adCmdTableDirect | 512 | Evaluates a command as a table name. Returns all columns in a table without any intermediate SQL code. |
The Execute method
The Execute method for a Command object invokes the code behind the Command object (a query, an SQL statement, or a stored procedure). You can specify up to three arguments for the Execute method. The first argument allows the Command object to tell the procedure invoking it how many records it has affected. The second argument can be a Variant array with parameters to drive the command. The third argument tells ADO how to evaluate the source. It can be any of the constant names listed in the table above.
The CreateParameter method
The Command object's CreateParameter method creates a new parameter for a command. After creating the parameter, you can use the Append method to add the parameter to the Parameters collection for a command. Before running a parameter query, you also have to assign a value to the parameter.
Creating a recordset with a select query
One of the most straightforward tasks you can perform with a Command object is to create a recordset based on a select query. The Command object runs the select query and represents its return set. Your code can then open a Recordset object based on the return set from the Command object. The SelectCommand procedure below accomplishes this. It has two parts: One part creates the Command object and a connection for it to relate to a database, and the second part processes a recordset based on the return set from the Command object.
Sub SelectCommand()
Dim cmd1 As Command
Dim rs1 As Recordset, str1 As String
Dim fldLoop As ADODB.Field
'Define and execute command.
Set cmd1 = New ADODB.Command
With cmd1
.ActiveConnection = CurrentProject.Connection
.CommandText = "SELECT MyTable.* FROM MyTable"
.CommandType = adCmdText
.Execute
End With
'Open and print recordset.
Set rs1 = New ADODB.Recordset
rs1.Open cmd1
Do Until rs1.EOF
str1 = ""
For Each fldLoop In rs1.Fields
str1 = str1 & fldLoop.Value & Chr(9)
Next fldLoop
Debug.Print str1
rs1.MoveNext
Loop
End Sub
The first part declares cmd1 as a Command object and then sets three critical properties of the object. Every command must have an ActiveConnection property in order to run against a database. The Command object relies on an SQL statement to represent its select query. You can substitute a saved query. An Execute statement runs the select query. After the Execute method runs, cmd1 contains a reference to a recordset.
The second part of the procedure opens a Recordset object based on cmd1 and prints the return set with tab delimiters (Chr(9)) in the Immediate window. The procedure can handle any number of columns in any number of rows.
Creating a recordset with a parameter query
The following code is an example of a parameter query. This code also has a two-part design. The parameter query in the first part has some extra ADO code lines and a different SQL statement syntax than that of the previous select query. The second part that prints the return set is the same as the previous select query.
Sub ParameterQCommand()
Dim cmd1 As Command
Dim rs1 As Recordset, str1 As String
Dim fldLoop As ADODB.Field
Dim prm1 As ADODB.Parameter, int1 As Integer
'Create and define command.
Set cmd1 = New ADODB.Command
With cmd1
.ActiveConnection = CurrentProject.Connection
.CommandText = "Parameters [Lowest] Long;" & _
"SELECT Column1, Column2, Column3 " & _
"FROM MyTable " & _
"WHERE Column1>=[Lowest]"
.CommandType = adCmdText
End With
'Create and define parameter.
Set prm1 = cmd1.CreateParameter("[Lowest]", _
adInteger, adParamInput)
cmd1.Parameters.Append prm1
int1 = Trim(InputBox("Lowest value?", _
"Programming Microsoft Access 2000"))
prm1.Value = int1
'Run parameter query.
cmd1.Execute
'Open recordset on cmd1 and print it out.
Set rs1 = New ADODB.Recordset
rs1.Open cmd1
Do Until rs1.EOF
str1 = ""
For Each fldLoop In rs1.Fields
str1 = str1 & fldLoop.Value & Chr(9)
Next fldLoop
Debug.Print str1
rs1.MoveNext
Loop
End Sub
The SQL statement syntax uses a new Parameters declaration line that specifies the parameter's name and data type. The WHERE****clause should also reference one or more parameters so that the parameters can affect the return set. These SQL syntax statement adjustments are not by themselves sufficient to make the parameter query work—you must add the parameter and append it to the command by using ADO code.
You invoke the CreateParameter method to add the parameter. The code above uses three arguments with the CreateParameter method. The first one names the parameter, the second designates a data type for the parameter, and the third declares a direction for the parameter. The adParamInput constant is actually the default that declares the parameter an input to the query. Other constants let you designate output, input/output, and return value parameters. After creating a parameter, you must append it to the Parameters collection for the command.
After writing the code to add a parameter, you must assign a value to the parameter to make the parameter query command function properly: The code above uses an InputBox function to gather input from a user. The procedure then invokes the Command object's Execute method to generate a return set.
Deleting records
You can use the Command object to delete, update, and add records to a data source. Command objects offer a programmatic means of maintaining a data source. The DeleteARecord and DeleteAllRecords procedures below prune records from a data source. You designate the data source and the criteria for selecting records by using the SQL DELETE****statement. The SQL view in the Access query window lets you graphically design a query and then copy the code to the CommandText property of a command. You typically want to edit the SQL code from the Access query designer to remove extra parentheses. If your query operates on a single table, you can remove the table prefix before field names. As you can see, the difference between the two delete queries is simply the syntax of the SQL statement.
Sub DeleteARecord()
Dim cmd1 As ADODB.Command
Set cmd1 = New ADODB.Command
With cmd1
.ActiveConnection = CurrentProject.Connection
.CommandText = "DELETE MyTable.Column1 FROM " & _
"MyTable WHERE (((MyTable.Column1)=13));"
.CommandType = adCmdText
.Execute
End With
End Sub
Sub DeleteAllRecords()
Dim cmd1 As ADODB.Command
Set cmd1 = New ADODB.Command
With cmd1
.ActiveConnection = CurrentProject.Connection
.CommandText = "DELETE MyTable.* FROM MyTable"
.CommandType = adCmdText
.Execute
End With
End Sub
Inserting records
When you develop an application, you might want the ability to delete all the records from a table and then reset its contents. The InsertRecords procedure below uses the Command object to stock a table with values. You can use this procedure in conjunction with the DeleteAllRecords procedure to refresh a table with a small base set of records.
Sub InsertRecords()
Dim cmd1 As ADODB.Command
Set cmd1 = New Command
With cmd1
.ActiveConnection = CurrentProject.Connection
.CommandText = "INSERT INTO MyTable(Column1, " & _
"Column2, Column3) VALUES (1,2,'3')"
.CommandType = adCmdText
.Execute
.CommandText = "INSERT INTO MyTable(Column1, " & _
"Column2, Column3) VALUES (4,5,'6')"
.CommandType = adCmdText
.Execute
.CommandText = "INSERT INTO MyTable(Column1, " & _
"Column2, Column3) VALUES (7,8,'9')"
.CommandType = adCmdText
.Execute
.CommandText = "INSERT INTO MyTable(Column1, " & _
"Column2, Column3) VALUES (10,11,'12')"
.CommandType = adCmdText
.Execute
.CommandText = "INSERT INTO MyTable(Column1, " & _
"Column2, Column3) VALUES (13,14,'15')"
.CommandType = adCmdText
.Execute
.CommandText = "INSERT INTO MyTable(Column1, " & _
"Column2, Column3) VALUES (16,17,'18')"
.CommandType = adCmdText
.Execute
End With
End Sub
The InsertRecords procedure has general and specific elements. The general elements do not depend on the design of a particular table. In the code above, the specific elements tailor the general elements for the MyTable table. Figure 2-7 shows MyTable in Design view. It has three columns, named Column1, Column2, and Column3. The first two columns have Long Integer data types, and the third column has a Text data type. (When you add records to a table, you must consider the field data types.)
Figure 2-7. The Design view of the table to which the InsertRecords procedure adds records
The general elements of the InsertRecords procedure are shared with other applications of the Command object. You create a reference to the Command object and set its connection property. For each row that you need to add to a record, three lines are required: the CommandText property setting, which indicates what the command will do; the CommandType property setting, which designates the format of the instruction; and the Execute method, which launches the addition of the new record. You can repeat these three lines for each row added to the data source. If you specify an updatable dynaset as the target, these steps can concurrently add records to two or more tables at the same time.
The syntax of the CommandText SQL statement has three features. (This syntax is not available from the SQL view of the Access query designer.) First, it uses the INSERT INTOkeyword, which is followed by the name of the data source to which you want to add records. Second, it takes the optional step of listing the field names for which it submits values. If you do not take this step, your values in the third step will append in sequential order, which can be a problem if the data source design changes over time. Third, the VALUESkeyword appears before the field values for the new record.
Updating record values
The OddToEven and EvenToOdd procedures below update data source values of Column1 by using the Command object. Figure 2-8 shows a fresh view of the table immediately after the DeleteAllRecords and InsertRecords procedures run. Notice that in Figure 2-8 the Column1 values alternate between odd and even: If the value in Column1 is odd, the value in Column2 is even. The procedures use this information to manage the contents of the table.
Figure 2-8. A Datasheet view of the table that the OddToEven and EvenToOdd procedures update
Sub OddToEven()
Dim cmdO2E As ADODB.Command
Dim intRowsChanged As Integer
Set cmdO2E = New ADODB.Command
With cmdO2E
.ActiveConnection = CurrentProject.Connection
.CommandText = "UPDATE MyTable SET Column1 = " & _
"Column1+1 WHERE ((1*(Column1 Mod 2))=True)"
.CommandType = adCmdText
.Execute intRowsChanged
Debug.Print intRowsChanged & " rows were affected."
End With
End Sub
Sub EvenToOdd()
Dim cmdE2O As ADODB.Command
Set cmdE2O = New ADODB.Command
With cmdE2O
.ActiveConnection = CurrentProject.Connection
.CommandText = "UPDATE MyTable SET Column1 = " & _
"Column11 WHERE ((1*(Column2 Mod 2))=False)"
.CommandType = adCmdText
.Execute
End With
End Sub
The overall design of these procedures should be familiar by now. The most significant difference between these examples and earlier ones is in the syntax of the SQL statement for the CommandText property. In this case, you can easily derive that general syntax from the Access query designer. The WHEREclause in the OddToEven procedure selects records whose Column1 value is odd. The UPDATEpart of the syntax adds 1 to the value to convert the value from an odd to an even number. The Execute method uses one of its built-in arguments to return the number of rows that a command changes. A simple Print method sends this value to the Immediate window for viewing.
The EvenToOdd procedure examines the entry in Column2 to determine whether it should subtract 1 from the value in Column1. When the entry in Column2 is not odd, the SQL statement operates on the value in Column1. This restores the entries in Column1 to their initial values if EvenToOdd runs immediately after the OddToEven procedure runs.
The Errors collection
The Errors collection lets you trap some, but not all, errors that occur in an ADO application. It also returns errors from an OLE DB provider. A single error condition can return multiple errors, each of which causes a new Error object to be placed in the Errors collection. Some errors cause a program termination; others do not. A new failure automatically clears the Errors collection for the entry of errors associated with it. Some ADO errors enter the Err object rather than the Errors collection, but you might want to use the latter collection as well. The Errors collection is most appropriate for handling connection-based errors returned from a remote database through its OLE DB provider.
The Error objects in the Errors collection have five properties that help you gather more information so that you can respond to them with program logic. The Number and Description properties parallel those for the Err object. These properties complement one another. The Number property returns a unique number that identifies an error, and the Description property returns a brief string that describes the error. The NativeError property offers a provider-specific error code. If you often work with a particular provider, this property might provide useful information about how to resolve an error. The Source property names the object or application that originated the error. The SQLState property can contain SQL statement syntax error messages originating from the database server to which you submit your request.
The OpenLookOnlyErrors procedure below is an adaptation of an earlier procedure that reveals the impact of the Connection object's Mode property. A read-only setting for this property causes an error to be generated when you attempt to update a database. Interestingly, this error does not become part of the Errors collection. You can trap the error and respond to it by using the Err object. The last member of the Errors collection also appears in the Err object. The error-trapping logic at the end of the procedure avoids printing two lines with an identical number and description.
Sub OpenLookOnlyErrors()
Dim cnn1 As New Connection
Dim rsCustomers As Recordset
Dim errLoop As Error, intInErrors As Integer
On Error GoTo LookOnlyTrap
cnn1.Mode = adModeRead
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\Office\" & _
"Samples\Northwind.mdb;"
'Spell Northwind incorrectly to generate trappable error.
' cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\Office\" & _
"Samples\Northwinds.mdb;"
'No Errrors element for faulty provider spelling.
' cnn1.Open "Provider=Microsoft.Jets.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\Office\" & _
"Samples\Northwind.mdb;"
Set rsCustomers = New ADODB.Recordset
rsCustomers.ActiveConnection = cnn1
'Spell rsCustomers incorrectly to make a 424 nontrappable error.
'Spell cnn1 as cnn to make 3001 nontrappable error.
' rsCustomer.ActiveConnection = cnn1
'Spell table name as "Customer" to make 2147217900 trappable error.
rsCustomers.Open "Customers"
'adModeRead setting for cnn1.Mode causes an error (3251) here.
'Comment out cnn1.Mode line to enable updates.
rsCustomers.Fields("CustomerID") = "xxxxx"
rsCustomers.Update
Debug.Print rsCustomers.Fields("CustomerID")
rsCustomers.Close
LookOnlyTrap:
intInErrors = 0
For Each errLoop In cnn1.Errors
Debug.Print errLoop.Number, errLoop.Description
intInErrors = intInErrors + 1
Next errLoop
If intInErrors = 0 Then
Debug.Print Err.Number, Err.Description
End If
End Sub
The OpenLookOnlyErrors procedure creates several different types of errors and attempts to write to a read-only connection. Figure 2-9 shows both the VBE Code window and the Immediate window for these errors. The messages with large, negative error codes are from the Errors collection. The remaining errors are ADO errors that report through the Err object. Only two of the errors have the large negative numbers characteristic of the Errors collection. The remaining errors are ADO errors that are available through the Err object. One error within the connection string (error number 3706) still did not report through the Errors collection. This, plus the fact that the last member of the Errors collection appears in the Err object, points out the usefulness of error trapping with the Err object. This same design works for VBA errors.
Figure 2-9. The VBE Code and Immediate windows showing error codes from typical kinds of errors
**Note **You can insert an Option Explicit statement in the general declarations area of a module to eliminate the possibility of certain errors, such as references to objects that do not exist.
The LoopToUsingErrors procedure below offers some alternative approaches to error processing with the Err object. The procedure generates one error and includes a comment that details the modifications to the code that are necessary to create another error. It also responds specifically to two errors and includes a general error handling routine for all others:
- In the case of a 3251 error, the procedure changes the lock type so that the recordset is updatable. This error occurs because the lock type is wrong. To fix the problem, the error-processing code closes the old recordset, resets the LockType property, and reopens the recordset object.
- With a 424 error, the procedure does not try to fix the error but alerts the user to the potential cause of the problem. This error occurs when a method is invoked or a property is set against a variable not declared as an object. For example, a typographical error can cause this problem.
- If the error does not have a 3251 or 424 error number, the routine prints out the number and description properties for the Err object.
Sub LoopToUsingErrors()
On Error GoTo DErrorsTrap
Dim cnn1 As Connection
Dim rsMyTable As Recordset
Set cnn1 = New ADODB.Connection
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\Office\" & _
"Samples\Northwind.mdb;"
Set rsMyTable = New ADODB.Recordset
'Open recordset with defaults.
OpenRSMyTable:
rsMyTable.Open "MyTable", cnn1
'Loop through recordset.
Do Until rsMyTable.EOF
'Make 424 error by using next instead of preceding line.
' Do Until rsMyTables.EOF
If rsMyTable.Fields(0) = 4 Then
'This line makes 3251 error because recordset is readonly.
rsMyTable.Fields(0) = 88
Else
Debug.Print rsMyTable.Fields(0), rsMyTable.Fields(1)
End If
rsMyTable.MoveNext
Loop
rsMyTable.Close
ErrorsExit:
Exit Sub
DErrorsTrap:
If Err.Number = 3251 Then
MsgBox "OLEDB Provider does not support operation. " & _
"Find another way to get the job done or get a new " & _
"OLEDB Provider. Error happened in LoopToDeleteErrors."
Debug.Print rsMyTable.LockType
rsMyTable.Close
rsMyTable.LockType = adLockOptimistic
Resume OpenRSMyTable
ElseIf Err.Number = 424 Then
MsgBox "The code tried to do something requiring an " & _
"object, such as set a property or invoke a method, " & _
"but the code did not have an object. Check spelling."
Else
MsgBox "Check Immediate window for error # and desc."
Debug.Print Err.Number, Err.Description
End If
Resume ErrorsExit
End Sub
The ADOX Library
The ADOX library supports schema and security tasks. You use this library to manage objects and thereby modify the architecture of your application's database design. With the exception of the Catalog object, all objects have matching collections. You use these collections to add and organize new objects in a catalog. Selected objects, such as tables, indexes, keys, and columns, have Properties collections. You use these to manage the behavior of the objects within an application. You manage the Users and Groups collections to control permissions for other ADOX objects, such as tables, views, and procedures. Figure 2-10 shows an overview of the ADOX library.
Figure 2-10. The ADOX object library
The ADOX library is an extension of the ADODB library. The Jet ADO provider fully supports ADOX. Other database providers can selectively implement its features, and you can use the two libraries together to build applications. For example, you can build Command objects with the ADODB library and then save them as procedures with the ADOX library. Or you can search for the availability of a table before basing a recordset on it. If the table does not exist, you can add it and populate it with values. The ability of the ADOX library to define new data structures makes it an alternative to SQL DDL.
The Catalog object
The Catalog object is the highest-level container in the ADOX library. Its members define the schema and security model for a database. Its ActiveConnection property defines the connection to which the catalog belongs. The Connection object is the database. The Catalog object is the database's container for tables, views, procedures, users, and groups within a connection or database. The Name property for a catalog is read-only. You designate it when you declare the catalog. You use the Catalog object's Create method to assign a connection and source to a catalog so that you can concurrently open a new database and gain access to its catalog.
The following code examples show the Catalog object in three typical contexts. The CatCon procedure opens the Northwind database and gives the current application programmatic access to the structure of the database. Notice that you need a Connection object for the catalog so that ADO knows which catalog to make available. You assign the Connection object to the catalog by using its ActiveConnection property. Once ADO knows which database to reference with a catalog, you have programmatic access to the contents of the catalog. You control access by using database and user-level security techniques.
Sub CatCon()
Dim cnn1 As New Connection
Dim cat1 As New Catalog
'Open the catalog of another database.
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\Office\" & _
"Samples\Northwind.mdb;"
Set cat1.ActiveConnection = cnn1
Debug.Print cat1.Tables(0).Name
End Sub
Sub CatCon2()
Dim cnn1 As New Connection
Dim cat1 As New Catalog
Dim proc1 As Procedure
'Open the catalog of this database.
'Print a range of selected collection information.
Set cnn1 = CurrentProject.Connection
Set cat1.ActiveConnection = cnn1
Debug.Print cat1.Tables(1).Name
Debug.Print cat1.Views(0).Name
Debug.Print cat1.Procedures(0).Name
Debug.Print cat1.Users(0).Name
Debug.Print cat1.Groups(0).Name
End Sub
Sub CatCon3()
Dim cat1 As New ADOX.Catalog
'Open the catalog to a new database.
cat1.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\My Documents\NewDB.mdb"
Debug.Print cat1.Tables(0).Name
End Sub
CatCon2 lists a member from each collection within the Catalog object of the current database. While you can reference a table's contents with an ADODB library reference, you must use an ADOX reference to loop through the Columns collection of a table. Only the ADOX library has Tables and Columns collections.
**Note **You must impose a logon requirement before you can list the members of the Users and Groups collections. Any attempt to print them without logging on can generate a run-time error. You can impose a logon requirement by setting a password for the Admin user.
CatCon3 creates a new database and exposes its catalog in one step. Notice that you apply the Create method to the Catalog object. The method takes a connection string as an argument. If the file specified in the connection string already exists, CatCon3 generates a run-time error. When the file runs successfully, it prints out one of the system table names because no user files are available just after the creation of a database.
You can use the Catalog object to enumerate the members of any of the collections within it. The following procedure enumerates the members of the Views collection, which corresponds to the set of all row-returning queries that do not rely on parameters that have Jet databases. When you use a SQL Server database, views are explicitly available from the Access 2000 database container.
Sub ListViews()
Dim cnn1 As New Connection
Dim cat1 As New Catalog
Dim view1 As View
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\Office\" & _
"Samples\Northwind.mdb;"
Set cat1.ActiveConnection = cnn1
For Each view1 In cat1.Views
Debug.Print view1.Name
Next view1
End Sub
The Views collection is available from the catalog for a connection. To enumerate the views, you must declare one view that your code will use to reference each member of the Views collection as it loops through the collection. Printing the Name property of each view provides an inventory of the View objects****in a catalog.
The Table object
The Table object is a member of the Tables collection, which is a member of the Catalog object. Each Table object has a Name property and a Type property. A Table object can be a standard table within the current database or a linked table based on ODBC and non-ODBC data sources. It can even be a view. The Type property values also include two system table types—Jet system tables and the Access system table.
| Type Values for the Table Object |
Description |
|---|---|
| ACCESS TABLE | An Access system table |
| LINK | A linked table from a non-ODBC data source |
| PASS-THROUGH | A linked table through an ODBC data source |
| SYSTEM TABLE | A Jet system table |
| TABLE | A table developed by or for your application |
| VIEW | A table from a row-returning, nonparameterized query |
A Table object in the ADOX library can contain up to three collections: Columns, Indexes, and Keys.
The Column object
The Columns collection is for tables, keys, and indexes. A Column object is roughly comparable to a Field object in the ADODB library. For a table, a column represents a set of data referring to a specific characteristic of the entity represented by the table. The Column object has several properties:
- Name. This****property is the name of the column.
- Type. This property indicates the data type of a column within the table. All the data within a column is of the same type.
- Attributes. The Attributes property describes the characteristics of a column. The two column characteristics are whether the column can contain nulls and whether it has a fixed length.
- DefinedSize. This property designates the maximum size of an entry in the column.
- Precision and NumericScale. These properties are used exclusively for numeric fields, such as integers, currency, and floating point numbers. Precision represents the maximum total number of digits used to convey the value of a column. NumericScale designates how many digits to the right of the decimal point are available to express a value.
When a Column object is an index, other properties, such as SortOrder and RelatedColumn, are also available.
**Note **The NumericScale property can yield confusing results. For example, Currency values have four places to the right of the decimal point, but their NumericScale property equals 0 because Access stores Currency data types as scaled integers. When you modify the Scale setting of a column that uses a Decimal data type in a table's Design view, the column's NumericScale property adjusts accordingly.
The Index object
The Index object sets indexes for a table. It has five properties: Clustered, IndexNulls, Name, PrimaryKey, and Unique. With the exception of the Name property, all are read-only after you append the index. The Name property is the name of the index. Three other properties are Boolean and indicate, respectively, whether the index is clustered (an index is said to be clustered when the physical order of rows matches the indexed order of rows), unique, or a primary key.
The IndexNulls property can assume any of three different values. Setting it to adIndexNullsDisallow causes the Index construction to fail if there is a Null in the index for the column. Assigning the adIndexNullsIsIgnore constant to IndexNulls allows the construction of the index if there is a Null in the index, but sets the Ignore Nulls property (on the Indexes window in the user interface) to Yes. Using the adIndexNullsIsIgnoreAny also constructs the index when the index contains a null, but it sets the Ignore Nulls property to No.
The Key object
The Key object embodies the behavior of foreign keys in its properties. Of course, the Name property is the name of the key. The RelatedTable property designates the table to which a foreign key points. The DeleteRule and UpdateRule properties determine what happens when a primary key is deleted or updated. The Type property is the type of the key and has three options: adKeyForeign for foreign keys, adKeyUnique for unique keys, and adKeyPrimary for primary keys.
Enumerating tables
One of the easiest ways to start working with tables is to enumerate them. The ListTables procedure below shows how to list the tables in the Northwind database. The declarations instantiate cat1 as a Catalog object and tbl1 as a Table object. Next, the procedure assigns the Northwind database and the Jet 4.0 provider to the ActiveConnection property for the catalog. The following loop identifies the longest table name in the catalog. The final segment prints the table names to the Immediate window. A string formula appends blanks to table names so they all occupy a fixed number of characters. The code appends the table type.
Sub ListTables()
Dim cat1 As New ADOX.Catalog
Dim tbl1 As ADOX.Table
Dim intMaxLength As Integer
'Specify active connection for the Catalog object.
cat1.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\Office\" & _
"Samples\Northwind.mdb;"
'Find longest table name.
intMaxLength = 0
For Each tbl1 In cat1.Tables
If Len(tbl1.Name) > intMaxLength Then intMaxLength = _
Len(tbl1.Name)
Next tbl1
'Print table names to Immediate window.
intMaxLength = intMaxLength + 2
For Each tbl1 In cat1.Tables
strName = tbl1.Name
strFiller = String(intMaxLength Len(tbl1.Name), " ")
Debug.Print strName & strFiller & tbl1.Type
Next tbl1
End Sub
Figure 2-11 shows output from the ListTables procedure. Notice that it appears as two columns, each showing a table's name. Next to the name is the Type property for the table. Recall that the Table object includes six types. Four of these appear in the figure.
Figure 2-11. The output from the ListTables procedure
Enumerating fields
The ListTableTypeColumns and ColumnType procedures below are more elaborate samples that dig into table hierarchy and properties. Once ListTableTypeColumns finds a Table object with its Table property set to TABLE, it lists the column names and types of the columns within that table. These appear below the table's name and its column count. Each column name is next to its ColumnType constant. Figure 2-12 shows an excerpt from the listing. The sample is considered rich because it manipulates several types of Table objects and multiple properties, and the objects and properties are at different points on the ADOX object model hierarchy.
Figure 2-12. The output from the ListTableTypeColumns and ColumnType procedures
Sub ListTableTypeColumns()
Dim cat1 As New ADOX.Catalog
Dim tbl1 As ADOX.Table
Dim col1 As ADOX.Column
cat1.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\" & _
"Office\Samples\Northwind.mdb;"
For Each tbl1 In cat1.Tables
If tbl1.Type = "TABLE" Then
strName = tbl1.Name
strFiller = String(30 Len(tbl1.Name), " ")
Debug.Print strName & strFiller & tbl1.Columns.Count
For Each col1 In tbl1.Columns
strFiller = String(20 Len(col1.Name), " ")
Debug.Print String(5, " ") & col1.Name & _
strFiller & ColumnType(col1.Type)
Next col1
End If
Next tbl1
End Sub
Public Function ColumnType(intType As Integer) As String
Select Case intType
Case adVarWChar
ColumnType = "adVarWChar"
Case adCurrency
ColumnType = "adCurrency"
Case adInteger
ColumnType = "adInteger"
Case adDate
ColumnType = "adDate"
Case adWChar
ColumnType = "adWChar"
Case adLongVarWChar
ColumnType = "adLongVarWChar"
Case adLongVarBinary
ColumnType = "adLongVarBinary"
Case adBoolean
ColumnType = "adBoolean"
Case adSmallInt
ColumnType = "adSmallInt"
Case Else
ColumnType = CStr(intType)
End Select
End Function
The declarations include a catalog, a table, and a column from the ADOX library. The catalog's declaration instantiates its object, but the other two are used as item references in collections. Immediately after the declarations, the procedure assigns a connection string to the ActiveConnection property of the Catalog object.
A pair of nested For loops search through the tables and their columns. The outer loop seeks tables whose Table property is set to TABLE. When it finds one, it prints the name and column count for that table. The inner For loop enumerates the columns of the table and handles the formatting of the output. The loop calls the ColumnType function, which returns a character string that represents the constant designating the column's Type property. It is a very robust function, so you can adapt it for your own conversion tasks. If it encounters a type that it does not recognize, it converts the type's value to a string. This helps you to go back into the function and add the new decode specification.
Creating tables
The ADOX library is especially important because you can programmatically create tables along with their indexes and keys.
If you are comfortable adding tables in Design view, you only have to learn a little more to do it programmatically. You define the table and then append it to the catalog's Tables collection. Defining a table involves a similar kind of logic. You first declare a table object variable, and then you append columns to the object variable. When you append the columns, your code can assign data types and other column properties. After you finish defining a table, you might want to populate it with data. Earlier in the chapter, you saw two approaches to tackling this issue (using SQL code and the recordset's AddNew method.) We'll examine this topic in greater detail in Chapter 3.
Adding an index
The AddIndex procedure on the next page demonstrates how to programmatically add an index to a table. As when you create a table, you use the Append method twice. First, you append one or more columns of data to your index. These columns define the index. Next, you set the Name property of the Index object. You can also set several other properties. The commented lines in the procedure below give instructions on how to set several optional properties and describe the impact of assigning values to the properties. After completing the specification of your index, you invoke the Append method again. This time, however, you use it to add the new index to the table's Indexes collection. This completes the task—unless there are run-time errors!
Sub AddIndex()
Dim cat1 As New ADOX.Catalog
Dim tbl1 As New ADOX.Table
Dim idx1 As New ADOX.Index
cat1.ActiveConnection = CurrentProject.Connection
Set tbl1 = cat1.Tables("MyTable")
idx1.Name = "MyFirstIndex"
idx1.Columns.Append ("Column1")
'Rules and syntax for setting IndexNulls property.
'Does not create index if field contains Nulls; yields error.
' idx1.IndexNulls = adIndexNullsDisallow
'Sets Ignore Nulls index property to Yes — creates index.
' idx1.IndexNulls = adIndexNullsIgnore
'Sets Ignore Nulls index property to No — creates index.
' idx1.IndexNulls = adIndexNullsIgnoreAny
'If you want to set the PrimaryKey property . . .
' idx1.PrimaryKey = True
'. . . you must also set the Unique property for the
'primaryKey property to take effect and avoid an error.
' idx1.Unique = True
tbl1.Indexes.Append idx1
End Sub
PKErrorCatcher, shown****below, performs error trapping when you create a new primary key in a table. The same general error trapping approach works for other indexes and foreign keys. The error trap explicitly handles two types of errors and includes a more general trap for other errors.
Sub PKErrorCatcher()
On Error GoTo PKErrorCatcherTrap
Dim cat1 As New ADOX.Catalog
Dim tbl1 As New ADOX.Table
Dim idx1 As New ADOX.Index
Dim iNumber As Integer
cat1.ActiveConnection = CurrentProject.Connection
Set tbl1 = cat1.Tables("MyTable")
PKErrorCatcherTry:
With idx1
.Name = "MyPrimaryKey"
.PrimaryKey = True
.Unique = True
.IndexNulls = adIndexNullsDisallow
End With
idx1.Columns.Append "Column2"
tbl1.Indexes.Append idx1
Set cat1 = Nothing
PKErrorCatcherExit:
Exit Sub
PKErrorCatcherTrap:
If Err.Number = 2147217856 Then
MsgBox "Table currently in use."
ElseIf Err.Number = 2147467259 Then
For Each idx1 In tbl1.Indexes
If idx1.PrimaryKey = True Then
tbl1.Indexes.Delete (iNumber)
Resume PKErrorCatcherTry
End If
iNumber = iNumber + 1
Next idx1
Else
MsgBox "Error #" & Err.Number & ": " & Err.Description
End If
Resume PKErrorCatcherExit
End Sub
The procedure starts by declaring new Catalog, Table, and Index objects. (A primary key is just an index with its Unique and PrimaryKey properties set to True.) The main portion of PKErrorCatcher sets the necessary properties, appends a column to the index, and then appends the index to the table. At least two conditions can cause these simple instructions to fail at run time. First, the table might be open. ADO assigns error number –2147217856 to this condition. When the error trap detects this error number, it displays a message telling the user that the table is currently in use. Second, the attempt to append a new primary key can fail if a primary key is already defined for the table. The procedure deletes the old obsolete primary key and then tries to create the primary key a second time. Since there is no longer a primary key, the routine cannot fail again for that reason.
Setting autoincrement field values
Access 2000 is the first version of Access that lets developers set the start and step value of autoincrement columns. You can programmatically set the start and step value of autoincrement columns by creating the table with Jet 4.0 SQL statements. You use the CREATE TABLE command to create the overall table, and you use the IDENTITY data type in Jet SQL for the autoincrement field. The IDENTITY data type has start and step values that let you specify the initial value for the autoincrement field, as well as how much it increases with each new record. The following SetStartAndStep procedure taps this new technology.
Sub SetStartAndStep()
Dim cnn1 As Connection
Dim cmd1 As Command
Dim tbl1 As New Table
Set cnn1 = CurrentProject.Connection
Set cmd1 = New ADODB.Command
With cmd1
.ActiveConnection = cnn1
'First create a table with two columns.
'Make one column an Identity column.
'Set its start value first, and its step value second.
.CommandType = adCmdText
.CommandText = "CREATE TABLE Contacts (ContactID " & _
"IDENTITY(2,4),ContactName Char)"
.Execute
'After creating the table with the autoincrement/identity
'column, you should add data.
.CommandText = "INSERT INTO Contacts(ContactName) " & _
"Values ('Kevin Mineweaser')"
.CommandType = adCmdText
.Execute
.CommandText = "INSERT INTO Contacts(ContactName) " & _
"Values ('Mike Gilbert')"
.CommandType = adCmdText
.Execute
.CommandText = "INSERT INTO Contacts(ContactName) " & _
"Values ('Neil Charney')"
.CommandType = adCmdText
.Execute
End With
End Sub
The code begins by creating a table. Although this procedure does not have an error-trapping routine, you should either make sure that such a table does not already exist or include an error trap to delete the table if it does exist. Within the CREATE TABLE statement, you specify the IDENTITY data type. You set the autoincrement field's first argument to the initial value and its second argument equal to the step. The code indicates that contacts will start with a value of 2 and increase by 4 for each new entry. Figure 2-13 shows the output from running the SetStartAndStep procedure. Notice that the ContactID field starts at 2 and increments in steps of 4. These are the settings for the IDENTITY data type specified in the procedure.
Figure 2-13. The output from the SetStartAndStep procedure
The View object
A View object is a row-returning query without any parameters that your application saves. When your application saves the other types of queries, it stores them as members of the Procedures collection. You can manipulate the Views collection members by using the Command object and the Views and Tables collections. View objects have two critical properties for the purposes of this discussion: The Name property is the name of the view, and the Command property lets you get at the SQL statement underlying the view.
In this section, we'll construct a view by using the MakeAView procedure and edit the SQL statement underlying an existing view by using the ChangeAView procedure. Both the MakeAView and ChangeAView procedures tap the ViewAView procedure. The code prints three fields from either view to the Immediate window. You'll also learn how to delete a view.
Creating a view
The MakeAView procedure adds a new view by creating a Command object that represents the view and appends the Command object to the Views collection. MakeAView needs a connection to a database. The cnn1 Connection object opens a link to the Northwind database. Then the application's code sets the ActiveConnection property of the Command object in the sample, cmd1, to cnn1. Next, and critically, the code sets the CommandText property of cmd1. The CommandText property holds the SQL statement that sets the view. The SQL statement in MakeAView constructs the view to reveal employee first and last names followed by their phone extensions. The code sets the ActiveConnection property of cat1, a Catalog object, to the Northwind database. An Append command names cmd1 AllEmployees and adds it to the Northwind catalog. Finally, MakeAView calls ViewAView to print the contents of the view****to the Immediate window.
Sub MakeAView()
Dim cnn1 As New ADODB.Connection
Dim cmd1 As New ADODB.Command
Dim cat1 As New ADOX.Catalog
'Open the connection.
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\Office\" & _
"Samples\Northwind.mdb;"
'Create the command representing the view.
Set cmd1.ActiveConnection = cnn1
cmd1.CommandText = "SELECT FirstName, LastName, " & _
"Extension FROM Employees"
'Open the catalog.
Set cat1.ActiveConnection = cnn1
'Create the new view.
cat1.Views.Append "AllEmployees", cmd1
'Show the view.
ViewAView
End Sub
Printing a view
The ViewAView procedure below prints the AllEmployees view. ViewAView starts by making a connection to the Northwind database, and then opens the rst1 Recordset object based on the AllEmployees view. Once the recordset is open and pointing to the right data source, the procedure takes a loop through the records to determine the longest name. Since the recordset is relatively small, there is not much of a penalty for making this extra pass. The maximum length for a name is convenient because it suggests a starting point for printing the extension after the name. This step allows the extension numbers to left-align, irrespective of the length of the names. Before starting the next loop through the names, ViewAView moves back to the recordset. Then, it moves through the names a second time. This time it constructs a string with the help of information gathered in the first loop.
Sub ViewAView()
Dim cnn1 As New ADODB.Connection
Dim rst1 As New ADODB.Recordset
Dim intMaxLength As Integer, Length As Integer
'Open the connection.
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\" & _
"Office\Samples\Northwind.mdb;"
'Find the longest name.
rst1.Open "AllEmployees", cnn1
Do Until rst1.EOF
Length = Len(rst1.Fields("FirstName")) + _
Len(rst1.Fields("LastName"))
If Length > intMaxLength Then intMaxLength = _
Length
rst1.MoveNext
Loop
'Print first name, last name, and third field.
rst1.MoveFirst
Do Until rst1.EOF
strFiller = (intMaxLength + 2) _
(Len(rst1.Fields("FirstName")) + _
Len(rst1.Fields("LastName")))
Debug.Print rst1.Fields("FirstName") & " " & _
rst1.Fields("LastName") & String(strFiller, " ") & _
rst1.Fields(2)
rst1.MoveNext
Loop
End Sub
Modifying a view
The ChangeAView procedure below alters the SQL statement behind a view. You can use this simple approach to add new fields, replace old ones, or even change the whole design of the view, such as by adding sorting and filtering. The procedure modifies the view to show the HomePhone field instead of the Extension field.
Sub ChangeAView()
Dim cnn1 As New ADODB.Connection
Dim cat1 As New ADOX.Catalog
Dim cmd1 As New ADODB.Command
'Open the connection.
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\" & _
"Office\Samples\Northwind.mdb;"
'Open the catalog.
Set cat1.ActiveConnection = cnn1
'Update the view.
cmd1.CommandText = "SELECT FirstName, LastName, " & _
"HomePhone FROM Employees"
cat1.Views("AllEmployees").Command = cmd1
'Show view.
ViewAView
End Sub
The trick to editing the SQL statement behind a view is to open the associated Command object and edit its CommandText property. To do this, you must point the ActiveConnection property of a Catalog object (see cat1 in the code above) to the database containing the view you want to edit—in this case, the Northwind database. Then you assign the SQL statement for the view you want to the CommandText property of a new Command object. Next, you assign that new Command object to the view that you want to change. The assignment automatically saves the new SQL statement over the old one.
Deleting a view
The DeleteAView procedure below deletes one view. It is almost too simple to include, but you will be glad to have it if you want to run MakeAView more than once, because ADO doesn't let you append one view over another of the same name. Therefore, you must first delete the last created view in order to rerun MakeAView. To delete a view, you first set the ActiveConnection of a Catalog object to point at the database that has the view you want to delete. Then you invoke the Delete method for the Views collection while you reference the specific member you want expunged from the schema.
Sub DeleteAView()
Dim cat1 As New ADOX.Catalog
'Open the catalog.
cat1.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office\Office\" & _
"Samples\Northwind.mdb;"
'Delete the procedure.
cat1.Views.Delete ("AllEmployees")
End Sub
The Procedure object
A procedure is a parameterized row-returning query or an action query that adds, deletes, or updates records. We'll discuss parameterized queries in this section; see Chapter 4 for more complete coverage of queries, including procedures that save action queries.
Procedures and views are similar: Both can persist Command objects. Views represent nonparameterized, row-returning queries, and procedures represent the remaining query types. If you enumerate the Procedure objects within a catalog, the enumeration will include the set of all views. However, procedures do not appear in an enumeration of View objects. The syntax for enumerating either is similar.
Procedure objects have two critical properties for this discussion: Name and Command. The Name property is the name of the procedure. The Command property makes accessible the properties of the Command object behind the procedure. The CommandText property is particularly useful because it sets or returns the SQL statement for the Command object. You can use this property to modify the SQL statement in the command behind a procedure or to view the object's SQL statement. Examining the object's SQL statement can acquaint you with the parameters that you need to set in order to run a procedure.
Enumerating views and procedures
The ListMyProcs procedure below actually displays both the Views and Procedures collections. The first For loop enumerates only members of the Views collection. The second loop enumerates members of the Procedures collection and lists both procedures and views. The second loop also displays the SQL statement for each query that it enumerates.
Sub ListMyProcs()
Dim cnn1 As New Connection
Dim cat1 As New Catalog
Dim proc1 As Procedure
Dim view1 As View
'Set database connection for catalog.
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office" & _
"\Office\Samples\Northwind.mdb;"
Set cat1.ActiveConnection = cnn1
'Enumerate views — notice this returns just
'nonparameterized, row-returning queries.
For Each view1 In cat1.Views
Debug.Print "View name: " & view1.Name
Next view1
'Enumerate views — this returns views
'and procedures.
For Each proc1 In cat1.Procedures
Debug.Print "Procedure name: " & proc1.Name
Debug.Print "SQL: " & proc1.Command.CommandText
Next proc1
End Sub
Views and procedures let you save commands. You simply append the Command object to the appropriate collection. The following code saves a parameter query as a stored procedure. This query uses the MyTable table (which appears in several of the preceding procedure examples), and it is available on the companion CD along with the samples to manipulate it. Design and Datasheet views of MyTable appear in Figure 2-7 and Figure 2-8.
Sub SaveParameterQuery()
On Error GoTo SavePQTrap
Dim cmd1 As Command
Dim cnn1 As New Connection
Dim cat1 As New adox.Catalog
'Create connection.
Set cnn1 = CurrentProject.Connection
'Create and define command.
Set cmd1 = New ADODB.Command
With cmd1
.ActiveConnection = cnn1
.CommandText = "Parameters [Lowest] Long;" & _
"SELECT Column1, Column2, Column3 " & _
"FROM MyTable " & _
"Where Column1>=[Lowest]"
.CommandType = adCmdText
End With
'Open the catalog.
Set cat1.ActiveConnection = cnn1
'Create the new procedure based on parameter query.
cat1.Procedures.Append "spLowestRow", cmd1
SavePQExit:
Exit Sub
SavePQTrap:
If Err.Number = 2147217816 Then
'If err.number = 214... query already exists, then . . .
deleteProcedure("spLowestRow")
Resume
Else
Debug.Print Err.Number, Err.Description
End If
End Sub
Sub deleteProcedure(procName as String)
Dim cnn1 As New Connection
Dim cat1 As New adox.Catalog
'Open the catalog.
Set cnn1 = CurrentProject.Connection
Set cat1.ActiveConnection = cnn1
'Delete existing procedure.
cat1.Procedures.Delete (procName)
End Sub
The procedure constructs a parameter query and saves it as a stored procedure. Its query prompts for the lowest value to appear in the first column. Notice that you can set the CommandText and CommandType properties for the query. The SQL statement includes a declaration for the parameter. After creating the Command object for a stored procedure, you use the Append method of the Procedures collection to persist the Command object as a stored procedure. If you run SaveParameterQuery more than once, the second attempt to save spLowestRow generates a run-time error. SaveParameterQuery traps for this error and deletes the old copy if it already exists. The procedure that deletes the stored procedure accepts an argument for the name of the procedure to delete. The ParameterQCommand procedure, shown earlier, performs the same task as the stored procedure, spLowestRow. Since ADO runs stored procedures as compiled objects, they run faster than when you construct a Command object each time you want to execute an SQL statement.
Creating and running stored procedures
The following pair of procedures prepares and runs a stored procedure. The first procedure creates a stored procedure that looks up the extension number for employees in the Northwind database; the second procedure invokes the stored procedure.
Sub procLookupNumber()
Dim cnn1 As New ADODB.Connection
Dim cmd1 As New ADODB.Command
Dim prm1 As ADODB.Parameter
Dim cat1 As New ADOX.Catalog
'Open the connection.
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office" & _
"\Office\Samples\Northwind.mdb;"
'Create the parameterized command.
Set cmd1.ActiveConnection = cnn1
cmd1.CommandText = "SELECT FirstName, LastName, Extension " & _
"FROM Employees WHERE LastName = [LName]"
Set prm1 = cmd1.CreateParameter("[LName]", adWChar, adParamInput, 20)
cmd1.Parameters.Append prm1
'Open the catalog.
Set cat1.ActiveConnection = cnn1
'Create the new procedure based on parameter query.
cat1.Procedures.Append "spEmployeeExtension", cmd1
End Sub
Sub RunLookUpProc()
Dim cnn1 As New Connection
Dim cat1 As New Catalog
Dim rst1 As New Recordset
Dim cmd1 As New Command
Dim prm1 As Parameter
Dim typedName As String
'Create and assign a connection for the catalog.
cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Office" & _
"\Office\Samples\Northwind.mdb;"
Set cat1.ActiveConnection = cnn1
'Set the Command and parameter object references.
Set cmd1 = cat1.Procedures("spEmployeeExtension").Command
Set prm1 = cmd1.CreateParameter("[LName]", adWChar, adParamInput, 20)
cmd1.Parameters.Append prm1
'Gather the parameter value from the user and assign it.
typedName = InputBox("Last name for extension?", _
"Programming Microsoft Access 2000")
prm1.Value = typedName
'Execute the parameter query and show first match.
cmd1.Execute
rst1.Open cmd1
MsgBox "The extension for " & rst1.Fields(0) & _
" " & rst1("LastName") & " is " & rst1.Fields(2), _
vbInformation, "Programming Microsoft Access 2000"
End Sub
The procLookupNumber procedure creates a persistent parameter query as a stored procedure. This query looks up a telephone number extension for an employee based on the employee's last name. The procedure defines the Command object and its associated parameter, [LName]. The adWChar constant designates the parameter as a fixed-width text string. The adParamInput constant designates this parameter for input only, and the trailing number denotes that the parameter can contain up to 20 characters. After defining both the Command and its associated parameter, the procedure sets the catalog connection to the Northwind database and appends the Command object to the Procedures collection with the name spEmployeeExtension.
If you run procLookupNumber more than once, it will fail when it tries to write spEmployeeExtension over itself. Add error trapping to detect this situation, and then create a delete routine. You will need to edit the one used in the previous sample because it deletes procedures in the current project, and this sample needs to delete procedures in the Northwind database.
The RunLookUpProc procedure runs the stored procedure created by the procLookupNumber procedure. This short procedure does four things:
- It creates a connection to the database with the stored procedure.
- It designates object variables that point at the stored procedure and its parameter. The code uses a Command object to refer to the stored procedure.
- It displays a dialog box in which the user must indicate the last name of the employee's extension. The code assigns the return value from the dialog box to the command's parameter.
- It executes the command and opens a recordset based on the command's return set. It displays the first row from the return set in a dialog box. The code uses both naming conventions for selecting one member from the Fields collection in a recordset. You can use either its ordinal position or its Name property.
See also:"Microsoft Access 2000: Class, Form, and Report Modules."