Chapter 11: Data Access Objects

C H A P T E R 11

Microsoft Office 97/Visual Basic Programmer's Guide

Data Access Objects

Contents

Working with DAO Objects

Using DAO with Microsoft Jet

Accessing ODBC Data

Using DAO with ODBCDirect

Using ODBCDirect

Microsoft Data Access Objects (DAO) provide a way to control a database from any application that supports Visual Basic for Applications, including Microsoft Access, Microsoft Excel, and Microsoft Visual Basic. Some DAO objects represent the structure of your database, while others represent the data itself. By using DAO, you can create and manage local or remote databases in a variety of formats, and work with their data. This chapter explains how to program with DAO objects from within Microsoft Office applications.

Working with DAO Objects

Microsoft DAO objects provide a way to interact with a database from any application that includes Visual Basic for Applications. DAO objects represent different parts of a database. You can use DAO objects to work with the parts of your database from code. With DAO objects, you can:

Create a database or change the design of its tables, queries, indexes, and relationships.
Retrieve, add, delete, or change the data in the database.
Implement security to protect your data.
Work with data in different file formats and link tables in other databases to your database.
Connect to databases on remote servers and build client/server applications.

Note In order to use DAO objects, you must select the Data Access check box when you install Microsoft Office. If you haven't installed Data Access with Microsoft Office, run Setup again.

DAO objects are organized in a hierarchical relationship. Objects contain collections, and collections contain other objects. The DBEngine object is the toplevel object that contains all the other objects and collections in the DAO object hierarchy. The following table summarizes the DAO objects.

Object Description

Connection Network connection to an Open Database Connectivity (ODBC) database

Container Security information for various types of objects in the database

Database Open database

DBEngine The top-level object in the DAO object hierarchy

Document Security information for individual objects in the database

Error Data access error information

Field Field in a TableDef, QueryDef, Recordset, Index, or Relation object

Group Group account in the current workgroup

Index Table index

Parameter Query parameter

Property Property of an object

QueryDef Saved query definition in a database

Recordset Set of records defined by a table or query

Relation Relationship between two table or query fields

TableDef Saved table definition in a database

User User account in the current workgroup

Workspace Active DAO session

Designing Databases in Microsoft Access

You can design databases in Visual Basic with DAO. However, if you're programming in an application other than Microsoft Access, you may find it faster to design your database in the Microsoft Access user interface, then write DAO code for any additional functionality that you want. With Microsoft Access, you can quickly and easily design tables, queries, indexes, and relationships; link tables from external data sources; and implement security. You can then open the database with DAO from another application that hosts Visual Basic.

There are a few things to keep in mind when you create a database in Microsoft Access:

When you open an .mdb file created in Microsoft Access from another application, you can't work with Microsoft Access forms, reports, macros, or modules. You should design forms and reports and write all Visual Basic code from the application in which you're working.

If you write code to work with your database within Microsoft Access, that code will not necessarily run if you copy it to a module in another application, such as Microsoft Excel. You may need to modify the code and remove any Microsoft Accessspecific objects, methods, properties, or functions.

In Microsoft Access, you use the CurrentDb function to return a reference to the database that's currently open in the Microsoft Access window. You can then use DAO to work with the current database. If you use this code in an application other than Microsoft Access, you'll need to change code that calls the CurrentDb function so that it calls the OpenDatabase method of a Workspace object instead.

Microsoft Access creates additional properties for DAO objects. When you create a database with DAO in Visual Basic, then open it in Microsoft Access, you may notice that some new properties are defined for some of your DAO objects. For example, a Field object in the Fields collection of a TableDef object may have a Description property, which is created by Microsoft Access. You can see these properties when you enumerate through the Properties collection for a DAO object.

Setting a Reference to the Microsoft DAO Object Library

To work with DAO objects from within any application, you must have a reference to the Microsoft DAO 3.5 object library. Microsoft Access sets this reference automatically. You may need to set it yourself if you're working within another Microsoft Office application.

To set a reference to the Microsoft DAO 3.5 object library from a Microsoft Office application other than Microsoft Access, open the Visual Basic Editor, click References on the Tools menu, and then select the Microsoft DAO 3.5 Object Library check box. Once you've set a reference to the DAO object library, you can view the DAO objects in the Object Browser by clicking DAO in the Project/Library box.

Some objects, properties, and methods that were supported in earlier versions of Microsoft DAO have been replaced by new objects, properties, and methods with more complete functionality, and are no longer supported by DAO version 3.5. If you're working with an application created in an earlier version of Microsoft DAO, you can set a reference to the Microsoft DAO 2.5/3.5 compatibility library rather than to the Microsoft DAO 3.5 object library. The Microsoft DAO 2.5/3.5 compatibility library contains all of the objects, methods, and properties that are in the Microsoft DAO 3.5 object library, plus some that existed in DAO version 2.5, but that are no longer supported in DAO version 3.5.

Code that uses objects, methods, and properties that were available in DAO version 2.5 but are no longer available in DAO version 3.5 will continue to run when you reference the Microsoft DAO 2.5/3.5 compatibility library. However, it's a good idea to update your code to take advantage of the features of DAO version 3.5, and to write new code that uses the objects, properties, and methods provided by the Microsoft DAO 3.5 object library. The Microsoft DAO 2.5/3.5 compatibility library is larger, so it requires more resources. Also, future versions may not support some objects, methods, and properties which are now available in the compatibility library.

To determine whether you need to use the compatibility library, make sure there is a reference set to the Microsoft DAO 3.5 object library and compile all modules that contain DAO code. If your code compiles without any problems, you can use the Microsoft DAO 3.5 object library. If your DAO code generates compile errors, then you should set a reference to the Microsoft DAO 2.5/3.5 compatibility library and try to compile your code again.

For more information about which DAO features are supported in the DAO 2.5/3.5 compatibility library but not in the Microsoft DAO 3.5 object library, search DAO Help for "Obsolete features in DAO," or search Microsoft Access Help for "DAO, compatibility with previous versions."

Referring to DAO Objects in Visual Basic

You refer to DAO objects in code in the same way that you refer to other objects. Because the DBEngine object doesn't have a collection, you can refer to it directly. You must refer to other objects within their collections and according to their positions in the object hierarchy.

You can refer to any type of object within a collection in one of two ways: by its Name property setting or by its index number, which indicates its position within the collection. DAO objects are indexed beginning with zero. This means that the first object in a collection has an index number of 0, the second object has an index number of 1, and so on. The following examples, which refer to a Database object within the Databases collection, illustrate both ways to refer to an object within a collection.

Databases("database name")
Databases(0)

To refer to a Database object in code, you also need to refer to it according to its position within the object hierarchy. The following code fragment shows how you can actually refer to a Database object in code. The Database object is the first member of the Databases collection of the default Workspace object, which is a member of the Workspaces collection of the DBEngine object. Note that if you're working in an application other than Microsoft Access, you must open a database with the OpenDatabase method before you run this code.

Dim dbs As Database
Set dbs = DBEngine.Workspaces(0).Databases(0)

When you work with DAO objects from any application other than Microsoft Access, you may want to qualify the object with the Visual Basic name of the DAO object library, which is DAO. By qualifying objects when you use them, you ensure that Visual Basic always creates the correct object. The following example declares a DAO object variable of type Database:

' Qualify object variable type.
Dim dbs As DAO.Database

Adding New DAO Objects to a Collection

As stated earlier in this chapter, some DAO objects represent the structure of the database, and others provide a means for you to work with the data stored in the database. Objects that represent the structure of the database are saved with the database. Objects that you use to work with the data in the database generally are not saved, but are created each time you need them.

When you create a new DAO object to be saved with the database, you must append it to the appropriate collection of saved objects by using that collection's Append method. The following example creates a new TableDef object named ArchivedInvoices with a new Field object named OrderID. It appends the new Field object to the Fields collection of the new TableDef object, and it appends the TableDef object to the TableDefs collection of the Database object that represents the open database.

Note The following example, and other examples in this chapter, use the Northwind sample database to illustrate concepts of DAO programming. In order to try these examples, you need to have installed the Northwind sample database which is included with Microsoft Access. By default, it is installed in the C:\Program Files\Microsoft Office\Office\Samples folder. If you haven't installed the Northwind sample database, you can install it by running Setup again.

Function AddTable() As Boolean
	' Declare object variables and constant.
	Dim dbs As Database, tdf As TableDef, fld As Field
	Const conPath As String = _
		"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"

	On Error GoTo Err_AddTable
	' Assign current database to database variable.
	Set dbs = DAO.DBEngine.Workspaces(0).OpenDatabase(conPath)
	' Create new table and field, and assign to table and field variables.
	Set tdf = dbs.CreateTableDef("ArchivedInvoices")
	Set fld = tdf.CreateField("OrderID", dbLong)

	' Add field to table's Fields collection.
	tdf.Fields.Append fld
	' Add table to database's TableDefs collection.
	dbs.TableDefs.Append tdf 
	dbs.Close
	AddTable = True

Exit_AddTable:
	Exit Function

Err_AddTable:
	MsgBox
"Error " & Err & ": " & Err.Description
	AddTable = False
	Resume Exit_AddTable
End Function

Note The preceding example uses the OpenDatabase method to open the Northwind sample database, return a reference to it, and assign this reference to an object variable of type Database. If you're programming within Microsoft Access, use the CurrentDb function to return a reference to the database that's currently open in Microsoft Access.

Working with External Data

You can use DAO to work with databases in different formats. There are three different categories of database formats that are accessible through DAO. The first type of format is the Microsoft Jet format. You can use DAO to work with all databases created with the Microsoft Jet database engine, including those created in Microsoft Access, Microsoft Visual Basic, Microsoft Visual C++®, and Microsoft Excel.

The second type of database format is the installable ISAM format. An installable ISAM is a driver that provides access to external database formats through DAO and the Microsoft Jet database engine. You must use your application's Setup program to install any installable ISAMs that you want to use. Installable ISAMs are loaded by Microsoft Jet when you refer to them in code. The individual database formats for which installable ISAMs are available include:

Microsoft FoxPro®, versions 2.0, 2.5, 2.6, 3.0 (readonly), and DBC

dBASE III, dBASE IV, and dBASE version 5.0

Paradox, versions 3.x, 4.x, and 5.x

Microsoft Excel version 3.0, 4.0, 5.0, 7.0, and 8.0 worksheets

Microsoft Exchange/Outlook

Lotus 123 WK1, WK3, and WKS spreadsheets

Delimited and fixedwidth text files in tabular format

Tabular data in Hypertext Markup Language (HTML) files

The third type of database format that is accessible through DAO is the Open Database Connectivity (ODBC) data source. ODBC data sources, such as Microsoft SQL Server™ versions 4.2 and later, require an ODBC driver. Often an ODBC data source resides on a network server. ODBC is useful for developing client/server applications. The next section introduces two ways to work with ODBC data sources through DAO.

Using DAO to Work with ODBC Data Sources

There are two different ways to use DAO to work with ODBC data sources: through Microsoft Jet, or by means of a new technology called ODBCDirect. If you're working with a database created with the Microsoft Jet database engine or in an external format supported by an installable ISAM, all DAO operations are processed through Microsoft Jet. If you're working with an ODBC data source, you can either process DAO operations through Microsoft Jet, or you can use ODBCDirect to circumvent the Microsoft Jet engine and work directly with the data in the ODBC data source.

Whether you use DAO with Microsoft Jet or with ODBCDirect to work with an ODBC data source depends on what kind of operations you need to perform on the data source. You can use DAO with Microsoft Jet when you need to take advantage of Microsoft Jet's unique features for ODBC operations, such as the ability to create or modify objects or to join data from different database formats.

You can use ODBCDirect when you need to run queries or stored procedures against a backend server, such as Microsoft SQL Server, or when your client application needs only the specific capabilities of ODBC, such as batch updates or asynchronous queries. ODBCDirect can also make certain client/server operations significantly faster.

Because not all DAO features are available with ODBCDirect, Microsoft DAO still supports ODBC through the Microsoft Jet database engine. You can use ODBC through Microsoft Jet, ODBCDirect, or both, with a single ODBC data source.

Which of these two methods you can use to access an ODBC data source is determined by what type of workspace you're working in. A workspace, represented by a Workspace object, is an active session for a particular user account. A session marks a sequence of operations performed by the database engine. A session begins when a particular user logs on and ends when that user logs off. The operations that a user can perform during a session are determined by the permissions granted to that user. If you don't specifically create a workspace, then DAO creates a default workspace for you.

With Microsoft DAO version 3.5, you can create either of two types of workspaces for ODBC operations. If you create a Microsoft Jet workspace, you can use DAO with Microsoft Jet to access ODBC data. If you create an ODBCDirect workspace, you can use DAO to work directly with the data in the ODBC data source, without going through the Microsoft Jet database engine.

Each type of workspace has its own object model. The next section of this chapter discusses the object model for the Microsoft Jet workspace. Later sections discuss the advantages of using each type of workspace and describe the object model for ODBCDirect workspaces.

Using DAO with Microsoft Jet

Microsoft Jet workspaces include objects that you can use to define the structure of your database, such as the TableDef, QueryDef, Field, Index, Parameter, and Relation objects. Microsoft Jet workspaces also include objects that you can use to manipulate your data, such as the Recordset object. You can use other objects, such as the User, Group, Container, and Document objects, to secure your application. The following diagram shows the object model for Microsoft Jet workspaces.

The DBEngine Object

As previously mentioned, the DBEngine object is the toplevel object in the DAO object hierarchy. It contains all other DAO objects and collections. The DBEngine object is the default object in the object model, so in many cases you don't need to refer to it explicitly.

The DBEngine object contains two collections: the Workspaces collection and the Errors collection. The Workspaces collection is the default collection of the DBEngine object, so you don't need to refer to it explicitly. You can return a reference to the first Workspace object in the Workspaces collection of the DBEngine object in any of the following ways:

Set wrk = DBEngine.Workspaces(0)
Set wrk = DBEngine(0)
Set wrk = Workspaces(0)

If you don't specifically create a new Workspace object, DAO automatically creates a default workspace when you need it. The settings of the DefaultUser and DefaultPassword properties of the DBEngine object specify the default user name and password to be used with the default Workspace object. By default, the DefaultUser property is set to Admin and the DefaultPassword property is set to a zerolength string ("").

The setting for the DefaultType property of the DBEngine object determines whether the default workspace is a Microsoft Jet workspace or an ODBCDirect workspace. By default, the DefaultType property is set to dbUseJet, and the default workspace is a Microsoft Jet workspace. When you're creating a workspace, you can override the setting for this property by specifying either dbUseJet or dbUseODBC as the type argument of the CreateWorkspace method. For example, if the DefaultType property is set to dbUseJet and you want to create an ODBCDirect workspace, specify the dbUseODBC constant as the type argument of the CreateWorkspace method. Conversely, if the DefaultType property is set to dbUseODBC and you want to create a Microsoft Jet workspace, specify the dbUseJet constant as the type argument of the CreateWorkspace method.

You can use some of the methods of the DBEngine object to maintain your database. For example, the CompactDatabase method copies your database and compacts it. The RepairDatabase method attempts to repair a database that's been damaged.

For more information about the DBEngine object, search DAO Help for "DBEngine object."

The Workspace Object and the Workspaces Collection

The DAO Workspace object defines a session for a user, based on the user's permissions. You use the Workspace object to manage the current session. The Workspace object contains open databases and provides mechanisms for simultaneous transactions and for securing your application. The Workspaces collection contains all active Workspace objects of the DBEngine object that have been appended to the Workspaces collection.

When you begin working with DAO objects in Visual Basic, DAO automatically creates a default workspace. To refer to the default workspace, you can refer to the index number of the first Workspace object in the Workspaces collection, as shown in the following example:

Dim wrk As Workspace
Set wrk = Workspaces(0)

DAO workspaces can be shared or hidden. A workspace is hidden until the user marks it as shared by appending the Workspace object to the Workspaces collection. After a workspace has been appended, you can access it throughout your code simply by referring to it within the Workspaces collection. If you need a Workspace object only within a particular procedure, you can create the Workspace object but not append it to the Workspaces collection.

As noted earlier in this chapter, there are two types of Workspace objects: Microsoft Jet workspaces and ODBCDirect workspaces. In a Microsoft Jet workspace, you can use DAO with the Microsoft Jet database engine to access data in Microsoft Jet databases, installable ISAM data sources, and ODBC data sources. In an ODBCDirect workspace, you can use DAO to access data in ODBC data sources, without going through the Microsoft Jet database engine. You can work with both Microsoft Jet and ODBCDirect workspaces from within a single application.

For more information about ODBCDirect workspaces, see "Using DAO with ODBCDirect" later in this chapter.

Creating a New Microsoft Jet Workspace

To create a new Microsoft Jet workspace, use the CreateWorkspace method of the DBEngine object. The following code creates a Microsoft Jet workspace. The constant specified for the type argument, dbUseJet, specifies that the workspace will be a Microsoft Jet workspace. If the DefaultType property of the DBEngine object is set to dbUseJet, then you don't need to specify a value for the type argument; DAO automatically creates a Microsoft Jet workspace.

Dim wrk As Workspace
Set wrk = CreateWorkspace("JetWorkspace", "Admin", "", dbUseJet)

Newly created Workspace objects — those created with the CreateWorkspace method — are not automatically appended to the Workspaces collection. You can use the Append method of the Workspaces collection to append a new Workspace object if you want it to be part of the collection. However, you can use the Workspace object even if it's not part of the collection. Append the new Workspace object to the Workspaces collection if you want to use the workspace from procedures other than the one in which you created it.

For more information about creating a workspace, search DAO Help for "CreateWorkspace method."

The Error Object and the Errors Collection

The Error object contains information about an error that occurred during a DAO operation. More than one error can occur during a single DAO operation; each individual error is represented by a separate Error object. The Errors collection contains all of the Error objects that correspond to a single DAO operation. When a subsequent DAO operation generates an error, the Errors collection is cleared, and one or more new Error objects are placed in the Errors collection. DAO operations that don't generate any errors have no effect on the Errors collection.

The first Error object in the Errors collection represents the lowest level error, the one that occurred closest to the ODBC data source. The second represents the next higher level error, and so forth. For example, if an ODBC error occurs while trying to open a Recordset object, the first Error object, Errors(0), contains the lowest level ODBC error; other Error objects contain the ODBC errors returned by the various layers of ODBC, and the last Error object contains the error returned by DAO. In this case, the ODBC driver manager, and possibly the driver itself, return separate Error objects. The index number of the last Error object in the collection, the DAO error, is one less than the value returned by the Count property of the Errors collection. The Visual Basic Err object contains the same error as the last Error object in the DAO Errors collection.

The following example tries to insert values into a table that doesn't exist, causing two DAO errors.

Note The following example, and other examples in this chapter, use the Microsoft SQL Server Pubs sample database to illustrate concepts of client/server programming. This database is included with Microsoft SQL Server. If you don't have Microsoft SQL Server, you can adapt the example to your work with your data source, or simply study it to understand the concepts. Before you can work with any ODBC data source, you must register it. For information about registering an ODBC data source, see "Registering an ODBC Data Source" later in this chapter.

Private Sub CauseODBCError()
	Dim dbs As Database, errObj As Error

	On Error GoTo Err_CauseODBCError
	Set dbs = OpenDatabase("", 0, 0, "ODBC;UID=sa;PWD=;DATABASE=Pubs;DSN=Publishers")
	dbs.Execute "INSERT INTO SomeTable VALUES (1,2,3)", dbSQLPassThrough
	Exit Sub

Err_CauseODBCError:
	For Each errObj In Errors
		Debug.Print errObj.Number, errObj.Description
	Next
	Resume Next
End Sub

The Database Object and the Databases Collection

The Database object represents an open database. It can be a Microsoft Jet database or an external data source. The Databases collection contains all currently open databases. The following table shows the relationship between the Database object and the Databases collection and other objects and collections in a Microsoft Jet workspace.

Object or collection Is contained by Contains

Database object Databases collection Containers collection
QueryDefs collection
Properties collection
Recordsets collection
Relations collection
TableDefs collection

Databases collection Workspace object Database objects

Opening a Database Object

To open a database and return a reference to the Database object that represents it in any application other than Microsoft Access, use the OpenDatabase method of the DBEngine object or of a Workspace object. When you use the OpenDatabase method of the DBEngine object, Microsoft DAO opens the database in the default workspace, as shown in the following example.

Function RetrieveRecordset(strDbName As String, strSource As String) As Boolean
	Dim dbs As Database
	Dim rst As Recordset 

	On Error GoTo Err_RetrieveRecordset
	Set dbs = OpenDatabase(strDbName)
	Set rst = dbs.OpenRecordset(strSource, dbOpenDynaset)
	' Perform some operation with recordset.
		.
		.
		.
	RetrieveRecordset = True

Exit_RetrieveRecordset:
	rst.Close
	dbs.Close
	Exit Function

Err_RetrieveRecordset:
	MsgBox "Error " & Err & ": " & Err.Description
	RetrieveRecordset = False
	Resume Exit_RetrieveRecordset
End Function

If you're working within Microsoft Access, use the Microsoft Access CurrentDb function to return a reference to the database that's currently open. Use the OpenDatabase method to open databases other than the one that's currently open, or to open databases in an ODBCDirect workspace. The following example uses the CurrentDb function to return a reference to the database that is currently open in Microsoft Access.

Dim dbs As Database
Set dbs = CurrentDb
Debug.Print dbs.Name

Creating Database Replicas with DAO

If you need to maintain two or more copies of a database, you can replicate the database. When you replicate a database, you designate the database to be the Design Master and create one or more copies of it that are identical in structure and data; the copies are called replicas. You can create multiple replicas of a database and maintain them on the same computer or over a network. You can add, change, or delete objects only in the Design Master. You can change data in the Design Master or in any of the replicas. When a user changes data within one replica, the users of other replicas can synchronize their replica, so that the same data is maintained in all replicas.

You can use DAO to make a database replicable, create replicas, synchronize replicas, and manage a set of replicas. You can also use DAO to create partial replicas. Partial replicas are replicas that contain only a subset of records in a full replica. By using partial replicas, you can synchronize a replica with only the data that you need, rather than with an entire database. For more information about partial replicas, search Microsoft Access Help for "partial replicas."

To replicate a database with DAO, you must first make the database replicable by setting either the Replicable or the ReplicableBool property of the Database object. These properties don't exist on the Database object until you create them and append them to the Properties collection. After you've made the database replicable, you can create one or more replicas of it. The following example backs up a database, makes it replicable by setting the ReplicableBool property to True, and creates a replica by using the DAO MakeReplica method.

Function ReplicateDatabase(strDBName As String) As Boolean
	Dim dbs As Database, prp As Property
	Dim strBackup As String, strReplica As String
	Const conPropNotFound As Integer = 3270

	On Error GoTo Err_ReplicateDatabase
	If InStr(strDBName, ".mdb") > 0 Then
		strBackup = Left(strDBName, Len(strDBName) - 4)
	Else
		strBackup = strDBName
	End If

	strReplica = strBackup & "Replica" & ".mdb"
	If MsgBox("Make backup copy of file?", vbOKCancel) = vbOK
Then
		strBackup = strBackup & ".bak"
		FileCopy strDBName, strBackup
		MsgBox "Copied file to " & strBackup
	End If

	Set dbs = OpenDatabase(strDBName, True)
	dbs.Properties("ReplicableBool") = True
	dbs.MakeReplica strReplica, "Replica of " & strDBName
	MsgBox "Created replica '" & strReplica & "'."
	dbs.Close
	ReplicateDatabase = True

Exit_ReplicateDatabase: 
	Exit Function

Err_ReplicateDatabase:
	If Err = conPropNotFound Then
		Set prp = dbs.CreateProperty("ReplicableBool", dbBoolean, True)
		dbs.Properties.Append prp
		Resume
Next
	Else
		MsgBox "Error " & Err & ": " & Err.Description
	End If
	ReplicateDatabase = False
	Resume Exit_ReplicateDatabase
End Function

Note The Replicable and ReplicableBool properties are functionally identical. The only difference between them is that the Replicable property setting is a string, and the ReplicableBool property setting is a Boolean value.

For more information about database replication and the DAO properties and methods that you can use for replication, search DAO Help for "replication."

The TableDef Object and the TableDefs Collection

A TableDef object represents the stored definition of a base table or a linked table in a Microsoft Jet workspace. The TableDefs collection contains all stored TableDef objects in a database. The following table shows the relationship between the TableDef object and the TableDefs collection and other objects and collections in a Microsoft Jet workspace.

Object or collection Is contained by Contains

TableDef object TableDefs collection Fields collection
Indexes collection
Properties collection

TableDefs collection Database object TableDef objects

Creating a Table with Code

To create a table with DAO code, use the CreateTableDef method of a Database object. After you've created a new TableDef object, but before you append it to the database, you must define one or more fields for the table. The following example creates a table that contains some of the error codes and strings used or reserved by Visual Basic in the Northwind sample database.

Function CreateErrorsTable() As Boolean
	Dim dbs As Database, tdf As TableDef, fld As Field, idx As Index
	Dim rst As Recordset, intCode As Integer, strErr As String

	Const conAppObjErr = "Application-defined or object-defined error"

	' Create Errors table with ErrorCode and ErrorString fields.
	Set dbs = CurrentDb

	On Error Resume Next
	' Delete any existing Errors table. 
	dbs.TableDefs.Delete "Errors"

	On Error GoTo Error_CreateErrorsTable
	' Create table. 
	Set tdf = dbs.CreateTableDef("Errors")
	' Create fields.
	Set fld = tdf.CreateField("ErrorCode", dbInteger)
	tdf.Fields.Append fld
	Set fld = tdf.CreateField("ErrorString", dbMemo)
	tdf.Fields.Append fld
	dbs.TableDefs.Append tdf 

	' Create index.
	Set idx = tdf.CreateIndex("ErrorCodeIndex") 
	Set fld = idx.CreateField("ErrorCode")
	With idx
		.Primary = True
		.Unique = True
		.Required = True
	End With
	idx.Fields.Append fld 
	tdf.Indexes.Append idx

	' Open recordset on Errors table.
	Set rst = dbs.OpenRecordset("Errors")
	' Set recordset's index.
	rst.Index = "ErrorCodeIndex"

	' Show hourglass pointer.
	DoCmd.Hourglass True

	' Loop through error codes.
	For intCode = 1 To 32767
		On Error Resume Next
		strErr = ""
		' Attempt to raise each error.
		Err.Raise intCode

		' Check whether error is VBA, DAO, or Access error.
		' If error is not a VBA error, the Description property
		' of the Err object contains "Application-defined or object-defined error".
		If Err.Description <> conAppObjErr Then
			strErr = Err.Description

		' Use AccessError method to return descriptive string for
		' DAO and Access errors.
		ElseIf AccessError(intCode) <> conAppObjErr Then
			strErr = AccessError(intCode)
		End If

		' If error number has associated descriptive string, add to table.
		If Len(strErr) > 0 Then
			' Add new record to recordset.
			rst.AddNew
			' Add error number to table.
			rst!errorcode = intCode
			' Add descriptive string to table.
			rst!ErrorString.AppendChunk strErr
			' Update record.
			rst.Update
		End If
	Next intCode

	DoCmd.Hourglass False
	' Close recordset.
	rst.Close
	MsgBox "Errors table created."
	' Show new table in Database window.
	RefreshDatabaseWindow

	CreateErrorsTable = True

Exit_CreateErrorsTable:
	Exit Function

Error_CreateErrorsTable:
	MsgBox Err & ": " & Err.Description
	CreateErrorsTable = False
	Resume Exit_CreateErrorsTable
End Function

Linking a Table to a Database

To use tables from an external data source in your database, you can link them to your database. You can link tables that reside in another Microsoft Jet database, or tables from other programs and file formats, such as Microsoft Excel, dBASE, Microsoft FoxPro, Paradox, or previous versions of Microsoft Jet. This is more efficient than opening the external database directly, especially if the table comes from an ODBC data source.

To link a table to your database, use the CreateTableDef method to create a new table. Next, specify settings for the Connect and SourceTableName properties of the TableDef object. You can also set the Attributes property of the TableDef object to specify that the object has certain characteristics. Finally, append the TableDef object to the TableDefs collection.

For more information about the Connect, SourceTableName, and Attributes properties, search DAO Help for the name of the property.

The following example links a Microsoft Excel version 8.0 worksheet to a database as a table.

Important Before you run this code, make sure that the Microsoft Excel ISAM driver (Msexcl35.dll) is installed on your system. If it's not, you need to run Setup again to install it. The Microsoft Excel ISAM driver enables Microsoft Excel 97 files to work with the Microsoft Jet database engine. For more information about working with the Microsoft Excel ISAM driver, search Microsoft Access Help for "Microsoft Excel driver."

Function LinkExcelTable() As Boolean
	Dim dbs As DAO.Database, tdf As DAO.TableDef

	Const errNoISAM As Integer = 3170
	Const conPath As String = _ 
		"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"

	On Error GoTo Err_LinkExcelTable
	' Return a reference to Northwind database.
	Set dbs = OpenDatabase(conPath)
	' Create new TableDef object.
	Set tdf = dbs.CreateTableDef("LinkedTable")
	' Specify range that is source table.
	tdf.SourceTableName = "DataRange"
	' Specify connect string.
	tdf.Connect = "EXCEL 8.0; DATABASE=C:\My Documents\XLTable.xls"
	' Append new TableDef object.
	dbs.TableDefs.Append tdf
	LinkExcelTable = True

Exit_LinkExcelTable:
	Exit Function

Err_LinkExcelTable:
	If Err = errNoISAM Then
		Dim strErr As String
		strErr = Err & ": " & Err.Description
		strErr = strErr _
			& "You may not have the ISAM driver installed properly on your computer, " _
			& "or you may have specified the Connect string incorrectly." _
			& " Check the Connect string and the ISAM driver."
		MsgBox strErr, vbOKOnly, "Error!"
	Else
		MsgBox "Error " & Err & ": " & Err.Description
	End If
End Function

The Field Object and the Fields Collection

In a Microsoft Jet workspace, the Field object represents a field in a table, query, index, relation, or recordset. The Fields collection contains all Field objects associated with a TableDef, QueryDef, Index, Relation, or Recordset object. The following table shows the relationship between the Field object and the Fields collection and other objects and collections in a Microsoft Jet workspace.

Object or collection Is contained by Contains

Field object Fields collection Properties collection

Fields collection TableDef object
Index object
QueryDef object
Recordset object
Relation object
Field objects

The Fields collection is the default collection of a TableDef, QueryDef, Index, Relation, or Recordset object, which means that you don't need to explicitly refer to the Fields collection. For example, the following code fragment returns a reference to the LastName field in the Employees table in the Northwind sample database.

Dim dbs As Database, tdf As TableDef, fld As Field

Const conPath As String = _
   "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"

Set dbs = OpenDatabase(conPath)
Set tdf = dbs.TableDefs("Employees")
Set fld = tdf!LastName

In the Fields collection of a TableDef, QueryDef, Index, or Relation object, the Field object is a structural unit. It represents a column in a table with a particular data type. If you're creating a database in Microsoft Access, you can define fields for any of these objects and set most of their properties in the Microsoft Access user interface, rather than by programming with DAO.

In a Recordset object, a Field object contains data, and you can use it to read data from a record or write data to a record. You can't work with the fields in a Recordset object in the Microsoft Access user interface; you must use DAO.

The Fields collection of a TableDef object contains all of the fields defined for a particular table. For a QueryDef object, the Fields collection contains fields that are included in the QueryDef object from one or more tables. The Fields collection of an Index object includes the one or more fields on which the index is defined.

For a Relation object, the Fields collection contains the fields involved in a relationship. Typically, there are two fields in the Fields collection of a Relation object. One is the field that is the primary key in the table, specified by the Table property of the Relation object; the other is the field that is the corresponding foreign key in the table, specified by the ForeignTable property of the Relation object.

The Fields collection of a Recordset object contains the fields specified in the source argument of the OpenRecordset method. The source argument specifies the source of the records for the new Recordset object and can be a table name, a query name, or an SQL statement that returns records.

The Value property of a Field object applies only to a Field object in the Fields collection of a Recordset object. The Value property returns the value of the data stored in that field for the current record. Because the Value property is the default property of a Field object, and the Fields collection is the default collection of a Recordset object, you can return the value of a field without explicitly referring to either the Fields collection or the Value property. The following code shows three ways you can refer to the Value property. It prints the value of the LastName, FirstName, and Title fields for the first record in a tabletype Recordset object based on the Employees table.

Dim dbs As Database, rst As Recordset
Const conPath As String = _
   "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"

Set dbs = OpenDatabase(conPath)
Set rst = dbs.OpenRecordset("Employees")
' Explicitly reference Fields collection and Value property.
Debug.Print rst.Fields("LastName").Value
' Implicitly reference Fields collection, explicitly reference Value property.
Debug.Print rst!FirstName.Value
' Implicitly reference Fields collection and Value property.
Debug.Print rst!Title

The Index Object and the Indexes Collection

The Index object represents an index on a table in your database in a Microsoft Jet workspace. The Indexes collection contains all of the Index objects defined for a particular table. The following table shows the relationship between the Index object and the Indexes collection and other objects and collections in a Microsoft Jet workspace.

Object or collection Is contained by Contains

Index object Indexes collection Fields collection
Properties collection

Indexes collection TableDef object Index objects

An index speeds up searching and sorting on a table. You can improve query performance in your database by indexing fields on both sides of joins, fields that are sorted, or fields that are used to specify criteria for a query. However, indexes add to the size of your database, and they can slow performance when you update data in indexed fields, or when you add or delete data. They can also reduce the efficiency of multiuser applications. If you evaluate your performance needs, you can add or omit indexes appropriately.

An index specifies the order in which records are accessed from database tables in a tabletype Recordset object. For example, suppose that you have an index on the LastName field in the Employees table in the Northwind sample database. If you create a tabletype Recordset object, then set the Recordset object's Index property to the name of the new index, the records returned by the Recordset object will be ordered alphabetically by last name.

You create an index on one or more fields in the table. When you create an index with DAO, you must create the field or fields to be included in the index and append them to the Fields collection of the Index object, as shown in the following example.

Sub SeekRecord()
	Const conPath As String = _
		"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
	Dim dbs As Database, tdf As TableDef, idx As Index
	Dim fld As Field, fldLast As Field, fldFirst As Field
	Dim rst As Recordset

	' Return a reference to Northwind database.
	Set dbs = DBEngine(0).OpenDatabase(conPath)
	' Return a reference to Employees table.
	Set tdf = dbs.TableDefs("Employees")
	' Create new index on LastName and FirstName fields.
	Set idx = tdf.CreateIndex("FirstLastName")
	' Create fields in Fields collection of new index.
	Set fldLast = idx.CreateField("LastName", dbText)
	Set fldFirst = idx.CreateField("FirstName", dbText)
	' Append Field objects.
	idx.Fields.Append fldLast
	idx.Fields.Append fldFirst
	' Set Required property.
	idx.Required = True
	' Append new Index object.
	tdf.Indexes.Append idx
	' Open table-type recordset.
	Set rst = dbs.OpenRecordset("Employees")
	' Set Index property of Recordset object.
	rst.Index = idx.Name
	' Perform seek operation.
	rst.Seek "=", "King", "Robert"

	' Print values of all fields except Photo.
	For Each fld In rst.Fields
		If fld.Type <> dbLongBinary Then
			Debug.Print fld
		End If
	Next fld
End Sub

When you create an index, you can also impose certain restrictions on the data contained in the fields that are indexed. For example, if you want to designate a particular field in a table as the primary key, you can create an Index object and set its Primary and Unique properties to True. A primary key is a special type of index. Each value in the field designated as the primary key must be unique. A foreign key is also an index, although it doesn't require special property settings. Other indexes are neither primary nor foreign keys and serve only to speed up searching and sorting operations.

Note If you're designing a database in the Microsoft Access user interface, you can add new indexes, change or delete existing indexes, and set index properties in table Design view. To do so, click Indexes on the View menu.

For more information about indexes, search DAO Help for "Index object."

The QueryDef Object and the QueryDefs Collection

The QueryDef object represents a query in DAO. QueryDef objects can be saved with your database, or they can be temporary. The QueryDefs collection contains all QueryDef objects that are saved with your database and any temporary QueryDef objects that are currently open. The following table shows the relationship between the QueryDef object and the QueryDefs collection and other objects and collections in a Microsoft Jet workspace.

Object or collection Is contained by Contains

QueryDef object QueryDefs collection Fields collection
Parameters collection
Properties collection

QueryDefs collection Database object QueryDef objects

Creating Persistent Queries

A query that's saved with your database is called a persistent query. You can create persistent queries in Visual Basic by using DAO, or you can create them in the Microsoft Access user interface.

To create a persistent query with DAO, use the CreateQueryDef method of a Database object, as shown in the following example.

Const conPath As String = _
   "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
Dim dbs As Database, qdf As QueryDef, rst As Recordset
Dim strSQL As String

strSQL = "SELECT FirstName, LastName, HireDate FROM Employees " _
	& "WHERE Title = 'Sales Representative' ORDER BY HireDate;"
Set dbs = OpenDatabase(conPath)
Set qdf = dbs.CreateQueryDef("Sales Representatives", strSQL)
Set rst = qdf.OpenRecordset

You don't need to append a QueryDef object to the QueryDefs collection. If you specify a value for the name argument of the CreateQueryDef method in a Microsoft Jet workspace, DAO automatically appends the new QueryDef object to the QueryDefs collection of the Database object. If you specify a zerolength string ("") for the name argument, DAO creates a temporary QueryDef object.

Note In an ODBCDirect workspace, QueryDef objects are always temporary.

Creating Temporary Queries

You can create a temporary QueryDef object when you need to run an SQL statement but don't want to store a new QueryDef object in the database. A temporary QueryDef object is not appended to the database and exists until the variable that represents it goes out of scope.

The following example creates two temporary QueryDef objects to return data from the Microsoft SQL Server Pubs sample database. It first queries the table of titles in the Microsoft SQL Server Pubs sample database and returns the title and title identifier of the bestselling book. It then queries the table of authors and instructs the user to send a bonus check to each author based on his or her royalty share. The total bonus is $1,000 and each author should receive a percentage of that amount.

This example uses ODBC through Microsoft Jet. You can apply the same principles to create a temporary QueryDef object on a Microsoft Jet database or an installable ISAM data source, or in an ODBCDirect workspace.

Function DetermineBonuses()
	Const conPath As String = _
		"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
	Dim dbsCurrent As Database, qdfBestSellers As QueryDef
	Dim qdfBonusEarners As QueryDef, rstTopSeller As Recordset
	Dim rstBonusRecipients As Recordset, strAuthorList As String

	' Open database from which QueryDef objects can be created.
	Set dbsCurrent = OpenDatabase(conPath)

	' Create temporary QueryDef object to retrieve data from
	' Microsoft SQL Server database.
	Set qdfBestSellers = dbsCurrent.CreateQueryDef("")
	qdfBestSellers.Connect = "ODBC;DATABASE=Pubs;UID=sa;PWD=;DSN=Publishers"
	qdfBestSellers.SQL = "SELECT title, title_id FROM titles ORDER BY ytd_sales DESC;" 
	Set rstTopSeller = qdfBestSellers.OpenRecordset()
	rstTopSeller.MoveFirst
	' Create temporary QueryDef to retrieve data from SQL Server database

	' based on results from first query.
	Set qdfBonusEarners = dbsCurrent.CreateQueryDef("")
	qdfBonusEarners	.Connect = "ODBC;DATABASE=Pubs;UID=sa;PWD=;DSN=Publishers"
	qdfBonusEarners.SQL = "SELECT * FROM titleauthor WHERE title_id = '" & _
		rstTopSeller!title_id & "'"
	Set rstBonusRecipients = qdfBonusEarners.OpenRecordset()

	' Build string containing names of authors to whom bonuses are owed.
	Do While Not rstBonusRecipients.EOF
		strAuthorList = strAuthorList & rstBonusRecipients!au_id & ": $" & _
			CStr(10* rstBonusRecipients!royaltyper) & vbCr
		rstBonusRecipients.MoveNext
	Loop

	' Display results.
	MsgBox "Please send a check to the following " & _
		"authors in the amounts shown: " & vbCr & _
		strAuthorList & " for outstanding sales of " & _
		rstTopSeller!Title & "."

	rstBonusRecipients.Close
	rstTopSeller.Close
	dbsCurrent.Close
End Function

The Parameter Object and the Parameters Collection

A Parameter object represents a value supplied to a query. The Parameters collection contains all of the Parameter objects defined for a QueryDef object. The following table shows the relationship between the Parameter object and the Parameters collection and other objects and collections in a Microsoft Jet workspace.

Object or collection Is contained by Contains

Parameter object Parameters collection Properties collection

Parameters collection QueryDef object Parameter objects

When you want the user or the application to supply a value at run time that limits the set of records returned by a query, you can define parameters for the query. For example, you can create a query on an Orders table that prompts the user to specify the range of records to return based on a range of order dates.

To create a parameter query, use the SQL PARAMETERS declaration to define parameters for the query. The syntax for the PARAMETERS declaration is:

PARAMETERS name datatype [, name datatype [, ...]]

The PARAMETERS declaration precedes the rest of the SQL statement and is separated from the SQL statement by a semicolon (;). The following SQL statement defines two parameters, Beginning OrderDate and Ending OrderDate, whose datatype is DATETIME.

PARAMETERS [Beginning OrderDate] DATETIME,[Ending OrderDate] DATETIME; 
SELECT * FROM Orders 
WHERE (OrderDate Between [Beginning OrderDate] And [Ending OrderDate]);

For a list of data types you can use for parameters, search Microsoft Access Help for "data types, SQL."

Each parameter that you define in the SQL statement is represented by a Parameter object in the Parameters collection of the QueryDef object based on that SQL statement. You specify the value of a parameter by setting the Value property of the Parameter object. The following example creates a new parameter query.

Function NewParameterQuery(dteStart As Date, dteEnd As Date) As Boolean
	Dim dbs As Database, qdf As QueryDef, rst As Recordset
	Dim strSQL As String

	On Error Resume Next
	' Return reference to current database.
	Set dbs = CurrentDb
	' Construct SQL string.
	strSQL = "PARAMETERS [Beginning OrderDate] DateTime, " _
		& "[Ending OrderDate] DateTime; SELECT * FROM Orders " & _
		"WHERE (OrderDate Between [Beginning OrderDate] " _
		& "And [Ending OrderDate]);"

	' Delete query if it already exists.
	dbs.QueryDefs.Delete "ParameterQuery"

	On Error GoTo Err_NewParameterQuery
	' Create new QueryDef object.
	Set qdf = dbs.CreateQueryDef("ParameterQuery", strSQL)

	' Supply values for parameters.
	If dteStart > dteEnd Then
		MsgBox "Start date is later than end date."
		Exit Function
	End If
	qdf.Parameters("Beginning OrderDate") = dteStart
	qdf.Parameters("Ending OrderDate") = dteEnd

	' Open recordset on QueryDef object.
	Set rst = qdf.OpenRecordset
	rst.MoveLast
	MsgBox "Query returned " & rst.RecordCount & " records."
	NewParameterQuery = True

Exit_NewParameterQuery:
	rst.Close
	Set dbs = Nothing
	Exit Function

Err_NewParameterQuery:
	MsgBox "Error " & Err & ": " & Err.Description
	NewParameterQuery = False
	Resume Exit_NewParameterQuery
End Function

You can call this function from the Debug window as follows:

? NewParameterQuery(#6-30-95#, #6-30-96#)

Note If you're creating a database in Microsoft Access, you can define parameters for a query in query Design view. For more information, search Microsoft Access Help for "parameter queries."

The Relation Object and the Relations Collection

The Relation object represents a relationship between fields in tables and queries. The Relations collection contains all stored Relation objects in a database. The following table shows the relationship between the Relation object and the Relations collection and other objects and collections in a Microsoft Jet workspace.

Object or collection Is contained by Contains

Relation object Relations collection Fields collection
Properties collection

Relations collection Database object Relation objects

You can use the Relation object to create, delete, or change relationships between fields in tables and queries in your database. You can use the properties of the Relation object to specify the type of relationship, which tables supply the fields that participate in the relationship, whether to enforce referential integrity, and whether to perform cascading updates and deletes.

A Relation object has a Fields collection that contains two fields, one in each of the tables in the relationship. The fields that make up the relationship must be of the same data type, and they must have common values. In most cases, a relationship consists of a field that is the primary key in one table and a foreign key in another table.

You use the Table and ForeignTable properties of the Relation object to specify which tables take part in the relation and how they are related. If you are creating a onetomany relationship, it is important that you set these properties correctly. In a onetomany relationship, the table on the "one" side of the relationship is the table in which the field to be joined is the primary key. The setting for the Table property must be the name of this table. The table on the "many" side of the relationship is the table in which the field to be joined is the foreign key. The setting for the ForeignTable property must be the name of this table.

For example, consider the relationship between the Employees table and the Orders table in the Northwind sample database. The two tables are joined on the EmployeeID field. In the Employees table, this field is the primary key; all values in this field must be unique. In the Orders table, the EmployeeID field is a foreign key. The same value can occur more than once in this field. For the Relation object that represents this relationship, the value of the Table property is the table on the "one" side of the relationship; the Employees table. The value of the ForeignTable property is the table on the "many" side of the relationship; the Orders table.

The following example shows how to create a Relation object in Visual Basic. The procedure deletes the existing relationship between the Employees table and the Orders table in the Northwind sample database, then recreates it.

Function NewRelation() As Boolean
	Dim dbs As Database
	Dim fld As Field, rel As Relation
	Const conPath As String = _
		"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"

	On Error GoTo Err_NewRelation
	' Return reference to current database.
	Set dbs = OpenDatabase(conPath)
		' Find existing EmployeesOrders relation.
	For Each rel In dbs.Relations
		If rel.Table = "Employees" And rel.ForeignTable = "Orders" Then
			' Prompt user before deleting relation.
			If MsgBox(rel.Name & " already exists. " & vbCrLf _
				& "This relation will be deleted and re-created.", vbOK) = vbOK Then
				dbs.Relations.Delete rel.Name
			' If user chooses Cancel, exit procedure.
			Else
				Exit Function
			End If
		End If
	Next rel 

	' Create new relationship and set its properties.
	Set rel = dbs.CreateRelation("EmployeesOrders", "Employees", "Orders")
	' Set Relation object attributes to enforce referential integrity.
	rel.Attributes = dbRelationDeleteCascade + dbRelationUpdateCascade
	' Create field in Fields collection of Relation object.
	Set fld = rel.CreateField("EmployeeID")
	' Provide name of foreign key field.
	fld.ForeignName = "EmployeeID"

	' Append field to Relation object and Relation object to database.
	rel.Fields.Append fld
	dbs.Relations.Append rel
	MsgBox "Relation '" & rel.Name & "' created."
	Set dbs = Nothing
	NewRelation = True

Exit_NewRelation:
	Exit Function

Err_NewRelation:
	MsgBox "Error " & Err & ": " & Err.Description
	NewRelation = False
	Resume Exit_NewRelation
End Function

Note If you're designing a database in Microsoft Access, you can view and change the relationships in your database in the Relationships window. In the Database window, click Relationships on the Tools menu.

For more information about Relation objects, search DAO Help for "Relation object."

The Recordset Object and the Recordsets Collection

The Recordset object represents a set of records within your database. The Recordsets collection contains all open Recordset objects. The following table shows the relationship between the Recordset object and the Recordsets collection and other objects and collections in a Microsoft Jet workspace.

Object or collection Is contained by Contains

Recordset object Recordsets collection Fields collection
Properties collection

Recordsets collection Database object Recordset objects

DAO offers five types of Recordset objects: tabletype, dynasettype, snapshottype, forwardonlytype, and dynamictype. Tabletype Recordset objects are supported only in Microsoft Jet workspaces. Dynamictype Recordset objects are available only in ODBCDirect workspaces. For more information, see "DynamicType Recordset Objects" later in the chapter.

The sections that follow discuss some characteristics of each of the other four types of Recordset objects. For more information about each type of Recordset object, search DAO Help for the name of the particular type of Recordset object.

Note that you should always close a Recordset object after you have finished working with it, and before you close the Database object in which the recordset was created. Use the Close method to close a Recordset object.

TableType Recordset Objects

The tabletype Recordset object represents a base table in your database. All of the fields and records in the table are included in a tabletype Recordset object. You can use a tabletype Recordset object to add, delete, or change records in a table in a Microsoft Jet workspace. You can open a tabletype Recordset object on base tables in a Microsoft Jet database, but not on tables in ODBC data sources or linked tables. You can also use the tabletype Recordset object with installable ISAM databases (such as FoxPro, dBASE, or Paradox) to open tables directly, rather than linking them to your database.

The RecordCount property of a tabletype Recordset object returns the number of records in the table. You can return the value of the RecordCount property as soon as you've created the recordset; you don't need to use the MoveLast method to move to the end of the recordset.

The tabletype Recordset object can use the indexes defined for the table. When you create a tabletype Recordset object, you can set the recordset's Index property to the name of an index that is defined for the table. You can then use the Seek method to search for a particular record based on the ordering criteria specified by the index.

Note You can't open a tabletype Recordset object on a linked table from an external data source. Instead, you must use the OpenDatabase method to open the external data source, and then open a tabletype Recordset object.

To create a tabletype Recordset object, specify the dbOpenTable constant for the type argument of the OpenRecordset method. The following example creates a tabletype Recordset object and then uses the Seek method to locate a particular record and make that record the current record.

Function ReturnEmployeesRecord(strKey As String) As Boolean
	Dim dbs As Database, rst As Recordset
	Const conPath As String = _
		"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"

	On Error GoTo Err_ReturnEmployeesRecord
	' Return reference to Northwind database.
	Set dbs = OpenDatabase(conPath)
	' Open table-type recordset on Employees table.
	Set rst = dbs.OpenRecordset("Employees", dbOpenTable)
	' Set Index property of recordset.
	rst.Index = "LastName"
	' Perform seek operation.
	rst.Seek "=", strKey
	' Check whether match is found.
	If rst.NoMatch = False Then
		' Print values of fields in first record found.
		Debug.Print rst!EmployeeID, rst!FirstName & " " & rst!LastName, rst!Title
		ReturnEmployeesRecord = True
	Else
		ReturnEmployeesRecord = False
	End If

Exit_ReturnEmployeesRecord:
	' Close recordset and database. 
	rst.Close
	dbs.Close
	Exit Function

Err_ReturnEmployeesRecord:
	MsgBox "Error " & Err & ": " & Err.Description
	ReturnEmployeesRecord = False
	Resume Exit_ReturnEmployeesRecord
End Function

DynasetType Recordset Objects

The dynasettype Recordset object represents the result of a query on one or more tables. A dynasettype Recordset object is a dynamic set of records that you can use to add, change, or delete records from an underlying database table or tables. With a dynasettype Recordset object, you can extract and update data in a multipletable join, including linked tables from multiple databases. You can create a dynasettype Recordset object in a Microsoft Jet workspace or an ODBCDirect workspace. A dynasettype Recordset object on a remote data source consists of a series of bookmarks. Each bookmark uniquely identifies one record in the recordset. The actual data in the fields of the recordset is not returned until you specifically refer to the record that contains that data. Microsoft DAO uses the bookmark to find the appropriate record and return the requested data. To improve performance, Microsoft DAO returns only the records that you explicitly refer to in your code; it doesn't necessarily return data from every record in the recordset.

In order to return the value of the RecordCount property for a dynasettype Recordset object, you must first use the MoveLast method to move to the end of the recordset. Moving to the end of the recordset retrieves all of the records in the recordset.

A dynasettype Recordset object may be updatable, but not all fields can be updated in all dynasettype Recordset objects. To determine whether you can update a particular field, check the setting of the DataUpdatable property of the Field object.

A dynasettype Recordset object may not be updatable if:

The data page the user is trying to update is locked by another user.

The record has changed since it was last read.

The user doesn't have permission to update the recordset.

One or more of the tables or fields are readonly.

The database is opened for readonly access.

The Recordset object was created from multiple tables without a JOIN statement.

The Recordset object includes fields from an ODBC data source, or Paradox table or tables, and there isn't a unique index on those table or tables.

To create a dynasettype Recordset object, specify the dbOpenDynaset constant for the type argument of the OpenRecordset method, as shown in the following example.

Sub PrintHireDates()
	Dim dbs As Database, rst As Recordset
	Dim strSQL As String
	Const conPath = "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"

	' Open database and return reference to Database object.
	Set dbs = DBEngine.Workspaces(0).OpenDatabase(conPath)
	' Initialize SQL string.
	strSQL = "SELECT FirstName, LastName, HireDate FROM Employees " & _
		"WHERE HireDate <= #1-1-93# ORDER BY HireDate;"
	' Open dynaset-type recordset.
	Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)
	' Print records in recordset.
	Do Until rst.EOF
		Debug.Print rst!FirstName, rst!LastName, rst!HireDate
		rst.MoveNext
	Loop
	' Close recordset and database.
	rst.Close
	dbs.Close
End Sub

SnapshotType Recordset Objects

A snapshottype Recordset object is a static set of records that represents the results of a query. A snapshottype Recordset object includes all values for all the requested fields in your query, whether you refer to them in code or not. A snapshottype Recordset object requires fewer resources than the dynasettype Recordset object, but the data in a snapshottype Recordset object cannot be updated.

As you move through a snapshottype Recordset object for the first time, all data is copied first into memory and then, if the recordset is large, into a temporary Microsoft Jet database on the user's computer. You can scroll forward and backward through the resulting set of data.

To create a snapshottype Recordset object, specify the dbOpenSnapshot constant for the type argument of the OpenRecordset method.

ForwardOnlyType Recordset Objects

A forwardonlytype Recordset object is identical to a snapshot, except that you can only scroll forward through its records. This improves performance in situations where you only need to make a single pass through a result set.

When working with a forwardonlytype Recordset object, you cannot use the MovePrevious or MoveFirst methods, or the Move method with a negative integer for the rows argument. In a forwardonlytype Recordset object, only one record exists at any given time. Therefore, you cannot use the MoveLast method because it implies that you have a set of records. Forwardonlytype Recordset objects offer less flexibility than other Recordset objects, but they usually provide the greatest speed.

To create a forwardonlytype Recordset object, specify the dbOpenForwardOnly constant for the type argument of the OpenRecordset method.

The Group Object and the Groups Collection

The Group object represents a group of user accounts that have common access permissions in a particular workspace. The Groups collection contains all Group objects in a workspace or a user account. The following table shows the relationship between the Group object and the Groups collection and other objects and collections in a Microsoft Jet workspace.

Object or collection Is contained by Contains

Group object Groups collection Group objects
Properties collection
Users collection

Groups collection Workspace object
User object
Group objects

You can use the Group object, along with the User, Container, Document, and Workspace objects, to secure your database. The Group object represents a group of user accounts, and the User object represents an individual user account. Users can be members of groups. When you establish security in your database, you secure a particular object or set of objects by specifying what type of permissions a user or group has for that object. If a group has certain permissions for an object, all users in the group have the same permissions. Conversely, if a user has permissions for an object, the group to which that user belongs has the same permissions.

Note The easiest way to secure your database is through the Microsoft Access user interface. From Microsoft Access, you can manage user and group accounts and assign permissions for objects with relative ease. For more information about securing a database in Microsoft Access, search Microsoft Access Help for "security," or see Chapter 14, "Securing Your Application," in Building Applications with Microsoft Access 97.

Both a Workspace object and a User object have a Groups collection. When you create a Group object, you should first append it to the Groups collection of a Workspace object. This notifies Microsoft Jet that the group exists.

After you've created a group and added it to the Groups collection of the Workspace object, you need to specify which users belong to that group. To do so, you can append the new Group object to the Groups collection of a User object. In this way, you specify that a particular user belongs to this group. Alternatively, you can append a User object to the Users collection in a Group object to give a particular user account the permissions held by that group. In either case, the existing Group object must already be a member of the Groups collection of the current Workspace object.

The following example creates a new group, the Managers group, and appends it to the Groups collection of the default workspace.

Function AddNewGroup() As Boolean
	Dim wrk As Workspace, grp As Group

	Const conAccountExists As Integer = 3390

	On Error GoTo Err_AddNewGroup
	Set wrk = DBEngine.Workspaces(0)
	Set grp = wrk.CreateGroup("Managers", "123abc")
	wrk.Groups.Append grp
	AddNewGroup = True

Exit_AddNewGroup:
	Exit Function

Err_AddNewGroup:
	If Err <> conAccountExists Then
		MsgBox "Error " & Err & ": " & Err.Description
		AddNewGroup = False
	Else
		AddNewGroup = True
	End If
	Resume Exit_AddNewGroup
End Function

After you've run this example, the Managers group exists, but no user accounts belong to it. The example in the following section adds user accounts to the Managers group.

The User Object and the Users Collection

The User object represents a user account with particular access permissions. The Users collection contains all User objects in a given workspace or group. The following table shows the relationship between the User object and the Users collection and other objects and collections in a Microsoft Jet workspace.

Object or collection Is contained by Contains

User object Users collection Groups collection
Properties collection
User objects

Users collection Workspace object
Group object
User objects

Like the Groups collection, the Users collection is a member of a Workspace object. Each User object in the Users collection of a Workspace object also has a Groups collection, in the same way that each Group object in the Groups collection of a Workspace object has a Users collection. To make a user a member of a particular group, you can append a User object to the Users collection of that Group object. You can achieve the same result by appending the Group object to the Groups collection of that User object. In either case, the existing User object must already be a member of the Users collection of the current Workspace object.

The following example creates a new User object and appends it to the Users collection of the default workspace. Next, it appends the User object to the Users collection of the Managers group created in the previous example. Note that because the User object doesn't already exist in the Users collection of the Group object, you must use the CreateUser method a second time to create the object there. However, you don't need to specify the pid and password arguments a second time.

Function AddNewUser() As Boolean
	Dim wrk As Workspace, grp As Group, usr As User

	Const conAccountExists As Integer = 3390

	On Error GoTo Err_AddNewUser
	Set wrk = DBEngine.Workspaces(0)
	Set usr = wrk.CreateUser("Joe Manager", "efg456", "")
	wrk.Users.Append usr
	Set grp = wrk.Groups("Managers")
	Set usr = grp.CreateUser("Joe Manager")
	grp.Users.Append usr
	AddNewUser = True

Exit_AddNewUser:
	Exit Function

Err_AddNewUser:
	If Err <> conAccountExists Then
		MsgBox "Error " & Err & ": " & Err.Description
		AddNewUser = False
	Else
		AddNewUser = True
	End If
	Resume Exit_AddNewUser
End Function

The Container Object and the Containers Collection

The Container object represents a particular set of objects in a database for which you can assign permissions in a secure workgroup. The Containers collection contains all the Container objects in the database. The following table shows the relationship between the Container object and the Containers collection and other objects and collections in a Microsoft Jet workspace.

Object or collection Is contained by Contains

Container object Containers collection Documents collection
Properties collection

Containers collection Database object Container objects

DAO provides three types of Container objects; every database contains at least these three Container objects. The following table describes the types of Container objects provided by DAO.

Container name Contains information about

Databases Saved databases

Tables Saved tables and queries

Relationships Saved relationships

Each Container object can contain a Documents collection. The Documents collection contains individual Document objects, each of which represents a document in your database. For more information about Document objects, see the following section, "The Document Object and the Documents Collection."

In addition to the Container objects provided by DAO, an application may define its own Container objects. For example, the following table lists the Container objects defined by Microsoft Access.

Container name Contains information about

Forms Saved forms

Modules Saved modules

Reports Saved reports

Scripts Saved macros

You use Container objects to establish permissions on a set of objects for a user or group. The following example establishes permissions for a group, and any users that belong to it, for the Tables container. To establish permissions, the function first sets the UserName property of the Tables container to the name of a group, then sets the Permissions property to the appropriate permissions.

Function SetGroupPermissions(strGroupName As String) As Boolean
	Dim dbs As Database, ctr As Container

	Const conPath As String = _
		"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"

	On Error GoTo Err_SetGroupPermissions
	Set dbs = DBEngine(0).OpenDatabase(conPath)
	' Return a reference to the Databases container.
	Set ctr = dbs.Containers("Databases")
	' Set UserName property to name of group.
	ctr.UserName = strGroupName
	' Set permissions for the group on the Databases container.
	ctr.Permissions = dbSecDBOpen

	' Return a reference to the Tables container.
	Set ctr = dbs.Containers("Tables")
	' Set UserName property to name of group.
	ctr.UserName = strGroupName
	' Set permissions for the group on the Tables container.
	ctr.Permissions = dbSecRetrieveData or dbSecInsertData or _
		dbSecReplaceData or dbSecDeleteData
	SetGroupPermissions = True

Exit_SetGroupPermissions:
	Exit Function

Err_SetGroupPermissions:
	MsgBox "Error " & Err & ": " & Err.Description
	SetGroupPermissions = False
	Resume Exit_SetGroupPermissions
End Function

To establish permissions for the Managers group on the Tables container, you can call the SetGroupPermissions function as follows.

Sub SetManagerPermissions()
	If SetGroupPermissions("Managers") = True Then
		MsgBox "Permissions for Managers group set successfully."
	Else
		MsgBox "Permissions for Managers group not set."
	End If
End Sub

The Document Object and the Documents Collection

The Document object represents an individual object in a database for which you can assign permissions in a secure workgroup. The Documents collection contains all of the Document objects in a given Container object. The following table shows the relationship between the Container object and the Containers collection and other objects and collections in a Microsoft Jet workspace.

Object or collection Is contained by Contains

Document object Documents collection Properties collection

Documents collection Container object Document objects

The following table describes the Document objects provided by DAO. It lists the type of object each Document object describes, the name of its Container object, and what type of information it contains.

Document Container Contains information about

Database Databases Saved database

Table or query Tables Saved table or query

Relationship Relationships Saved relationship

Other applications can define additional Document objects. For example, the following table lists the Document objects defined by Microsoft Access.

Document Container Contains information about

Form Forms Saved form

Macro Scripts Saved macro

Module Modules Saved module

Report Reports Saved report

SummaryInfo Databases Database document summary

UserDefined Databases User-defined properties

The following example establishes permissions for a particular user on all the existing Table Document objects in the Documents collection of the Tables Container object. Table Document objects represent either tables or queries.

Function SetPermissionsOnDocument(strUserName As String) As Boolean
	Dim dbs As Database, ctr As Container, doc As Document 

	Const conPath As String = _
		"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"

	On Error GoTo Err_SetPermissionsOnDocument
	' Return reference to Northwind sample database.
	Set dbs = DBEngine(0).OpenDatabase(conPath)
	' Return reference to Tables container.
	Set ctr = dbs.Containers("Tables")
	' Enumerate through documents in Tables container.
	For Each doc In ctr.Documents
		' Set UserName property to name of user.
		doc.UserName = strUserName
		' Set permissions for that user on the document.
		doc.Permissions = dbSecRetrieveData or dbSecInsertData or _
			dbSecReplaceData or dbSecDeleteData
	Next doc
	SetPermissionsOnDocument = True

Exit_SetPermissionsOnDocument:
	Exit Function

Err_SetPermissionsOnDocument:
	MsgBox "Error " & Err & ": " & Err.Description
	SetPermissionsOnDocument = False
	Resume Exit_SetPermissionsOnDocument
End Function

The Properties Collection

Most DAO objects contain a Properties collection. Each Property object in the Properties collection corresponds to a property of the object. You can use an object's Properties collection either to determine which properties apply to a particular object or to return their settings. For example, the following procedure loops through the properties that apply to the Database object, which represents the current database. The procedure displays the name of each property in the Debug window.

Sub DisplayProperties()
	Dim dbs As Database, prp As Property

	Const conPath As String = _
		"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"

	' Open database and return reference.
	Set dbs = OpenDatabase(conPath)
	Debug.Print "Current Database Properties"
	' Enumerate Properties collection.
	For Each prp In dbs.Properties
		Debug.Print prp.Name
	Next prp
	dbs.Close
End Sub

Some properties of DAO objects don't automatically exist in the Properties collection for that object. Before you can set a property of this type, you must create a Property object to represent the property and append the new Property object to the Properties collection. After you create the property and append it to the collection, you can set or read it as you would any other property.

When you're writing code that uses this type of property, it's a good idea to implement error handling in case the property does not yet exist in the collection. The following function is a generic procedure that you can use to set any property that doesn't automatically exist in an object's Properties collection. It implements error handling. The first time you call the procedure, an error occurs because the property does not yet exist within the Properties collection. Within the error handler, the procedure creates the new Property object and appends it to the collection. The next time you call the procedure, the error does not occur because the property already exists, and the property is set with the value you've specified.

Function SetProperty(obj As Object, strName As String, _
		intType As Integer, varSetting As Variant) As Boolean
	Dim prp As Property

	Const conPropNotFound As Integer = 3270

	On Error GoTo Error_SetProperty
	' Explicitly refer to Properties collection.
	obj.Properties(strName) = varSetting
	SetProperty = True

Exit_SetProperty:
	Exit Function

Error_SetProperty:
	If Err = conPropNotFound Then
		' Create property, denote type, and set initial value.
		Set prp = obj.CreateProperty(strName, intType, varSetting)
		' Append Property object to Properties collection.
		obj.Properties.Append prp
		obj.Properties.Refresh
		SetProperty = True
		Resume Exit_SetProperty
	Else
		MsgBox Err & ": " & vbCrLf & Err.Description
		SetProperty = False
		Resume Exit_SetProperty
	End If
End Function

To set the ReplicableBool property of a Database object, you can call the preceding function as follows.

Sub ReplicateDatabase()
	Dim dbs As Database

	Const conPath As String = _
		"C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"

	Set dbs = OpenDatabase(conPath, True)
	If SetProperty(dbs, "ReplicableBool", dbBoolean, True) Then
		Debug.Print "Database replicated successfully."
	Else
		Debug.Print "Database not replicated."
	End If
End Sub

The SetProperty function shown in the previous example is a generic procedure that you can use to set any property, including those that must first be appended to the Properties collection. You can compare this function to the ReplicateDatabase function shown earlier in this chapter, in "Creating Database Replicas with DAO." Both functions achieve the same end, but the SetProperty function can be used to set any property, while the ReplicateDatabase function sets only the ReplicableBool property.

Each time you set or read a property that doesn't automatically exist in the Properties collection for an object, you must refer to the Properties collection explicitly. For example, each time you refer to the ReplicableBool property after it has been set, you must refer to it within the Properties collection, as shown in the following example.

Dim dbs As Database
Const conPath As String = _
   "C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb"
Set dbs = OpenDatabase(conPath)
Debug.Print dbs.Properties("ReplicableBool")

You can also use the SetProperty function shown in the previous example to define custom properties on DAO objects. For example, you may want to define a property that stores the name of the user who last modified a particular table. When you set or read a custom property, you must refer to the Properties collection explicitly, as shown in the previous examples.

Some applications define their own properties for DAO objects. For example, Microsoft Access defines properties for DAO TableDef, QueryDef, Field, and Document objects. If you're working with a database that has been opened in Microsoft Access, some of these properties may be defined for DAO objects.

For more information about the Properties collection, search DAO Help for "properties, collection" and "CreateProperty method."

Accessing ODBC Data

When you're working with an ODBC data source, you'll need to decide whether you should use ODBC with Microsoft Jet, ODBCDirect, or both. This section discusses the advantages of both ODBC with Microsoft Jet and ODBCDirect. It also explains how to register an ODBC data source, whether you're working with a Microsoft Jet workspace or with an ODBCDirect workspace.

Accessing ODBC Data with Microsoft Jet

The following capabilities are supported in Microsoft Jet workspaces, but not in ODBCDirect workspaces:

Updatable Joins You can update data in Recordset objects based on multipletable joins.

Support for Linked Tables You can store persistent links to server data in a local Microsoft Jet database. When you link a table, you can cache information about the table's structure, including field and index information, in your local database. The next time you access that table, the connection is quicker because you don't need to retrieve the structural information from the data source again.

Support for the Find Methods You can use the FindFirst, FindNext, FindPrevious, and FindLast methods with Recordset objects in a Microsoft Jet workspace.

Partial Failures of Update Queries If you have a bulkoperation query, and it fails for some reason, the query stops, giving you the opportunity to decide whether or not you want to commit the changes made up to the point of failure.

UserDefined Properties You can customize DAO objects by adding persistent properties to existing objects. For example, you can add a Description property to an object so that you can store descriptive text about the object.

Crosstab Queries You can use the SQL TRANSFORM statement to create crosstab queries that summarize data.

Heterogeneous Data Access You can work with server data, native Microsoft Jet database (.mdb file) data, and external installable ISAM data such as FoxPro, Paradox, and dBASE data. You can perform joins on tables in different data sources.

Programmatic Data Definition Language (DDL) You can use DAO to perform operations that affect the structure of your database. For example, you can create, delete, and modify tables.

Form and Control Binding If your application requires that forms or controls be bound to data in an ODBC data source, you must use Microsoft Jet. Data accessed within an ODBCDirect workspace cannot be bound to forms or controls because ODBCDirect does not support linked tables.

Accessing ODBC Data with ODBCDirect

With ODBCDirect, you can access server data by using the existing DAO object model directly on top of the ODBC application programming interface (API). ODBCDirect implements a thin code layer over the ODBC API that establishes connections, creates cursors, and runs complex procedures using minimal workstation resources, without going through Microsoft Jet. ODBCDirect offers the following advantages:

Direct Access Your application can access ODBC data sources directly. You can improve performance, reduce network traffic, and take advantage of the server's capabilities by processing more data on the server.

Reduced Resource Requirements You don't have to go through the Microsoft Jet database engine, so your application requires fewer resources at the workstation. If you're using ODBCDirect from Microsoft Access, keep in mind that Microsoft Access always loads Microsoft Jet, even though ODBCDirect operations don't go through Microsoft Jet.

Improved Access to ServerSpecific Functionality You can take advantage of features specific to the ODBC server that aren't available if you're using ODBC through Microsoft Jet. For example, in an ODBCDirect workspace, you can specify where cursors are located — on the client or on the server — for servers that support different types of cursors. In addition, to interact with stored procedures on the server, you can specify input values and check return values; operations that are not possible in a Microsoft Jet workspace.

Asynchronous Queries You can run a query and perform other operations without waiting for the query to finish. You can then check properties to keep track of the query's progress. You can enhance concurrency and optimize performance with asynchronous queries.

Batch Optimistic Updating With batch optimistic updating, you can cache Recordset changes locally and then submit these changes to the server in a single batch.

Flexible Stored Procedure Execution You can handle output parameters and return values from stored procedures.

Note You can't perform DDL operations with DAO in an ODBCDirect workspace, but you can run SQL DDL statements to modify the structure of the database.

Registering an ODBC Data Source

Before you can use ODBC in a Microsoft Jet workspace or in an ODBCDirect workspace, you must register the ODBC data source. Registering the data source stores information about the data source in the Windows Registry and makes this information available to applications. You can register a data source from the ODBC data source manager or from Visual Basic.

To register a SQL Server data source by using the ODBC data source manager

In Windows Control Panel, doubleclick the 32bit ODBC icon.

Click Add and then doubleclick the ODBC driver for the data source you want to access. For example, doubleclick SQL Server.

In the Data Source Name box, type a data source name (DSN). This can be any string, such as SalesDB or Pubs. The string doesn't have to correspond to the actual name of a database or table you want to access.

In the Description box, type a description of the database, such as Sales Data for 1996. You can enter any text.

In the Server box, type the name of the network server where your data source resides. Do not include a doublebackslash (\\) before the name.

Click Options, and then type the name of the database you want to access in the Database Name box. For example, to specify the Microsoft SQL Server Pubs sample database, type Pubs.

Note This procedure describes the steps for registering a Microsoft SQL Server data source. The steps for registering other ODBC data sources may vary because each data source driver requires a different set of information. If the dialog box for the data source you selected has values not described in the preceding steps, click the Help button for more information.

In some cases, you may want to register the data source in Visual Basic code instead of relying on users to register it with the ODBC data source manager. To do this, use the RegisterDatabase method of the DBEngine object. The following example registers a data source named Pubs.

Function RegisterDB() As Boolean
	Dim str As String

	On Error GoTo Err_RegisterDB
	' Build keywords string.
	str = "Description=SQL Server on Server Publishers" & _
		vbCr & "OemToAnsi=No" & _
		vbCr & "Network=(Default)" & _
		vbCr & "Address=(Default)" & _
		vbCr & "Server=Publishers" & _
		vbCr & "Database=Pubs"
	' Register database.
	DBEngine.RegisterDatabase "Pubs", "SQL Server", True, str
	RegisterDB = True

Exit_RegisterDB:
	Exit Function

Err_RegisterDB:
	MsgBox "Error " & Err & ": " & Err.Description
	RegisterDB = False
	Resume Exit_RegisterDB
End Function

Using DAO with ODBCDirect

The object model for an ODBCDirect workspace includes a subset of the objects in a Microsoft Jet workspace, with the addition of a new object, the Connection object. The following diagram shows the object model for ODBCDirect workspaces; the subsequent sections describe the objects themselves, to the extent that they differ from the objects in the Microsoft Jet object model.

The DBEngine Object

The DBEngine object contains both Microsoft Jet and ODBCDirect workspaces. As mentioned earlier in this chapter, the DefaultType property of the DBEngine object determines what type of Workspace object is created by default when you use the CreateWorkspace method. If you set the DefaultType property to dbUseODBC, then the default workspace is an ODBCDirect workspace. When you're creating a workspace, you can override the setting for this property by specifying either dbUseJet or dbUseODBC as the type argument of the CreateWorkspace method. For example, if the DefaultType property is set to dbUseJet and you want to create an ODBCDirect workspace, specify the dbUseODBC constant as the type argument of the CreateWorkspace method. Conversely, if the DefaultType property is set to dbUseODBC and you want to create a Microsoft Jet workspace, specify the dbUseJet constant as the type argument of the CreateWorkspace method.

Note If you're programming in Microsoft Access, avoid setting the DefaultType property to dbUseODBC. Because Microsoft Access uses DAO and Microsoft Jet for many types of operations, setting the DefaultType property to dbUseODBC may cause unexpected results.

The following example creates an ODBCDirect workspace.

Dim wrkODBC As Workspace
Set wrkODBC = DBEngine.CreateWorkspace("NewODBCWrk", "Admin", "", dbUseODBC)

Because you can use both Microsoft Jet and ODBCDirect workspaces in your code, you may need to determine the type of a Workspace object after it is created. You can do this by using the Type property of the Workspace object. The Type property is readonly once it is set and returns either dbUseJet or dbUseODBC.

The Workspace Object and the Workspaces Collection

The Workspace object represents an ODBCDirect workspace. The Workspaces collection contains the set of all active ODBCDirect workspaces. The following table shows the relationship between the Workspace object and the Workspaces collection and other objects and collections in an ODBCDirect workspace.

Object or collection Is contained by Contains

Workspace object Workspaces collection Connections collection
Databases collection
Properties collection

Workspaces collection DBEngine object Workspace objects

The first step in using ODBCDirect is to create an ODBCDirect workspace with the CreateWorkspace method. The ODBCDirect workspace routes calls directly to the ODBC application programming interface (API), as opposed to the Microsoft Jet workspace, which first routes calls to the Microsoft Jet database engine, and then to the ODBC API if you're using ODBC.

The Connection Object and the Connections Collection

After you've created an ODBCDirect workspace, you can connect to an ODBC data source. To connect to an ODBC data source, you can use the OpenConnection method to open a new Connection object, or you can use the OpenDatabase method to open a new Database object. This section explains how to use the Connection object. For information on how to use a Database object, see the following section, "The Database Object and the Databases Collection."

A Connection object represents a connection to an ODBC database in an ODBCDirect workspace. The Connections collection contains all currently open Connection objects. When you open a Connection object, it is automatically appended to the Connections collection of the Workspace object. When you close a Connection object with the Close method, it is removed from the Connections collection.

The Connection object provides the following advantages for accessing ODBC data:

Asynchronous Connection Your application can connect to an ODBC data source asynchronously. Rather than pausing execution while the connection is established, your code can continue to perform other operations, and can later check to determine whether the connection was made successfully.

Asynchronous Queries Your application can run queries against your ODBC data source asynchronously. Rather than pausing execution while a long query runs, your code can perform other tasks, and then check later to determine whether the query has run successfully.

QueryDef Objects You can define QueryDef objects that represent queries in the ODBC data source.

You can use the OpenConnection method to create a Connection object. The syntax of the OpenConnection method is:

Set connection = workspace.OpenConnection (name, options, readonly, connect)

The connection argument is the name of the new Connection object. The workspace argument is the name of an ODBCDirect Workspace object from which you're creating the new Connection object.

The name argument indicates the name of the registered data source. You can reference the new Connection object by using either the data source name (DSN) or the Connection object's ordinal position within its collection. The options argument determines if and when to prompt the user to establish the connection, and whether or not to open the connection asynchronously. The readonly argument controls the updatability of the data accessed through the connection. Set this argument to True to prevent updates; set it to False to allow updates.

The connect argument is a valid connect string that supplies parameters to the ODBC driver manager. These parameters can include user name, password, default database, and data source name (DSN), which overrides the value provided in the name argument.

The connect string must start with "ODBC;", and must contain a series of values needed by the driver to access the data. The actual connect string can vary depending on the data source you're trying to access; different ODBC data sources require different parameters in the connect argument. Usually, the minimum requirement is a user ID, a password, and a DSN, as shown in the following example:

ODBC;UID=JamesK;PWD=OpenSesame;DSN=MasterData

When the ODBC driver processes the connect string and one or more of the parameters required by the data source is missing, the driver displays a dialog box that asks for the information. If you don't want this dialog box displayed, you must make sure that the connect string has all the required information.

Note If you are trying to connect to a Microsoft SQL Server database that uses integrated security, omit the user ID (UID) and password (PWD) values because your Windows NT® user name and password are automatically used. For example, the connect string may look something like the following:

ODBC;UID=;PWD=;DATABASE=Pubs;DSN=Pubs

For more information about parameters that can be included in a connect string, search DAO Help for "Connect property." The following example illustrates how to use the OpenConnection method to open a new Connection object.

Function OpenPubsConnection() As Boolean
	Dim wrk As Workspace, cnn As Connection, rst As Recordset, fld As Field
	Dim strConnect As String, strSQL As String

	On Error GoTo Err_OpenPubsConnection
	' Create connnect string.
	strConnect = "ODBC;DSN=Pubs;UID=SA;PWD=;DATABASE=Pubs"
	' Create SQL string.
	strSQL = "SELECT * FROM Authors WHERE State = 'MD';"

	' Create ODBCDirect workspace.
	Set wrk = DBEngine.CreateWorkspace("NewODBCDirect", "sa", "", dbUseODBC)
	' Open connection.
	Set cnn = wrk.OpenConnection("Pubs", dbDriverNoPrompt, False, strConnect)
	' Open recordset on connection.
	Set rst = cnn.OpenRecordset(strSQL, dbOpenDynaset)
	' Print values in recordset.
	Do Until rst.EOF
		For Each fld In rst.Fields
			Debug.Print fld.Name, fld.Value
		Next fld
		Debug.Print
		rst.MoveNext
	Loop
	OpenPubsConnection = True

Exit_OpenPubsConnection:
	rst.Close
	cnn.Close
	Exit Function

Err_OpenPubsConnection:
	MsgBox "Error " & Err & ": " & Err.Description
	OpenPubsConnection = False
	Resume Exit_OpenPubsConnection
End Function

After you've created a Connection object, you can open Recordset objects and run queries on the Connection object.

When you open a Connection object, a corresponding Database object is created and appended to the Databases collection in the same workspace. When you open a database in an ODBCDirect workspace, a Connection object is likewise created and appended to the Connections collection. When you close either the Connection object or the Database object, the corresponding object is also closed.

Note Before you close a Connection object, close all open Recordset objects within it.

Opening Connections Asynchronously

In some cases, opening connections to data sources can take a long time, making it necessary for users to wait until the connection completes or an error occurs. To reduce the amount of time users must wait, you can open a connection asynchronously. This means that your application can complete other tasks while the connection is being established. To open a connection asynchronously, specify the dbRunAsync constant for the options argument of the OpenConnection method, as shown in the following example.

Dim wrk As Workspace, cnn As Connection, strConnect As String

Set wrk = DBEngine.CreateWorkspace("NewODBCDirect", "sa", "", dbUseODBC)
strConnect = "ODBC;DSN=Pubs;UID=SA;PWD=;DATABASE=Pubs"
Set cnn = wrk.OpenConnection("",dbDriverNoPrompt + dbRunAsync, False, strConnect)

You can use the StillExecuting property of the Connection object to see if the connection has been established, or use the Cancel property of the Connection object to cancel the connection attempt if it takes too long.

The Database Object and the Databases Collection

You can also connect to an ODBC data source by using the OpenDatabase method to open a Database object. However, the Database object in an ODBCDirect workspace doesn't support all of the functionality of a Connection object. Specifically, if you're using a Database object, you can't connect asynchronously, run queries asynchronously, or define QueryDef objects that represent queries in the ODBC data source.

To connect to an ODBC data source with the OpenDatabase method in an ODBCDirect workspace, specify a valid connect string for the connect argument of the OpenDatabase method, as shown in the following example.

Dim wrk As Workspace, dbs As Database
Dim strConnect As String

strConnect = "ODBC;DSN=Pubs;UID=SA;PWD=;DATABASE=Pubs"
Set wrk = DBEngine.CreateWorkspace("NewODBCDirect", "sa", "", dbUseODBC)
Set dbs = wrk.OpenDatabase("Pubs", dbDriverNoPrompt, False, strConnect)

Switching Between Connection and Database Objects

With ODBCDirect, you can open a Database object and a Connection object against the same ODBC data source, and use both in your code. You can then take advantage of each object for its different capabilities.

Alternatively, you may want to create a single object and then switch to the other type when needed. To do this, use the Connection property of the Database object or the Database property of the Connection object. You can use these properties to create Connection objects from Database objects and to create Database objects from Connection objects. This is especially useful for adding ODBCDirect capabilities to existing applications that only use Database objects.

For example, you can use a Database object for most of your ODBC data access needs, but when you need to run an asynchronous query, you can create a Connection object from the Database object and then run the query on the Connection object. The following example illustrates this technique.

Sub DeleteRecords()
	Dim dbs As Database, strConnect As String
	Dim cnn As Connection

	' Open database in default workspace.
	strConnect = "ODBC;DSN=Pubs;DATABASE=Pubs;UID=sa;PWD=;"
	Set dbs = OpenDatabase("", False, False, strConnect)

	' Try to create Connection object from a Database object. If workspace is an
	' ODBCDirect workspace, the query runs asynchronously. If workspace is a
	' Microsoft Jet workspace, an error occurs and the query runs synchronously.

	Err = 0
	On Error Resume Next
	Set cnn = dbs.Connection
	If Err = 0 Then
		cnn.Execute "DELETE FROM Authors", dbRunAsync
	Else
		dbs.Execute "DELETE FROM Authors"
	End If
End Sub

The QueryDef Object and the QueryDefs Collection

The QueryDef object represents a temporary definition of a query in an ODBCDirect workspace. The QueryDefs collection contains all QueryDef objects that currently exist in the workspace. The following table shows the relationship between the QueryDef object and the QueryDefs collection and other objects and collections in an ODBCDirect workspace.

Object or collection Is contained by Contains

QueryDef object QueryDefs collection Parameters collection
Properties collection

QueryDefs collection Connection object QueryDef objects

Unlike QueryDef objects created in a Microsoft Jet workspace, QueryDef objects created in an ODBCDirect workspace are always temporary — they are not saved within the data source before they run, even if you assign them a name.

Running Asynchronous Queries

Creating and running queries in an ODBCDirect workspace is similar to creating and running queries in a Microsoft Jet workspace. You create the query by invoking the CreateQueryDef method on a Connection object, and then use the Execute or OpenRecordset methods on the resulting query.

You can use asynchronous queries so that users can continue using your application while the query runs. You can also give users the ability to cancel asynchronous queries if they are taking too long. The following example runs an asynchronous query.

Function DeleteLargeSales() As Boolean
	Dim wrk As Workspace, rst As Recordset
	Dim cnn As Connection, qdf As QueryDef
	Dim strConnect As String, strSQL As String
	Dim errObj As Error

	On Error GoTo Err_DeleteLargeSales
	' Create ODBCDirect workspace.
	Set wrk = DBEngine.CreateWorkspace("ODBC", "sa", "", dbUseODBC)
	' Create connect string.
	strConnect = "ODBC;DSN=Publishers;UID=SA;PWD=;DATABASE=Pubs"
	' Open connection on workspace.
	Set cnn = wrk.OpenConnection("", dbDriverNoPrompt, False, strConnect)
	' Delete existing QueryDef named DeleteLargeSales.
	For Each qdf In cnn.QueryDefs
		If qdf.Name = "DeleteLargeSales" Then
			cnn.QueryDefs.Delete "DeleteLargeSales"
		End If
	Next qdf

	' Create QueryDef.
	Set qdf = cnn.CreateQueryDef("DeleteLargeSales")
	strSQL = "DELETE FROM sales WHERE qty = 100"
	qdf.SQL = strSQL

	' Run query asynchronously.
	qdf.Execute dbRunAsync

	While qdf.StillExecuting
	' Additional code runs here while query runs.
	' Check StillExecuting property to determine whether query has finished.
	Wend

	DeleteLargeSales = True

Exit_DeleteLargeSales:
	cnn.Close
	wrk.Close
	Exit Function

Err_DeleteLargeSales:
	For Each errObj In Errors
		Debug.Print errObj.Number, errObj.Description
	Next errObj
	DeleteLargeSales = False
	Resume Exit_DeleteLargeSales
End Function

The preceding example uses a QueryDef object on a Connection object to run an asynchronous query. You can also use the Execute method directly on the Connection object, as shown in the following example.

Dim cnn As Connection, strConnect As String

strConnect = "ODBC;DSN=Pubs;UID=SA;PWD=;DATABASE=Pubs"
Set cnn = OpenConnection("", dbDriverNoPrompt, False, strConnect)
cnn.Execute "DELETE FROM sales WHERE qty = 100", dbRunAsync
cnn.Close

When you run a query asynchronously, you can use the StillExecuting property to determine if the query has completed. If the value of the StillExecuting property is True, the query has not yet completed. If you want to cancel an asynchronous query, use the Cancel method, as shown in the following example.

Function CancelAsynchQuery() As Boolean
	Dim wrk As Workspace, cnn As Connection, strConnect As String
	Dim errObj As Error

	On Error GoTo Err_CancelAsynchQuery
	Set wrk = DBEngine.CreateWorkspace("ODBCDirect", "Admin", "", dbUseODBC)
	strConnect = "ODBC;DSN=Pubs;UID=SA;PWD=;DATABASE=Pubs"
	Set cnn = wrk.OpenConnection("", dbDriverNoPrompt, False, strConnect)

	' Start transaction in order to roll back if needed.
	wrk.BeginTrans
	cnn.Execute "DELETE FROM sales WHERE qty = 100", dbRunAsync

	' Perform other operations.
		.
		.
		.

	' If query is still running, cancel and roll back.
	If cnn.StillExecuting Then
		cnn.Cancel
		wrk.Rollback
	' If query is complete, commit transaction.
	Else
		wrk.CommitTrans
	End If
	CancelAsynchQuery = True

Exit_CancelAsynchQuery:
	cnn.Close
	wrk.Close
	Exit Function

Err_CancelAsynchQuery:
	For Each errObj In Errors
		Debug.Print errObj.Number, errObj.Description
	Next errObj
	CancelAsynchQuery = False
	Resume Exit_CancelAsynchQuery
End Function

You can use the StillExecuting property and the Cancel method with QueryDef, Connection, and Recordset objects.

A Connection object can support only one asynchronous operation at a time. Also, you can't perform another DAO operation, such as recordset manipulation, on a Connection object while an asynchronous query runs on the same Connection object. After an asynchronous query is complete, you can then begin running another asynchronous query on the same Connection object. You must first test the value of the StillExecuting property to determine whether you can start the next asynchronous operation. To run multiple asynchronous queries at the same time, you must create separate Connection objects and run each asynchronous query on its own Connection object.

In most cases, you'll want to run an asynchronous query as part of a transaction. Be aware, however, that if you call the CommitTrans method while the asynchronous query is still running, your code will pause at the CommitTrans method until the query finishes. For this reason, it is more efficient to periodically check the StillExecuting property and continue to perform other work while the query runs. Once the StillExecuting property returns False, you can then call the CommitTrans method. This prevents your code from pausing at the CommitTrans method.

Note If you cancel an action query that is not part of a transaction, the query updates records up to the point where you called the Cancel method. The operation will be partially complete and will not be rolled back. For this reason, you should use the Cancel method only within the scope of a transaction. Additionally, if you start an asynchronous query in a procedure and the procedure exits before the query has completed, the query will continue to run.

To improve performance when you're retrieving data from an ODBC data source, you can cache records locally. A cache is a space in local memory that holds the data most recently retrieved from the server. If you're performing repeated operations on a set of data, caching that data makes those operations faster because you don't have to retrieve the data from the server each time you need it.

In ODBCDirect queries, use the CacheSize property of the QueryDef object to specify the number of records to cache. The default cache size is 100 records. The following example shows how to reset the cache size to 200 records.

Sub SetCacheSize()
	Dim
wrk As Workspace, qdf As QueryDef, rst As Recordset
	Dim cnn As Connection, strConnect As String

	Set wrk = CreateWorkspace("ODBCDirect", "Admin", "", dbUseODBC)
	Set cnn = OpenConnection("", dbDriverNoPrompt, False, strConnect)
	strConnect = "ODBC;DSN=Pubs;UID=SA;PWD=;DATABASE=Pubs"
	Set qdf = cnn.CreateQueryDef("tempquery")
	qdf.SQL = "SELECT * FROM roysched"
	qdf.CacheSize = 40
	Set rst = qdf.OpenRecordset()
	' Perform some operations on recordset.
	rst.Close
	cnn.Close
End Sub

The Parameter Object and the Parameters Collection

The Parameter object in an ODBCDirect workspace is similar to the Parameter object in a Microsoft Jet workspace, with a few differences. In an ODBCDirect workspace, you can change the setting of the Type property, which is readonly in a Microsoft Jet workspace. You can also use the Direction property to indicate whether a parameter is an input parameter, an output parameter, or both, or the return value from the procedure. The following example specifies parameters for a query in an ODBCDirect workspace.

Function RunStoredProc() As Boolean
	Dim wrk As Workspace
	Dim qdf As QueryDef, rst As Recordset, fld As Field
	Dim cnn As Connection, strConnect As String, strSQL As String

	Set wrk = CreateWorkspace("ODBCDirect", "sa", "", dbUseODBC)
	strConnect = "ODBC;DSN=Pubs;UID=sa;PWD=;DATABASE=Pubs"
	Set cnn = wrk.OpenConnection("", dbDriverNoPrompt, False, strConnect)

	strSQL = "CREATE PROCEDURE tamram @lolimit money AS " _
		& "SELECT pub_id, type, title_id, price " _
		& "FROM titles WHERE price >@lolimit"
	cnn.Execute strSQL

	Set qdf = cnn.CreateQueryDef("RunStoredProc")
	qdf.SQL = "{ call tamram (?) }"
	qdf.Parameters(0).Value = CCur(10)
	Set rst = qdf.OpenRecordset()
	Do Until rst.EOF
		For Each fld In rst.Fields
			Debug.Print fld.Name, fld.Value
		Next fld
		rst.MoveNext
	Loop
End Function

The Recordset Object and the Recordsets Collection

The Recordset object represents the records that result from running a query on a Connection object or a Database object in an ODBCDirect workspace. The Recordsets collection contains all currently open Recordset objects on a Connection object or a Database object. The following table shows the relationship between the Recordset object and the Recordsets collection and other objects and collections in an ODBCDirect workspace.

Object or collection Is contained by Contains

Recordset object Recordsets collection Field objects
Properties collection

Recordsets collection Connection object
Database object
Recordset objects

The types of Recordset objects supported in an ODBCDirect workspace include the dynasettype, snapshottype, forwardonlytype, and dynamictype Recordset objects. For more information on all of these Recordset objects except the dynamictype Recordset object, see "TableType Recordset Objects," "DynasetType Recordset Objects," "SnapshotType Recordset Objects," "ForwardOnlyType Recordset Objects" earlier in this chapter. The following section describes dynamictype Recordset objects.

DynamicType Recordset Objects

An additional type of Recordset object, the dynamictype Recordset object, is available in ODBCDirect workspaces. Dynamictype Recordset objects behave like dynasettype Recordset objects, but they are updated dynamically as other users make modifications to the underlying tables. To create a dynamictype Recordset object, specify the dbOpenDynamic constant for the type argument of the OpenRecordset method.

Dynamictype Recordset objects are available only if you're using an ODBC driver that supplies its own cursors. Because not all ODBC drivers supply their own cursors, you need to determine whether yours does before you try to open a dynamictype Recordset object. If your ODBC driver doesn't supply its own cursors, then you should open a snapshottype or forwardonlytype Recordset object instead. For more information on cursors, see "Using Cursors in ODBCDirect Workspaces" later in this chapter.

The advantage of using a dynamictype Recordset object is that the recordset will immediately reflect any changes to the data, including added or deleted records. For example, if you open a dynamictype Recordset object and another user edits a record in one of the underlying tables, that change will be reflected in the Recordset you opened. In order to do this, however, DAO must constantly requery the data source, which may slow performance considerably. Therefore, avoid using dynamictype Recordset objects except in situations where it's crucial to have the most uptodate data at all times.

Opening Recordset Objects Asynchronously

In addition to running queries asynchronously, you can open Recordset objects asynchronously. To do so, specify the dbRunAsync constant for the options argument of the OpenRecordset method. You can then use the Cancel method and the StillExecuting property directly on the Recordset object. For example, if you open a Recordset object asynchronously, and it takes a long time to open because more records are returned than expected, you can give users the option of canceling the operation in order to specify more restrictive criteria that returns fewer records.

If you cancel an OpenRecordset method, the Recordset object becomes invalid and you must reopen it to retrieve a valid Recordset object.

Because moving to the last record in a recordset can take a long time, the MoveLast method of a Recordset object supports asynchronous operation. To perform an asynchronous MoveLast operation, use the dbRunAsync constant with the MoveLast method. Be sure to check the StillExecuting property to determine when this operation is complete.

The Field Object and the Fields Collection

In an ODBCDirect workspace, the Field object represents a field in a QueryDef object or a Recordset object. When you're performing batch updates, you can use the Value, VisibleValue, and OriginalValue properties of a Field object to verify successful completion of a batch update. For more information, see "Using Batch Optimistic Updating" in the following section.

Using ODBCDirect

The following sections explain how to perform some common operations in an ODBCDirect workspace: using batch optimistic updating, working with cursors, and working with stored procedures.

Using Batch Optimistic Updating

In many client/server applications, optimistic updates occur on a recordbyrecord basis. This usually happens with the following series of events:

A user edits a record.

The user tries to save the record.

The server attempts to place a lock on that record, and if successful, the record is updated. Otherwise, a lock violation is handled by the application.

The user moves to another record and the entire process is repeated.

Although this process works well for many applications, it is often more efficient to have the user edit multiple records that are cached locally and then submit these records to the server in a single batch for updating. This process is called batch optimistic updating.

To use batch optimistic updating

Create an ODBCDirect workspace.
Set the DefaultCursorDriver property of the workspace to dbUseClientBatchCursor.
Open a Connection or Database object from the ODBCDirect workspace.
Use the OpenRecordset method on the Connection or Database object to open a Recordset and specify the dbOptimisticBatch constant in the lockedits argument.
Perform any edits to the Recordset object. All edits are cached locally.
When you are ready to update the data source, call the Update method on the Recordset object, specifying dbUpdateBatch for the type argument.

Note If you attempt a batch update while a record in that Recordset object is being edited by the user, the record being edited will automatically be updated before the batch update begins.

The following example illustrates how to use batch optimistic updating.

Function RunInBatch()
	Dim wrk As Workspace, cnn As Connection, rst As Recordset
	Dim strConnect As String

	' Create ODBCDirect workspace.
	Set wrk = DBEngine.CreateWorkspace("ODBCDirect", "Admin", "", dbUseODBC)
	' Set default cursor driver to dbUseClientBatchCursor.
	wrk.DefaultCursorDriver = dbUseClientBatchCursor
	' Create connect string.
	strConnect = "ODBC;DSN=Pubs;DATABASE=Pubs;UID=sa;PWD=;"
	' Open connection.
	Set cnn = wrk.OpenConnection("", dbDriverNoPrompt, False, strConnect)
	' Open recordset on connection.
	Set rst = _
      cnn.OpenRecordset("SELECT * FROM sales", dbOpenDynaset, 0, dbOptimisticBatch)

	' Change all records in local recordset.
	While Not rst.EOF
		rst.Edit
		rst!qty = rst!qty + 1
		rst.Update
		rst.MoveNext
	Wend

	' Update all records in data source.
	rst.Update dbUpdateBatch
End Function

If multiple records have been edited locally, and you want to update the current record before you perform the batch update, you can call the Update method and specify the dbUpdateCurrentRecord constant for the type argument. This writes the current record to the data source without writing any other batch updates. This is illustrated in the following example.

' Edit and update first record.
' Only first record is written back to data source.
rst.MoveFirst
rst.Edit
rst!qty = rst!qty + 2
rst.Update dbUpdateCurrentRecord

' Update remaining records in data source.
rst.Update dbUpdateBatch

Handling Collisions

When you attempt to update a group of records in a single batch operation, it is possible that other users are editing one or more records you are trying to update, causing a collision. A collision occurs when a batch update attempts to update a record at the same time another user is updating the record.

To handle collisions, examine the BatchCollisions property on the Recordset object. The BatchCollisions property returns an array that stores bookmarks pointing to records in the Recordset object on which a collision occurred. Each time a collision occurs during a batch update, a bookmark for the record is added to the array returned by the BatchCollisions property. You can then move to each of these bookmarks and examine the following properties of the Field object of the current record.

Property Description

Value The current value of the field in your Recordset object. This corresponds to the value of the field after the Update method was called.

OriginalValue The value of the field in your Recordset object before the Update method was called.

VisibleValue The value of the field as it is stored in the database.

After examining these properties, you can choose one of the following options:

You can force the current value in your Recordset object into the database, overwriting the field's original value. To do this, call the Update method and specify True for the force argument.

You can change the current value in your Recordset object to the original value and force the change into the database.

Caution

Update

dbUpdateBatch

type

True

force

Update

force

BatchCollisions

Value

OriginalValue

VisibleValue

The following example shows how to use the array returned by the BatchCollisions property to force all changes made to a local Recordset object into the database.

Function BatchForceChanges()
	Dim rst As Recordset, cnn As Connection, varCollision As Variant

	' Open recordset for batch optimistic updating.
	Set rst = _
      cnn.OpenRecordset("SELECT * FROM sales", dbOpenDynaset, 0, dbOptimisticBatch)
	' Change all records in local recordset.
	While Not rst.EOF
		rst.Edit
		rst!qty = rst!qty + 1
		rst.Update
		rst.MoveNext
	Wend
	rst.Update dbUpdateBatch

	' Check for collisions and force all changes to recordset
	' into database one record at a time.
	For j = 0 to rst.BatchCollisionCount - 1
		varCollision = rst.BatchCollisions(j)
		rst.BookMark = varCollision
		rst.Update dbUpdateCurrentRecord, True
	Next j
End Function

In the preceding example, modifications to the Recordset object are written back to the database one record at a time. In the following example, all records are saved in a batch instead of writing one record at a time.

' Open recordset.
Set rst = _
   cnn.OpenRecordset("SELECT * FROM sales", dbOpenDynaset, 0, dbOptimisticBatch)
' Change all records in local recordset.
While Not rst.EOF
	rst.Edit
	rst!qty = rst!qty + 1
	rst.Update
	rst.MoveNext
Wend
rst.Update dbUpdateBatch, True

Using Cursors in ODBCDirect Workspaces

A cursor indicates the current record position in a result set. Most types of cursors contain a representation of the data in the data source, and are not updatable. Keysets are cursors that contain actual data, and are updatable.

You work with a cursor through the DAO Recordset object. When you open a Recordset object through DAO, ODBCDirect creates the corresponding cursor. Each type of Recordset object, except for the tabletype Recordset object, corresponds to a different type of cursor.

Characteristics of Cursors

You can use cursors to work with sets of data on an ODBC data source. Cursors can:

Represent some or all records in a single table.

Represent some or all records in a multipletable join.

Represent no records.

Be readonly or updatable at either the cursor or the field level.

Be fully scrollable, meaning that you can move forward and backward through the records, or they can be forwardonly scrolling.

Exist on either the client or the server.

ClientSide Cursors vs. ServerSide Cursors

A cursor requires temporary resources to hold its data. These resources can be in the form of RAM, a paging file such as the virtual memory feature of Microsoft Windows, or temporary files or databases. If these resources are stored on the client machine, the cursor is called a clientside cursor. With this type of cursor, the server sends the data that the cursor represents across the network to the client, along with the data required by the cursor itself. The client manages the temporary resources needed by the cursor.

Some server database engines, such as Microsoft SQL Server version 6.0, support an additional type of cursor known as serverside cursors. With this cursor type, the server manages the result set with resources located on the server itself. The server returns only the requested data to the client over the network. Using this type of cursor can result in significant performance improvements compared to clientside cursors, especially in situations where excessive network traffic or inadequate network bandwidth is a problem. However, because RAM and disk space resources are needed at the server, you must plan accordingly and ensure that your server hardware is capable of managing all cursors requested by clients.

Choosing a Cursor Type

When you open a Recordset object on a nonODBC data source, you can specify a constant for the type argument of the OpenRecordset method that determines what type of recordset is opened. When you open a Recordset object on an ODBC data source, you use this same argument to specify the type of cursor that the Recordset object represents. Each type of cursor corresponds to a type of recordset. The following table shows the four constants you can use for the type argument, the type of Recordset object that is created on a nonODBC data source, and the type of cursor that is created on an ODBC data source.

Constant Recordset type Cursor type

dbOpenDynamic Dynamic-type Dynamic

dbOpenDynaset Dynaset-type Keyset

dbOpenSnapshot Snapshot-type Static

dbOpenForwardOnly Forward-only-type Forward-only scrolling (this is the default)

For more information about ODBC cursors, see the ODBC 3.0 Programmer's Reference.

Note Tabletype Recordset objects aren't supported in ODBCDirect workspaces, so they have no corresponding cursor.

The DefaultCursorDriver property of a Workspace object specifies where ODBCDirect creates the cursor — on the client or on the server. You can set the DefaultCursorDriver property to any of the constants listed in the following table.

Constant Description

dbUseODBCCursor Use clientside cursors. Clientside cursors give better performance for small result sets, but degrade quickly for larger result sets.

dbUseServerCursor Use serverside cursors. For most large operations, serverside cursors provide better performance, but may cause more network traffic. Not all ODBC data sources support serverside cursors.

dbUseDefaultCursor Use serverside cursors if the server supports them; otherwise, use clientside cursors.

dbUseClientBatchCursor Use client batch cursors. Required for batch updates.

dbUseNoCursor Open all Recordset objects as forwardonlytype, readonly, with a rowset size of 1.

Record Locking

When you open a Recordset object, you can also specify the type of record locking you want to use by setting the lockedits argument of the OpenRecordset method to the appropriate constant. The following table lists the five constants you can use for the lockedits argument of the OpenRecordset method, and describes the ODBC cursor lock type to which they correspond.

Constant ODBC cursor lock type

dbOptimistic Uses optimistic locking to determine how changes are made to the Recordset object in a multiuser environment. The page containing the record that is being edited is locked only while the record is being updated by the Update method.

DbPessimistic Uses pessimistic locking to determine how changes are made to the Recordset object in a multiuser environment. The page containing the record that is being edited is locked as soon as you use the Edit method.

DbOptimisticValue Uses optimistic concurrency based on record values.

DbOptimisticBatch Uses batch optimistic updating.

DbReadOnly Default for ODBCDirect workspaces. Prevents users from making changes to the data in the Recordset object.

Some combinations of cursors and lock types will not work together. For example, with Microsoft SQL Server version 6.0 cursors, if you specify the dbOpenSnapshot constant for the type argument of the OpenRecordset method, you must specify the dbReadOnly constant for the lockedits argument. Static cursors do not support the other types of record locking. Which combinations work together depends on the cursor driver. For specific information about compatible lock types, refer to your cursor driver documentation.

Your cursor driver can handle different combinations of cursor types and lock types in different ways. In some cases, it may return an error if it does not handle a specific combination. In other cases, it may switch to the nearest possible combination that it supports. If an error occurs, DAO places the error information in the Errors collection.

Cursor Limitations

In an ODBCDirect workspace, the default recordset is a readonly, forwardonlytype Recordset object. Therefore, if you create the default Recordset object by opening it without specifying a value for the type argument, you won't be able to edit data on the server. If you want to edit data on the server, you need to explicitly specify a lock type other than dbReadOnly for the lockedits argument of the OpenRecordset method.

Because you can't open a tabletype Recordset object in an ODBCDirect workspace, you can't use the Index property or the Seek method to retrieve data. Also, recordsets opened against ODBC data sources do not support any of the Find methods: FindFirst, FindNext, FindPrevious, and FindLast. In a client/server environment, it's more efficient to fetch only the data that you need, rather than retrieving more records than you need and then searching through those records for the data that you want. Therefore, design your queries to return only the records that you need.

Retrieving Multiple Result Sets

Any SQL statement can include multiple SELECT statements or stored procedures that invoke one or more SELECT statements. Each SELECT statement generates a result set that must be processed by your code or discarded before the resources are released and the next result set is made available. Because you don't necessarily know how many results sets will be generated by a stored procedure, your code must be prepared to process an unknown number of result sets. Note that when a stored procedure returns multiple result sets, none of the result sets can be updated.

You can use either clientside cursors or serverside cursors to retrieve multiple result sets. If you use clientside cursors, multiple result sets are returned no matter what type of Recordset object you open. If you use serverside cursors to retrieve multiple result sets, you must open a forwardonlytype Recordset object.

To retrieve multiple results sets

Set the workspace's DefaultCursorDriver property to dbUseServerCursor to specify serverside cursors.

Create a QueryDef object and set its SQL property to a valid SQL string that returns multiple Recordset objects.

Set the CacheSize property of the QueryDef object to 1 to request that the server sends you one record at a time. When you retrieve records in this way, you don't actually utilize the cursor.

Open a Recordset object on the QueryDef object you just created. Specify dbOpenForwardOnly for the type argument of the OpenRecordset method.

Use the NextRecordset method to access the next Recordset object in the group of Recordset objects returned by the server. This discards the current Recordset object and replaces it with the next Recordset object specified in your query's SQL statement. If there are no more Recordset objects in the group of Recordset objects, then the return value of the NextRecordset method will be False and the current Recordset object will be empty.

The following example prints the values of each field for each record in each result set.

Function GetMultipleResults()
	Dim wrk As Workspace, rst As Recordset, cnn As Connection, qdf As QueryDef
	Dim fld As Field, strSQL As String, strConnect As String, fDone As Boolean

	' Create ODBCDirect workspace.
	Set wrk = DBEngine.CreateWorkspace("ODBCDirect", "Admin", "", dbUseODBC)
	' Create connect string.
	strConnect = "ODBC;DSN=Pubs;DATABASE=Pubs;UID=sa;PWD=;"
	' Open connection.
	Set cnn = wrk.OpenConnection("", dbDriverNoPrompt, False, strConnect)
	' Create SQL statement.
	strSQL = "SELECT au_lname, au_fname FROM Authors; SELECT title FROM Titles;"
	' Set default cursor driver.
	wrk.DefaultCursorDriver = dbUseServerCursor

	' Open recordset.
	Set qdf = cnn.CreateQueryDef("", strSQL)
	qdf.CacheSize = 1
	' Open recordset on QueryDef.
	Set rst = qdf.OpenRecordset(dbOpenForwardOnly)

	Do Until fDone = True
		' Print values for each field in each record of recordset.
		While Not rst.EOF
			For Each fld In rst.Fields
				Debug.Print fld.Value
			Next fld
			rst.MoveNext
		Wend
		fDone = Not rst.NextRecordset()
	Loop
	rst.Close
	cnn.Close
	wrk.Close
End Function

Working with Stored Procedures

You can use ODBCDirect QueryDef objects to run stored procedures. ODBCDirect QueryDef objects support stored procedures that have both input parameters and return values. Input parameters are the parameter values supplied to the procedure at run time. The procedure's return value is the value that it returns when it has finished running. For example, a stored procedure may return the number of records that have been affected.

The following example creates a stored procedure named GetEmps on the server.

strSQL = "CREATE PROCEDURE GetEmps AS "
strSQL = strSQL & "SELECT * FROM EMPLOYEE;"
cnn.Execute strSQL

If there is already a stored procedure named GetEmps on the server, you can use the DROP statement to delete it before creating a new one, as shown in the following example.

strSQL = "DROP PROCEDURE GetEmps;"
cnn.Execute strSQL

You can run the stored procedure by using the Execute method of a Connection object. To retrieve the return value, create a QueryDef object and open a recordset on it.

Set qdf = cnn.CreateQueryDef("qry", "{ call GetEmps() }")
Set rst = qdf.OpenRecordset

Use the Parameter object to work with parameters. The Direction property of a Parameter object tells DAO how the parameter will function. The ODBC driver tries to determine the parameter direction, but the Direction property is read/write, so you can set it if you need to. The following example creates a simple stored procedure with an input parameter and a return value. It then runs the procedure and retrieves the return value.

' Create stored procedure on the server.
strSQL = "CREATE PROCEDURE UpdateEmps (@invar int) AS RETURN @invar;"
cnn.Execute strSQL

' Create QueryDef object to run stored procedure.
Set qdf = cnn.CreateQueryDef("qry", "{ ? = call UpdateEmps(?) }")

' Handle parameters.
qdf.Parameters(0).Direction = dbParamReturnValue
qdf.Parameters(1) = 10
qdf.Execute

' Get return value.
var = qdf.Parameters(0).Value

Object	Description
Connection	Network connection to an Open Database Connectivity (ODBC) database
Container	Security information for various types of objects in the database
Database	Open database
DBEngine	The top-level object in the DAO object hierarchy
Document	Security information for individual objects in the database
Error	Data access error information
Field	Field in a TableDef, QueryDef, Recordset, Index, or Relation object
Group	Group account in the current workgroup
Index	Table index
Parameter	Query parameter
Property	Property of an object
QueryDef	Saved query definition in a database
Recordset	Set of records defined by a table or query
Relation	Relationship between two table or query fields
TableDef	Saved table definition in a database
User	User account in the current workgroup
Workspace	Active DAO session

Object or collection	Is contained by	Contains
Database object	Databases collection	Containers collection QueryDefs collection Properties collection Recordsets collection Relations collection TableDefs collection
Databases collection	Workspace object	Database objects

Container name	Contains information about
Databases	Saved databases
Tables	Saved tables and queries
Relationships	Saved relationships

Container name	Contains information about
Forms	Saved forms
Modules	Saved modules
Reports	Saved reports
Scripts	Saved macros

Constant	Recordset type	Cursor type
dbOpenDynamic	Dynamic-type	Dynamic
dbOpenDynaset	Dynaset-type	Keyset
dbOpenSnapshot	Snapshot-type	Static
dbOpenForwardOnly	Forward-only-type	Forward-only scrolling (this is the default)

Constant	Description
dbUseODBCCursor	Use clientside cursors. Clientside cursors give better performance for small result sets, but degrade quickly for larger result sets.
dbUseServerCursor	Use serverside cursors. For most large operations, serverside cursors provide better performance, but may cause more network traffic. Not all ODBC data sources support serverside cursors.
dbUseDefaultCursor	Use serverside cursors if the server supports them; otherwise, use clientside cursors.
dbUseClientBatchCursor	Use client batch cursors. Required for batch updates.
dbUseNoCursor	Open all Recordset objects as forwardonlytype, readonly, with a rowset size of 1.

Constant	ODBC cursor lock type
dbOptimistic	Uses optimistic locking to determine how changes are made to the Recordset object in a multiuser environment. The page containing the record that is being edited is locked only while the record is being updated by the Update method.
DbPessimistic	Uses pessimistic locking to determine how changes are made to the Recordset object in a multiuser environment. The page containing the record that is being edited is locked as soon as you use the Edit method.
DbOptimisticValue	Uses optimistic concurrency based on record values.
DbOptimisticBatch	Uses batch optimistic updating.
DbReadOnly	Default for ODBCDirect workspaces. Prevents users from making changes to the data in the Recordset object.