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 1-2-3.

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 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 pass-through 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 update batch 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. 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 CreateWorkspace Type setting. The Jet workspace type is the native default. Any setting for the CreateWorkspace Type 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 using one of these formats:

• DBEngine.Workspaces (0)
• DBEngine.Workspaces ("Name")
• DBEngine.Workspaces! [Name]

All DAO objects have a similar referencing syntax 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 nonJet 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 section "ODBCDirect Workspace Objects").

The Recordsets collection

A Recordset object represents the records in a table or those resulting from a row-returning query. You create new recordsets 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 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 multi-user 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 oneor 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 scroll forward only. 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 multi-user 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 a 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 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 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 BOF marker from the recordset. You can use this marker to flag a movement beyond the first record. Similarly, DAO returns an EOF marker when an application invokes the MoveNext method from the last record. Any attempt to move beyond either the BOF or EOF markers 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 a 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 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 using a TableDef object. You use 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 afield 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 a 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 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 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 persist 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 subelements 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 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:

1. Set the DefaultCursorDriver property for the workspace to dbUseClientBatchCursor.
2. Create a Connection object or a Database object.
3. Invoke the OpenRecordset method for the object from step 2 with a dbOptimisticBatch setting for the LockEdits argument.
4. Edit the fields locally as needed.
5. 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.