Microsoft Jet Database Engine Programmer's Guide - Chapter 2

(blank title page)

Introducing Data Access Objects

Data Access Objects (DAO) is the programming interface for the Microsoft Jet database engine. You can use the objects and collections in the DAO object hierarchy to manipulate your data and the structure of your database. DAO is a shared component of Microsoft Office. Any application that supports Automation can use DAO to work with objects in a database. This chapter introduces you to DAO and discusses how to use Microsoft Visual Basic for Applications code.

Using the Code Examples in This Chapter

You can use the code examples in this chapter to help you understand the concepts discussed, or you can modify them and use them in your own applications.

The code examples are located in the JetBook\Samples subfolder on the companion CD-ROM. The code examples for Microsoft Access 97 are in JetSamples.mdb, and the corresponding code examples for Microsoft Visual Basic version 5.0 and other applications that support Visual Basic for Applications are referenced in JetSamples.vbp. Both JetSamples.mdb and JetSamples.vbp use tables and queries in NorthwindTables.mdb, also located in the JetBook\Samples subfolder.

To use the code examples, copy the sample files to your hard disk. Be sure to copy NorthwindTables.mdb as well so that you can use the code examples to work with data.

See Also For more information about copying and using the code examples from the companion CD-ROM, see "Using the Companion CD-ROM" in the Preface.

Hello DAO

"Hello World" is the first code example in the now-classic book, The C Programming Language, by Brian Kernighan and Dennis Ritchie. The following examples show you how to perform a simple task with DAO using Hello World. But instead of printing a string variable to the screen, our version searches for a value in a table and retrieves a message for display.

The NorthwindTables database includes a table with one text field, Greeting, that contains different greetings. The following table shows the data that the Greeting field contains.

Greeting
Bonjour
Buenos dias
Hello World!
Hi
Howdy

The following code shows one way to retrieve the greeting. In this example, strDbPath is the path to the NorthwindTables database, and strGreeting is the string for which you are searching:

Dim dbs As Database Dim rst As Recordset  Set dbs = OpenDatabase(strDbPath) Set rst = dbs.OpenRecordset("Greetings", dbOpenTable)  With rst .Index = "GreetingIndex" .Seek "=", strGreeting If Not .NoMatch Then MsgBox !Greeting Else MsgBox "Could not find " & strGreeting & "." End If End With Set rst = Nothing Set dbs = Nothing

The following explains what's happening when this code runs:

1. First, two object variables named dbs and rst are declared. These are used to point to the actual Database and Recordset objects used in the example.

2. The Set statement and the OpenDatabase method return a reference to the NorthwindTables database, represented by the variable strDbPath, and assign the variable dbs to the database. If you install the sample databases to the default folder, then the value of strDbPath is"C:\JetBook\Samples\NorthwindTables.mdb". If you open NorthwindTables.mdb in Microsoft Access and paste this code into a module, you can set the Database object variable by using the CurrentDb function.

3. The Set statement is used again, this time to assign the rst object variable to the table named Greetings. This is done by using the OpenRecordset method of the Database object. This method tells DAO to look in the NorthwindTables database (NorthwindTables.mdb) for the Greetings table.

4. Next, the code tells DAO which index to use when searching the table. This is done by setting the Index property of the Recordset object to the value "GreetingIndex". This index exists on the Greeting field in the Greetings table.

5. Then the Recordset object's Seek method is called to find the record where the value of the indexed field is equal to value of strGreeting. You can experiment with searching for other values in the table by using the Seek method to look for other values in the table.

6. Next, the NoMatch property of the Recordset object is checked to see if a match was found. If a match was found, the value of the Greeting field is displayed. If not, an error message is displayed.

7. Finally, the Set statement is used at the end of the procedure to set the value of the Database and Recordset object variables to Nothing, freeing up any memory that they may have used while they existed.

The end result is a program that displays one of the greetings (such as "Hello World!") stored in the Greetings table of the NorthwindTables database. The key concepts in this example are:

• DAO is a hierarchy of objects. The Greetings TableDef object is in the TableDefs collection of the Database object, and the GreetingIndex Index object is in the Indexes collection of the Greetings TableDef object.

• Methods initiate actions on objects. This example uses methods to open a database and a table, and to find a record.

• Properties define characteristics of objects. In this example, the value of the Index property of the Recordset object is set to tell DAO how to order the records that you are going to search.

As you progress through the rest of this chapter, you'll see how these elements work together to form a cohesive DAO object model that provides flexible access to the objects and data in your database.

DAO Objects

DAO is a hierarchy of objects and collections of objects. As shown in Figure 2.1, each table, field, index, query, and so on is represented by objects organized into collections. Each object has a set of properties that define its characteristics and one or more methods that you use to perform various operations on the object. This section introduces the DAO object hierarchy and provides information about each type of object it contains.

Figure 2.1 Collections, objects, properties, and methods

In the DAO object hierarchy, all objects are contained in a single DBEngine object. Figure 2.2 shows the DAO object hierarchy for Microsoft Jet workspaces.

Note DAO version 3.5 includes a second object hierarchy — the ODBCDirect object hierarchy. You can use the ODBCDirect object hierarchy to work with ODBC data sources without going through the Jet database engine. The objects in the ODBCDirect object hierarchy are not shown in Figure 2.2. For information about ODBCDirect and to see the ODBCDirect object hierarchy, see Chapter 9, "Developing Client/Server Applications."

Figure 2.2 The DAO object hierarchy for Microsoft Jet workspaces

DBEngine Object

The DBEngine object contains and controls all other collections and objects in the DAO object hierarchy. The DBEngine object doesn't have an associated collection.

Note Prior to version 3.0 of DAO, you could run up to 10 applications that simultaneously used the DBEngine object. You can now run as many processes (executions of Microsoft Jet, other applications, and so on) as your system resources allow. Additionally, within each process, you can have up to 64 instances of Microsoft Jet. For example, you can create 64 private DBEngine objects within an application.

The DBEngine object has several properties and methods and contains two collections: the Workspaces collection and the Errors collection. You use the DBEngine object when referring to a database. When using DAO from within Microsoft Access, you can use the CurrentDb function as a shortcut to refer to the DBEngine object for the current database.

Error Objects

As you perform operations by using DAO, errors may occur. Each error is stored as an Error object in the Errors collection. You can use information contained in the Error object to determine what caused the error, and to display meaningful error messages to your users.

It's important to note that as each DAO error occurs, the Errors collection is cleared of previous errors, and the new Error object is placed in the Errors collection. There can be several related Error objects in the collection caused by a single operation.

See Also For a list of errors returned by Microsoft Jet, see Appendix D, "Error Reference."

Workspace Objects

There are two types of Workspace objects: Microsoft Jet Workspace objects and ODBCDirect Workspace objects. This section discusses Microsoft Jet Workspace objects. For information about ODBCDirect Workspace objects, see Chapter 9, "Developing Client/Server Applications."

A Workspace object exists for each active session of the Jet database engine. A session delineates a sequence of operations performed by Microsoft Jet. A session begins when a user logs on and ends when the user logs off. All operations during a session are subject to permissions determined by the logon user name and password. The Workspaces collection contains all Workspace objects defined by the currently running instance of DAO.

The ability to create multiple workspaces in code is useful when you have to log on to Microsoft Jet as another user. Also, because each Workspace object maintains its transactions independent of other workspaces, managing multiple workspaces can be useful when you have to manage multiple sets of independent transactions.

When you start DAO, what happens with workspaces depends on whether or not you have security established. If you don't have security established, DAO automatically creates the default Workspace object. The settings of the Name and UserName properties of the default Workspace object are #Default Workspace# and Admin, respectively.

If you want to use security, you can set the SystemDB property to the current location of the workgroup information file (System.mdw), set the DefaultUserName property, and set the DefaultPassword property. When you use the OpenDatabase method without specifying a Workspace object, a default Workspace object is created by using these defaults.

Database Objects

The Databases collection contains all Database objects currently open within a Workspace object. In DAO, a Database object represents a currently open database. This can be a native Microsoft Jet database or an external database. You can have multiple databases open at one time, even databases of different types.

In Visual Basic, after you open a database and return a reference to it by using the OpenDatabase method, you can refer to the database according to its position in the Databases collection. If you open only one database, then you can refer to that database in any of the following ways:

DBEngine.Workspaces(0).Databases(0) DBEngine.Workspaces(0)(0) DBEngine(0)(0)

If you're working in Microsoft Access 97, you can use either the CurrentDb function or the DBEngine(0)(0) syntax to return a reference to the database that is currently open in the Microsoft Access user interface. The CurrentDb function differs from the DBEngine(0)(0) syntax in that it creates another instance of the current database and returns a reference to that instance, and it refreshes all the collections in the current database. On the other hand, the DBEngine(0)(0) syntax doesn't refresh any collections, so it may perform faster.

If you switch from using the CurrentDb function to the DBEngine(0)(0) syntax, make sure that your code doesn't depend on any collections being refreshed. If it does, you can refresh these individual collections separately.

Note If you specify a value for the connect argument of the OpenDatabase method, you must also specify a value for the preceding read-only argument.

Microsoft Access and Visual Basic Users In Microsoft Access versions 1.x and 2.0, several databases are automatically opened when you open your database through the Microsoft Access user interface. These include the various library databases that Microsoft Access uses to perform its own functions. The other databases that Microsoft Access opens are used internally by Microsoft Access and are not part of the Databases collection. In Microsoft Access 97, library databases are identified by references to specific type libraries, but are not actually loaded until needed.

Microsoft Access also creates a default Workspace object when you open a database. The user account that's currently logged on to the database is the account used to create the default workspace.

In Visual Basic, no databases are open in a workspace until you explicitly open them by using the OpenDatabase method — unless you use the Data control, which automatically opens the appropriate database for you when the form containing the Data control is loaded.

Container Objects

The DBEngine object is application independent. This means that the engine isn't specifically tied to any one host application. However, Microsoft Jet does provide a generic collection and object type so that an application can create and store its own objects in a database. This generic object is known as a container. The Containers collection holds all Container objects for a Database object.

As an example, Microsoft Access relies on Microsoft Jet to store its application-specific objects such as forms, reports, macros, and modules. Microsoft Jet keeps track of these foreign objects through the Containers collection. It's the Container object that enables Microsoft Jet to store application-specific objects without violating its application independence. You can use the Container object to set security to control access to all database objects of a particular type.

Document Objects

The Document object represents a specific object in the Documents collection. As noted in the preceding section, Microsoft Access uses the Containers collection to organize its own objects. The Document object stores a specific instance of an application-specific object. For example, when Microsoft Access creates a database, it creates a variety of Container objects, one each to store forms, reports, macros, and modules. As the Microsoft Access user creates forms, the forms are stored as individual Document objects in the forms Container object that is added to the database by Microsoft Access. You can set security to control access to a document.

QueryDef Objects

The QueryDef object represents a query in the database and maintains information about the query's properties, including its SQL representation. The QueryDefs collection contains all QueryDef objects for a database.

Parameter Objects

With Microsoft Jet queries, you can define formal parameters. Formal parameters represent unknown values that must be supplied by the user running the query or the program executing the query. A query's formal parameters are represented by Parameter objects in the Parameters collection of a QueryDef object. This collection is particularly useful when you execute a parameter query in code and have to supply values for the query parameters at run-time.

See Also For more information about formal and implicit parameters, see Chapter 4, "Queries."

Recordset Objects

You use Recordset objects to manipulate data in a database at the record level. There are five types of Recordset objects: table-type Recordset objects, dynaset-type Recordset objects, snapshot-type Recordset objects, forward-only-type Recordset objects, and dynamic-type Recordset objects. For more information about the types of Recordset objects, see "Manipulating Data" later in this chapter.

The Recordset object is somewhat different from other DAO objects discussed so far in that it exists only while your code or application is running. A Recordset object represents, in memory, a temporary set of records from one or more tables. Recordset objects are one of the most powerful objects in DAO because you can use them to programmatically access not only native Microsoft Jet tables, but any tables from ISAM data sources such as Microsoft FoxPro, or ODBC data sources such as Microsoft SQL Server. In addition, Recordset objects can be based on queries that join multiple tables from heterogeneous data sources.

The Recordsets collection contains all Recordset objects open in the current Database object. Note that a Recordset object is an object that you explicitly open through code. For example, in Microsoft Access, when you are viewing a table through the user interface, this table isn't a Recordset object.

See Also For more information about Recordset objects, see Chapter 5, "Working with Records and Fields."

Relation Objects

You can use the Relation object to create new relationships and examine existing relationships in your database. A Relation object represents a relationship between fields in two or more tables. The Relations collection contains all Relation objects for a given Database object.

TableDef Objects

The TableDef object represents a table in a database. This includes tables in the current database, as well as linked tables (known as attached tables in previous versions of Microsoft Jet). It's important to note that TableDef objects don't represent data stored in the table — they represent the structure of the table. The TableDefs collection contains all TableDef objects for a database.

Index Objects

Index objects specify the order of records accessed from database tables and whether or not duplicate records are accepted, providing efficient access to data. For external databases, Index objects describe the indexes established for external tables. The Indexes collection contains all the stored Index objects for a TableDef object.

Microsoft Jet uses indexes when it joins tables and creates Recordset objects. Indexes determine the order in which table-type Recordset objects return records, but they don't determine the order in which Microsoft Jet stores records in the base table or the order in which any other type of Recordset object returns records.

Field Objects

Field objects define a specific field (sometimes referred to as a column). TableDef, QueryDef, Index, Relation, and Recordset objects all contain Fields collections. The following table explains the Field object's representation for each type of object that can contain a Fields collection.

Object	Description of Fields collection
TableDef	Defines the fields in a table, but doesn't contain data.
QueryDef	Defines the fields in a query, but doesn't contain data.
Index	Defines the characteristics of the fields that make up an index in the context of the information the index must maintain about the field.
Relation	Defines the characteristics of the fields that make up a relationship in the context of the information the relationship must maintain about the field.
Recordset	Defines the fields that exist in the tables or queries that the recordset is based on. Contains data in the field's Value property.

Group Objects

Microsoft Jet provides security services you can use to protect objects in the database from unwanted access. The security model relies on users and groups defined in a workgroup information file. The Groups collection is used by Microsoft Jet in its security model to control access to objects. The Groups collection contains all Group objects. A Group object defines a group of users as it exists in the Workspace object's workgroup information file. Each Group object also has a Users collection that contains a User object for each of the users in the group.

User Objects

The Users collection contains all User objects for a Workspace object. A Bbject defines a user account as it exists in the Workspace object's workgroup information file. Each User object also has a Groups collection that contains a Group object for each group to which the user belongs.

See Also For more information about workgroup information files and security, see Chapter 10, "Managing Security."

Property Objects

Every DAO object has a Properties collection, which contains all the properties associated with the object. A Property object contains information about the characteristics of a given property. You can read property values to get information about an object's characteristics or write to a property to define an object's characteristics. You can also create your own properties and add them to an object's Properties collection.

DAO Basics

Now that you're familiar with DAO objects and collections, it's time to move on to the basics of using DAO. This section shows you how to work with DAO objects and collections, how to refer to objects, and how to represent objects by using object variables.

DAO Objects and Collections

As you've seen from the discussion of the DAO object hierarchy, the concept of objects and collections is central to an understanding of DAO. Most object types have collections that contain each of that type's members. For example, the TableDefs collection represents a set of all tables in the database, and it contains all individual TableDef objects.

Guidelines for Naming DAO Objects

When you name your DAO objects, you must adhere to the following guidelines. Names of DAO objects:

• Can be up to 64 characters long.

• Can include any combination of letters, numbers, spaces, and special characters except a period (.), an exclamation point (!), an accent grave (`), and brackets ([ ]).

• Can't begin with leading spaces.

• Can't include control characters (ASCII values 0 through 31).

Although you can include spaces, they can produce naming conflicts in Visual Basic in some circumstances.

When you name an object, it's a good idea to make sure the name doesn't duplicate the name of a property or other element used by DAO; otherwise, your database can produce unexpected results.

Referring to Objects

When you refer to objects in code, specify the hierarchical path that points to the object to which you want to refer. In general, start with the DBEngine object and work your way through the object hierarchy, as shown in the following syntax:

DBEngine.ParentCollection.ChildCollection("ObjectName")

For example, in the following code, the dbs object variable represents the DBEngine object. The code sets the value of a Field object variable to the CompanyName field (a member of the Fields collection) in the Customers table (a member of the TableDefs collection):

Set fld = dbs.TableDefs("Customers").Fields("CompanyName")

See Also For more information about using object variables to refer to objects, see "Using Object Variables" later in this chapter.

There are three ways to refer to an object in a collection:

• Collection("name")

• Collection(expression) where expression is a string variable containing the name of the object

• Collection(index) where index is the object's position within the collection

The first way to refer to an object is to use the Collection("name") syntax. In the following example, strDbPath is the path to the NorthwindTables database, which contains the Customers table:

Dim dbs As Database Dim tdf As TableDef  Set dbs = OpenDatabase(strDbPath) ' Return reference to Customers table. Set tdf = dbs.TableDefs("Customers")

The second way to refer to an object is to use the Collection(expression) syntax. You can use this technique to refer to an object by using a variable. In this example, strDbPath is the path to the database, and strTableName is the name of a table in that database:

Dim dbs As Database Dim tdf As TableDef  Set dbs = OpenDatabase(strDbPath) ' Return reference to table specified by value of variable. Set tdf = dbs.TableDefs(strTableName)

The value of strDbPath in the preceding example may be"C:\JetBook\Samples\NorthwindTables.mdb", and the value of strTableName may be "Customers".

By using the Collection(index) syntax, you can refer to an object according to its position in a collection. In this example, strDbPath is the path to the database:

Dim dbs As Database Dim tdf As TableDef  Set dbs = OpenDatabase(strDbPath) ' Return reference to first TableDef in TableDefs collection. Set tdf = dbs.TableDefs(0)

Indexes on DAO collections are always zero-based. This means that the first element in the collection has an index number of zero. This is important to note if you're used to working with one-based indexes, where the first element has an index number of one.

It's important to note that an object's position within a collection isn't fixed. It can change as objects are added to and removed from the collection. You shouldn't refer to objects according to their position within a collection except when referring to all members within a loop.

Using the ! and . (Dot) Operator Syntax

In DAO syntax, you separate parts of your object references by using either the ! operator or the . (dot) operator. When to use the ! operator and when to use the . (dot) operator depends on the context of its use. In general:

• Use the . (dot) operator when what follows is an item created by Microsoft Jet.

• Use the ! operator when what follows is a user-defined item.

For example, to refer to the Name property of a TableDef object (a property created by Microsoft Jet), use the . (dot) operator, as show in the following line of code:

dbs.TableDefs("Customers").Name

However, to refer to a field in a TableDef object (an item that you create), use the ! operator, as follows:

dbs.TableDefs("Customers")!CustomerID

Using Default Collections to Write Shorter Code

Almost all of the DAO objects have default collections. With these default collections, you can use a shorter reference to objects in code. By using the full syntax, you can return a reference to a TableDef object and a Field object, as shown in the following code. In this example, strDbPath is the path to the database, strTableName is the name of the table, and strFieldName is the name of the field:

Dim dbs As Database, tdf As TableDef  Set dbs = OpenDatabase(strDbPath) Set tdf = dbs.TableDefs(strTableName) Set fld = tdf.Fields(strFieldName)

Because the TableDefs collection is the default collection of the Database object, and the Fields collection is the default collection of the TableDef object, you can shorten this code to:

Dim dbs As Database, tdf As TableDef  Set dbs = OpenDatabase(strDbPath) Set tdf = dbs(strTableName) Set fld = tdf(strFieldName)

Note Although using default collections produces shorter code that may be more efficient, it also reduces the readability of the code. It's always advisable to use comments in your code to provide information about what your code is doing.

The following table lists the default collections available in DAO.

Object	Default collection
DBEngine	Workspaces
Workspace	Databases
Database	TableDefs
Container	Documents
QueryDef	Parameters
Recordset	Fields
Relation	Fields
TableDef	Fields
Index	Fields
Group	Users
User	Groups

Using Object Variables

The preceding sections have shown how to return a reference to an object in order to work with that object in Visual Basic. It's possible to use an object reference throughout your code each time you need to refer to a particular object. However, your code runs more quickly if you declare an object variable to represent the object instead. An object variable is a variable that represents an object in Visual Basic. Object variables differ from regular variables in that they have no intrinsic value. They point to an object, not a value. In other words, an object variable points to an object's representation in memory. All object variables that are assigned to an object point to the same object.

There are several advantages to using object variables. By using object variables to refer to objects in your code, you can:

• Avoid typing and maintaining long lines of code that refer to objects. By using object variables, you can set a reference to an object once, and then continually refer to the object variable instead of making repeated explicit references to an object through the DAO object hierarchy.

• Write code that is easier to read and maintain.

• Improve the performance of your application. When you use object variables, DAO doesn't need to repeatedly parse and resolve recurrent long object references. Because DAO has to resolve the reference to the object only once, your application should run marginally faster.

Object variables are a special type of variable supported by Visual Basic. Each object type in DAO has a corresponding object variable type. For example, the Workspace object has a corresponding workspace object variable, the Database object has a corresponding database object variable, and so on.

To use an object variable, first declare the variable with the Dim statement and then use the Set statement to assign an object to it. The syntax is:

Dim ObjectVariable As ObjectType

Set ObjectVariable=SomeDataAccessObject

For example, the following code, which prints the names of all the fields in a table, uses object variables to refer to Database, TableDef, and Field objects. In this example, strDbPath is the path to the database and strTableName is the name of the table:

Dim dbs As Database Dim tdf As TableDef Dim fld As Field  Set dbs = OpenDatabase(strDbPath) Set tdf = dbs.TableDefs(strTableName) For Each fld In tdf.Fields Debug.Print fld.Name Next fld

Closing Object Variables

Four of the DAO objects are considered temporary objects. That is, they exist only in memory and are never permanently saved as part of the database file. These are the Workspace, Database, Connection, and Recordset objects. QueryDef objects can be either permanent or temporary.

Note QueryDef objects are not typically temporary objects. As mentioned earlier, a QueryDef object represents a saved query in the database. However, unlike other permanent objects, if a QueryDef object is created by using DAO, the QueryDef object can be used without first appending it to the QueryDefs collection.

Another slight exception is the Database object. Although it's a temporary object, it does refer to the database file on disk, which is permanent.

Because these objects exist in memory, you should close them explicitly when your code no longer needs them. To close an object explicitly, use the Close method and then set the object variable to Nothing.

Closing Workspace Objects

Prior to Microsoft Jet version 2.0, you couldn't close a Workspace object that contained any open Database objects. In Microsoft Jet 3.0 and 3.5, if you close a Workspace object, Microsoft Jet closes all the Workspace object's Database objects and rolls back any pending transactions.

Microsoft Access Users In Microsoft Access, a default workspace, Workspaces(0), is automatically created when you open a database through the user interface. Microsoft Access ignores attempts to close this default Workspace.

Closing Database Objects

If you open a database with the OpenDatabase method, you should close it explicitly when you are done using it. To close a database explicitly, first use the Close method to close the database, then set the Database object variable to Nothing.

In DAO 3.0 and 3.5, if you explicitly close a Database object that has an open Recordset object with pending edits or uncommitted transactions, the edits are canceled, the transactions are rolled back, and the Recordset object is closed. If you implicitly close the same Database object (by exiting a procedure that has the Database object as a local object variable), the Recordset object stays open.

Note This behavior is different from the behavior of DAO version 2.x. In DAO 2.x, if you explicitly close a Database object that has an open Recordset object, an error occurs. If you implicitly close the same Database object, the Recordset object is closed as well. DAO 2.x had Dynaset objects (replaced by dynaset-type Recordset objects in DAO 3.0). If you explicitly or implicitly close a Database object that has an open Dynaset object, the Dynaset object stays open.

Closing Recordset Objects

You should always close a Recordset object after you finish using it. Close the Recordset object by using its Close method. Then set the Recordset object variable equal to Nothing.

Determining the Boundaries of Collections

To work effectively with collections, it's important to understand how collections are ordered and how to determine their boundaries. To determine the boundaries of a collection, use the Count property of the collection. The value of the Count property reflects the number of objects in the collection. For example, if a collection's Count property returns 5, then there are five objects in that collection.

However, remember that DAO collections are zero-based. This means that members of DAO collections are indexed starting with 0, with the first object in the collection having an index number of 0. Therefore, when your code compares the value of the Count property to the current index position of the collection, it must subtract one. For example, the following code illustrates how to use the Count property to determine the number of TableDef objects in a database and to print the name of each table. In this code, strDbPath is the path to the database:

Dim dbs As Database Dim intX As Integer  Set dbs = OpenDatabase(strDbPath) For intX = 0 To dbs.TableDefs.Count - 1 Debug.Print dbs.TableDefs(intX).Name Next intX

You can see how this example subtracts one from the Count property to make sure the comparison works.

You can simplify your code by using the For Each…Next statement instead of a For...Next statement. The following code accomplishes the same task, but without requiring you to handle collection boundaries. In this example, strDbPath is the path to the database:

Dim dbs As Database Dim tdf As TableDef  Set dbs = OpenDatabase(strDbPath) For Each tdf In dbs.TableDefs Debug.Print tdf.Name Next tdf

The first example above shows how you can refer to an object by using its location within a collection. When you add or delete objects from a collection, the Count property changes and no longer provides a reliable means of specifying an object's location in a collection. Therefore, you should not use the Count property to determine the boundaries of a collection with code that loops through and deletes objects.

DAO Properties

All DAO objects have properties that define their characteristics. You can retrieve the values of properties to see the characteristics of an object, and you can change the values of properties to change the characteristics of an object.

Properties are represented by Property objects. Property objects themselves contain properties, as described in the following table.

For information about	See
Name	Text
Value	Variant
Type	Integer
Inherited	Boolean

Types of Properties

Microsoft Jet identifies two types of properties. Built-in properties are those automatically created and maintained by DAO. For example, when you create a TableDef object, DAO automatically creates a set of properties for the table, including the Name property and the Connect property.

User-defined properties are those properties you create and add to an object. For example, you may want each field in your table to store a description that explains what the field is for. By adding a user-defined property called Description to the field object's Properties collection, you can store and retrieve this information.

Replication Properties

When you create a replicated database, Microsoft Jet automatically adds new properties to tables, indexes, and application-specific objects in the Containers collection. These properties are used by Microsoft Jet to determine whether or not you can replicate objects.

See Also For more information about replicating databases, see Chapter 7, "Database Replication."

The Properties Collection

Each DAO object has an associated Properties collection. This collection contains the Property objects that identify each characteristic of the object. Like other collections, you can iterate through the collection and refer to specific elements. You refer to Property objects and Properties collections by using the same syntax as you do for other DAO collections:

• Properties("PropertyName")

• Properties(expression) where expression is a string expression or variable that is the name of a property

• Properties(index) where index is the property's position within the collection

For example, to refer to the value of the Type property of the CustomerID field in the Customers table, you could use any of the following lines of code, where dbs is a Database object:

dbs.TableDefs("Customers").Fields("CustomerID").Properties("Type").Value dbs.TableDefs("Customers").Fields("CustomerID").Properties(strType).Value dbs.TableDefs("Customers").Fields("CustomerID").Properties(3).Value

In the second line of code, a string variable, strType, is set to the value of "Type".

In the last line, the code assumes that the Type property is always going to be in the fourth position in the Properties collection. As mentioned earlier in the "Objects and Collections" section, you should not rely on an object's position within its collection. The only time you would refer to a property by its index number is when you are iterating through all the Properties in a collection.

Read-Only Properties

When you create a DAO object in code, you set its properties to define its characteristics. You then append the object to its collection to make it a permanent part of the database. However, some properties become read-only after the property's parent is appended. For example, after you append a new TableDef object and its Field objects to its collection, the Type property becomes read-only. To change these properties, you must delete the object and re-create it.

Setting Bitwise Properties

Some properties store bitwise values. To compare bitwise values you use the bitwise comparison operators: And, Eqv, Not, Or, Xor, and Imp. A bitwise comparison is a bit-by-bit comparison of the individual bits of a value, rather than the value as a whole. To correctly store and retrieve values for these properties, you must use bitwise operators. For example, the Attributes property of a TableDef object defines certain characteristics such as how the table is linked. To check this property for a TableDef object, you may use the following line of code, where tdf represents the TableDef object.

If (tdf.Attributes And dbAttachedTable) = dbAttachedTable Then ' Perform some action on TableDef object. . . . End If

If the result is a nonzero value, then the attribute dbAttachedTable is set for the table. If the result is zero, then that particular attribute isn't set.

The previous example uses the And operator to retrieve bitwise values. You can use the Or operator to set bitwise values, and the And and the Not operators together to clear them.

Microsoft Access Users When you create an object through DAO by using Microsoft Jet, only the standard built-in properties are created in the new object's Properties collection. However, when Microsoft Access creates a Microsoft Jet object, it may add several user-defined properties to objects. These properties are a special case of user-defined properties known as application-defined properties. For example, when you create a table in the Microsoft Access user interface, and type a value in the Description field, it automatically adds a new property to the TableDef object to represent the description.

Microsoft Access doesn't automatically add these application-defined properties to new objects. For example, when you create a new table through the user interface in Microsoft Access, the Description and Format properties are not created unless you type a value in the Description and Format fields. This can cause a problem because when you try to read the value of these properties on a table where they have not been specified, a run-time error occurs. The technique for avoiding this problem, and for writing more robust property-retrieval code in general, is to trap for errors when looking at Property objects. For more information, see "Retrieving Properties" later in this chapter.

You can add properties not created by Microsoft Access through the user interface, by using the user interface, or by creating the property in code and appending it to the appropriate object's Properties collection. For example, if you want to include a Description property in tables you create from code, you must add it to the Properties collection by using the CreateProperty method of the TableDef object.

DAO Methods

Methods are procedures that act on objects. Most DAO objects have associated methods you can use to interact with the object in useful ways. Methods are similar to procedures that you create in your code, with the important distinction that methods are explicitly tied to an object, whereas your procedures can generally be called independent of a specific object.

Like a procedure, a method can return a value or return nothing. If the method returns a value, use the equal sign (=), as shown in the following syntax:

variable=object.method [arglist]

If the method doesn't return a value, omit the variable, equal sign (=), and the parentheses surrounding the argument list, as follows:

object.method arglist

Using DAO

So far, this chapter has discussed DAO in general terms, describing its syntax and architecture. In this section, those ideas are put to work.

Note that this section introduces the creation and manipulation of objects. It doesn't fully cover all of the DAO operations you may want to perform. For complete information, see the chapters listed in the following table.

For information about	See
Using data definition objects (databases, tables, fields, and so on)	Chapter 3, "Data Definition and Integrity"
Recordset objects that represent actual data	Chapter 5, "Working with Records and Fields"
Setting multiuser properties	Chapter 6, "Creating Multiuser Applications"
Using ODBCDirect workspaces	Chapter 9, "Developing Client/Server Applications"
Using groups, users, and permissions to control access to your application and its data	Chapter 10, "Managing Security"

Creating Database Objects

The first operation you are likely to perform using DAO involves data definition: creating objects, assigning characteristics to objects by using properties, and interrogating the structure of existing objects.

Creating a new object is a two-step process. First you create the object and define its characteristics, and then you append the object to its collection. The process of appending an object to a collection makes it a permanent or persistent part of the database. Additionally, if the object you're creating contains child objects, you first create the parent object, then create and append the child object, and finally, append the parent object to its collection.

Note Unlike other DAO objects, Workspace objects can be created and then appended to the Workspaces collection, but they don't become a permanent part of the database. When you exit the application, all Workspace objects are not saved. It's important to remember that a Workspace object is only a "space" used to identify a specific user and transaction sequence. Recordset objects work in the same way.

Also, when you create a new Database object by using the CreateDatabase method, the new database automatically becomes a persistent object on the drive where it's created. You don't have to explicitly append it to the Databases collection to make it persistent.

You can also create linked tables, which are tables stored in files outside the current database. Because Microsoft Jet supports a variety of external data sources, you are not limited to linking to only Microsoft Jet databases.

See Also For more information, see Chapter 8, "Accessing External Data."

Creating Your Own Properties

Earlier, this chapter introduced user-defined properties, which you can use to store and retrieve your own information about an object. DAO is extensible by using these properties. The following function tries to set a property on an object. If the property doesn't exist in that object's Properties collection, then the function creates the property and appends it to the collection:

Function SetCustomProperty(obj As Object, strName As String, _ intType As Integer, varSetting As Variant) As Boolean ' This procedure attempts to set a property on an object. ' If the property does not exist in the object's Properties collection, ' then the procedure creates the object. Dim prp As Property Const conPropNotFound As Integer = 3270  On Error GoTo ErrorSetCustomProperty ' Explicitly refer to Properties collection. obj.Properties(strName) = varSetting obj.Properties.Refresh SetCustomProperty = True  ExitSetCustomProperty: Exit Function  ErrorSetCustomProperty: 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 SetCustomProperty = True Resume ExitSetCustomProperty Else MsgBox Err & ": " & vbCrLf & Err.Description SetCustomProperty = False Resume ExitSetCustomProperty End If End Function

For example, to create a property called DateLastModified on each table in a database, you may call this function from the following code, which creates the property on each table that isn't a system object. In this example, strDbPath is the path to the database:

Dim dbs As Database, tdf As TableDef ' Define constant to specify system object. Const conSystemObject As Long = -2147483648  Set dbs = OpenDatabase(strDbPath) For Each tdf In dbs.TableDefs ' Check to see whether table is system object or hidden. If Not (tdf.Attributes And conSystemObject) = conSystemObject Then If Not (tdf.Attributes And dbHiddenObject) = dbHiddenObject Then ' Set property. SetCustomProperty tdf, "DateLastModified", dbDate, Now End If End If Next tdf

Of course, simply adding a property doesn't automatically update and maintain it. In the previous example, the DateLastModified property is added to the TableDef object, but that's the extent of what the code does. Your application must programmatically update the value of this property when appropriate.

Adding Groups and Users

Microsoft Jet implements security through users, groups, and permissions settings on specific objects. You can use DAO to create users and groups in code, and to assign permissions to objects.

See Also For more information, see Chapter 10, "Managing Security."

Using the Error Object

The Errors collection contains Error objects that represent the Microsoft Jet errors that have occurred. You can use these objects to determine if errors have occurred, and then to work around them. The following code attempts to open a database that doesn't exist, and then displays the errors that result:

Dim dbs As Database Dim errX As Error  ' Disable error handling. On Error Resume Next  ' Try to open a nonexistent database. Set dbs = OpenDatabase("XYZ123.456")  ' Look at the errors generated. For Each errX In DBEngine.Errors Debug.Print errX.Description Debug.Print errX.Number Debug.Print errX.Source Next errX

The Error object is unlike the error variables and functions in Visual Basic in that more than one error can be generated by a single operation. Also, objects in the Errors collection are appended in a manner different from the other DAO collections. The most detailed errors are placed at the end of the collection, and the most general errors are placed at the beginning.

The set of Error objects in the Errors collection describes one error. The first Error object is the lowest-level error, the second the next-highest level, and so on. For example, if an ODBC error occurs while trying to open a Recordset object, the last Error object contains the DAO error indicating that the object can't be opened. The first Error object contains the lowest-level ODBC error. Subsequent errors contain the ODBC errors returned by the various layers of ODBC.

Interrogating Existing Objects in Your Database

In addition to creating objects, you can look at existing objects and determine their structures. When you retrieve structures, you iterate (loop) through collections. For example, you can iterate through the TableDefs collection to find all the tables, and then iterate through a specific TableDef object's Fields collection to find information about the table's fields.

Refreshing Collections

As objects are appended to and deleted from collections, DAO usually keeps track of the collection. However, there are situations when DAO doesn't know the current state of a collection. This occurs when:

• Users are modifying a shared database's object in a multiuser environment.

• SQL statements alter structures (for example, "DROP TABLE Customers").

• Changes are made through a host program's user interface. Note that this isn't the case for applications that use DAO directly, such as Visual Basic and Microsoft Excel.

If you want to make sure that you're looking at the most up-to-date version of a collection, refresh the collection. This is done by using the Refresh method of the collection. The syntax for the Refresh method is:

Collection.Refresh

Collection is the name of the collection you want to refresh. For example, if your code needs a complete list of TableDef objects in the database, you may want to refresh the collection before using it. You can accomplish this with the following line of code, where dbs is a pointer to the database:

dbs.TableDefs.Refresh

Use the Refresh method only when necessary because it may take time to refill the collection.

Listing the Objects in a Database

To get a list of the objects in a database, you iterate through each Collection object. The following example iterates through each TableDef, QueryDef, Relation, and Container object in a database, and prints the object name to the Debug window. In this example, strDbPath is the path to the database:

Dim dbs As Database Dim tdf As TableDef, qry As QueryDef Dim rel As Relation, ctr As Container  Set dbs = OpenDatabase(strDbPath)  Debug.Print "Tables: " For Each tdf In dbs.TableDefs Debug.Print tdf.Name Next tdf  Debug.Print "Querys: " For Each qry In dbs.QueryDefs Debug.Print qry.Name Next qry  Debug.Print "Relations: " For Each rel In dbs.Relations Debug.Print rel.Name Next rel  Debug.Print "Containers: " For Each ctr In dbs.Containers Debug.Print ctr.Name Next ctr

Retrieving Properties

To identify the properties associated with an object, you write code to iterate through the object's Properties collection, retrieving the information you want. Some special cases when referencing properties may be problematic:

• Some properties are not readable. The Password property of the User object is a good example. You can set the value of the property through code, but you can't retrieve the value. (If you could, it would be an obvious hole in the Microsoft Jet security model.)

• Some properties are readable at some times, but not at others. For example, you can't read the Value property of a Field object in a recordset if there is no current record in the recordset.

• Certain properties that you are explicitly looking for may not exist. For example, if you're using Microsoft Access, you may have code that retrieves the Description property of a TableDef object. However, unless you explicitly add a description to the table through the Microsoft Access user interface or through DAO code, this user-defined property doesn't exist, and a run-time error occurs when the code executes.

The following code displays each of the properties of each User object in the current Workspace. After it has tried to retrieve the property value, it checks to see if an error has occurred, and if so, checks the specific error number. If the error is anticipated, as in the case of trying to retrieve a write-only property (error 3251), error text is printed. If the error isn't anticipated, the code displays a message to the user.

Dim usr As User Dim prp As Property Dim varValue As Variant  For Each usr In Workspaces(0).Users For Each prp In usr.Properties ' Disable error handling. On Error Resume Next varValue = prp.Value ' See if an error occurred. Select Case Err Case 3251 ' Operation not supported error. Debug.Print prp.Name & ": <Error " & Err _ & ": " & Err.Description & ">" Case 0 ' No error. Debug.Print prp.Name & ": " & varValue Case Else ' Unanticipated error. MsgBox "Error: " & Err & vbCrLf & Err.Description End Select Err.Clear ' Re-enable error handling. On Error GoTo 0 Next prp  Debug.Print Next usr

Getting a Table Structure

The concepts demonstrated in the previous two examples are included in the following example, which prints the properties of a TableDef object, the names of its fields, and the properties of each Field object and their values. The first procedure iterates through the Properties collection for the TableDef object and each Field object, and then calls the GetProperty function for each Property object in each collection. The GetProperty function returns a string containing the property's name and value. If reading the property's Value property returns an error, then the error message is included in the string:

Sub GetTableInfo(strDbName As String, strTableName As String) Dim dbs As Database Dim tdf As TableDef, fld As Field, prp As Property  ' Open database. Set dbs = OpenDatabase(strDbName) Set tdf = dbs.TableDefs(strTableName) With tdf ' Print table properties. Debug.Print "Properties for table '" & strTableName & "':" For Each prp In tdf.Properties Debug.Print GetProperty(prp) Next prp Debug.Print  ' Print fields and field properties. Debug.Print "Fields and field properties for table '" & _ strTableName & "':" For Each fld In .Fields Debug.Print "Field: " & fld.Name For Each prp In fld.Properties Debug.Print " " & GetProperty(prp) Next prp Debug.Print Next fld End With End Sub  Function GetProperty(prp As Property) As String ' This procedure checks whether a property has a value. ' It returns either the value or an appropriate ' error string. Dim varValue As Variant  ' Disable error handling. On Error Resume Next ' Attempt to retrieve the property value. varValue = prp.Value ' Check for an error. If Err Then varValue = "<Error: " & Err.Description & ">" End If ' Re-enable error handling. On Error GoTo 0 ' Return string. GetProperty = "Property: " & prp.Name & vbTab & vbTab & "Value: " & varValue End Function

Creating Temporary Queries with DAO

You can create temporary QueryDef objects that you can use in code. You create a temporary QueryDef object in your code by using a zero-length string ("") in place of the query name. You can then execute the QueryDef object and close it. Temporary QueryDef objects are well-suited to creating and running update, parameterized, and pass-through queries.

The following example shows how you may use a temporary QueryDef object to execute an action query that updates data as defined by an SQL statement passed to the procedure in the strSQL variable. In this example, strDbPath is the path to the database, and strSQL is an SQL statement for an update query:

Dim dbs As Database Dim qdf As QueryDef  Set dbs = OpenDatabase(strDbPath) Set qdf = dbs.CreateQueryDef("", strSQL) qdf.Execute qdf.Close

For example, you may supply the following values for strDbPath and strSQL:

strDbPath = "C:\JetBook\Samples\NorthwindTables.mdb" strSQL = "UPDATE Products INNER JOIN Suppliers " & _ "ON Products.SupplierID = Suppliers.SupplierID " & _ "SET Products.UnitPrice = [UnitPrice]*1.1 " & _ "WHERE Suppliers.CompanyName=""Exotic Liquids"";

This update query increases the unit price of all Exotic Liquids products in the Products table by 10 percent. After you run the update query, close the temporary QueryDef object because it's no longer needed.

Note that if the SQL statement specified a select query rather than an update query, you need to open a Recordset object on the QueryDef object in order to view the result set. For example, suppose you specified a value for strSQL as follows:

strSQL = "SELECT ProductName, UnitPrice FROM Products " & _ "WHERE UnitPrice >= 40 ORDER BY ProductName;"

Rather than executing the temporary QueryDef object, you can open a Recordset object on it, then close the temporary QueryDef object and manipulate the Recordset object, as follows:

Dim dbs As Database Dim qdf As QueryDef Dim rst As Recordset, fld As Field  Set dbs = OpenDatabase(strDbPath) Set qdf = dbs.CreateQueryDef("", strSQL) Set rst = qdf.OpenRecordset qdf.Close Do Until rst.EOF For Each fld In rst.Fields Debug.Print fld.Value Next fld rst.MoveNext Loop

Modifying Existing Database Objects

You can use DAO to modify the structure of existing objects. However, there are things that you can't programmatically alter by using DAO. For example, you can't use DAO to change the Type property of a Field object in a table. Because this would change the data type of the field and require a conversion of existing data in the table, you must delete the Field object and then re-create it, specifying the new data type.

See Also For complete information about modifying database objects, see Chapter 3, "Data Definition and Integrity."

Manipulating Data

You can use DAO to manipulate data in your database. In DAO, you use the Recordset object to interact with data stored in the database. Recordset objects are extremely powerful because they provide a great deal of flexibility in working with data.

Recordset objects are different from most other DAO objects in that they exist only as temporary objects. Even though the method you use to create Recordset objects is called OpenRecordset, you don't actually open existing Recordset objects; you create new ones. After you close a Recordset object, it no longer exists.

To create a Recordset, you use the OpenRecordset method based on a Database object. The syntax for the OpenRecordset method is:

Set recordset = object.OpenRecordset(source[, type[, options[, lockedits]]])

In this syntax, recordset is an object variable that represents the Recordset object you want to open. Object is an object variable that represents an existing object from which you want to create the new Recordset object. The source argument is the source of the data, such as a table name, a query name, or an SQL statement that returns records. You can open Recordset objects:

• Directly on tables in the database.

• On tables in other databases.

• On tables linked to external data sources such as ODBC or ISAM.

• On queries.

• Based on SQL strings that you dynamically create in code.

• On other Recordset objects.

The type argument is a numeric constant denoting the type of Recordset to open. There are five types of Recordset objects:

• Table-type Recordset object. A representation in code of a table in the current database. You can use table-type Recordset objects to retrieve, add, change, or delete records.

• Dynaset-type Recordset object. The result of a query that can have updatable records. A dynaset is a dynamic set of records that you can use to add, change, or delete records from an underlying database table or tables. A dynaset can contain fields from one or more tables in a database.

• Snapshot-type Recordset object. A static copy of a set of records that you can use to find data or generate reports. A snapshot can contain fields from one or more tables in a database, but it can't be updated.

• Forward-only-type Recordset object. This type of Recordset object is identical to a snapshot-type Recordset object, 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.

  Note DAO 3.5 introduced the dbOpenForwardOnly constant as a new value for the type argument of the OpenRecordset method. This Recordset object behaves the same as a DAO 3.0 snapshot-type Recordset object opened with the dbForwardOnly option.

• Dynamic-type Recordset object. This type of Recordset object represents a query result set from one or more base tables in which you can add, change, or delete records from a row-returning query. Further, records that users add, delete, or edit in the base tables also appear in your Recordset object. This Recordset object is similar to an ODBC dynamic cursor and is available in ODBCDirect workspaces only.

You can set the options argument to a combination of constants that specify characteristics of the new Recordset object. For example, with the options argument, you can specify:

• That users can't modify or add records in the table or tables you are accessing.

• That users can't view records (table-type Recordset only).

• That users can append new records, but can't edit or delete existing records (dynaset-type Recordset only).

• That inconsistent updates are allowed (dynaset-type Recordset only).

• That only consistent updates are allowed (dynaset-type Recordset only).

Note When you don't use the options argument, you must specify 0 for it.

The lockedits argument determines the locking for the new Recordset object. For example, you can:

• Prevent users from making changes to the Recordset object (default setting for ODBCDirect workspaces).

• Use pessimistic locking to determine how changes are made to the Recordset object in a multiuser environment. The page containing the record you're editing is locked as soon as you use the Edit method (default for Microsoft Jet workspaces).

• Use optimistic locking to determine how changes are made to the Recordset object in a multiuser environment. The page containing the record you're editing isn't locked until the Update method executes.

Using Private DBEngine Objects

One advantage that Microsoft Visual C++ and Visual Basic developers have over Microsoft Access developers is that when their program starts, the DBEngine object isn't yet initialized. This allows the program to set important properties such as the SystemDB property (which sets or returns the path for the current location of the workgroup information file), and the DefaultUser and DefaultPassword properties (which set the user name and password to use for default logon). In Microsoft Access, however, the DBEngine object is already initialized when you start, and none of these properties can be changed. Even in Visual Basic, you may want to open a database that uses a different workgroup information file, without having to start a new application through Automation to do so.

There is a new object called a PrivDBEngine object, which is a new, uninitialized copy of the top-level DBEngine object, with its own objects and collections beneath it. You can use the PrivDBEngine object to open a database that uses a different workgroup information file, to determine whether a database is opened exclusively, and to determine whether you can open a database exclusively.

To create a private DBEngine object, declare an object variable as type PrivDBEngine, and then use the Set statement with the New keyword, as shown in the following code:

Dim dbe As PrivDBEngine  Set dbe = New PrivDBEngine

Using a Different Workgroup Information File

As an example, you may have some data in a secured copy of the Northwind database that uses a different workgroup information file than your user may be using by default. To open this database, you can use code such as the following fragment:

Dim dbe As PrivDBEngine Dim wrk As Workspace Dim dbs As Database  Set dbe = New PrivDBEngine dbe.SystemDB = "C:\MyApp\MyWorkgroupFile.mdw" dbe.DefaultUser = "SecureUser" Set wrk = dbe.Workspaces(0) Set dbs = wrk.OpenDatabase("C:\Samples\Secured Northwind.mdb")

Without the PrivDBEngine object, you would have to start another instance of your application or Microsoft Access in order to access a database that uses another workgroup information file. Creating a PrivDBEngine object is significantly faster.

Determining Whether the Current Database Is Open Exclusively

Determining whether the current database is open exclusively isn't simply a matter of trying to open the database. If you have the database open exclusively and try to open it again, you won't receive a run-time error as you may expect. Actually, you can open a database as many times as you want to, exclusively or not, with the same DBEngine object.

To determine whether the current database is open exclusively, you can try to open the database by using the PrivDBEngine object, as shown in the following code:

Function IsDbOpenedExclusively(strDbPath As String) As Boolean On Error Resume Next Dim dbe As PrivDBEngine Dim wrk As Workspace Dim dbs As Database Const conFileInUse = 3045 Const conDBOpenedExclusively = 3356  ' Return reference to private DBEngine object. Set dbe = New PrivDBEngine ' Return reference to default workspace. Set wrk = dbe.Workspaces(0) ' Attempt to open database. Set dbs = wrk.OpenDatabase(strDbPath) ' If reference to database isn't returned, check error. If dbs Is Nothing Then ' If error indicates database is open exclusively, return True. If (Err = conFileInUse Or Err = conDBOpenedExclusively) Then IsDbOpenedExclusively = True ' If unanticipated error occurs, display message. Else MsgBox "Error: " & Err & ": " & vbCrLf & Err.Description End If ' If reference to database is returned, it must not be opened ' exclusively by any other user. Else IsDbOpenedExclusively = False dbs.Close Set dbs = Nothing End If End Function

If this function generates error 3045 or error 3356, then it returns True because these errors indicate that the database is already opened exclusively.

Determining Whether You Can Open a Database Exclusively

You may also want to determine whether your own code is able to open a database exclusively. This information can be helpful with many operations, such as:

• Repairing a database.

• Compacting a database.

• Saving your Microsoft Access 97 database as an MDE file.

• Converting a database into a Design Master replica.

• Converting or enabling a database from a previous version.

In addition, the user that is currently logged on may not have permission to open the database exclusively.

See Also For more information about Design Master replicas, see Chapter 7, "Database Replication." For more information about user permissions, see Chapter 10, "Managing Security."

To determine whether you can open a database exclusively, use the PrivDBEngine object in code that is similar to the IsDbOpenedExclusively function shown in the preceding example:

Function CanOpenDbExclusively(strDbPath As String) As Boolean On Error Resume Next Dim dbe As PrivDBEngine Dim wrk As Workspace Dim dbs As Database Const conFileInUse = 3045 Const conDBOpenedExclusively = 3356  Set dbe = New PrivDBEngine Set wrk = dbe.Workspaces(0) Set dbs = wrk.OpenDatabase(strDbPath, True) If dbs Is Nothing Then ' If error is unanticipated, display message. If (Err = conFileInUse Or Err = conDBOpenedExclusively) Then ' If database cannot be opened exclusively, return False. CanOpenDbExclusively = False Else MsgBox "Error: " & Err & ": " & vbCrLf & Err.Description End If Else ' If database can be opened exclusively, return True. CanOpenDbExclusively = True dbs.Close End If End Function

This function returns True if you can open the database exclusively.

Features New to DAO 3.0 and 3.5

This section outlines the functionality new to DAO in 3.0 and 3.5.

See Also For more information about new features, search the DAO Help index for "DAO, new features" or for the name of the language element you want information about.

Accessing DAO

DAO is a component that supports Automation. Any licensed program that can call components that support Automation can access DAO functionality. Unlike regular dynamic-link libraries (DLLs) that expose calls but no details about them, components that support Automation contain a type library that describes every object, method, property, and so forth, in the DLL. Other components that support Automation, such as Microsoft Access, Visual Basic, Microsoft Excel, Microsoft Word, or Microsoft PowerPoint can read the DAO type library and make the appropriate calls to the DLL.

In DAO 3.5, there is a new client/server connection mode called ODBCDirect. You can use the ODBCDirect technology to establish a direct connection to an ODBC data source without having to load Microsoft Jet in memory. ODBCDirect is useful in situations where the specific features of ODBC are required. For more information about ODBCDirect see, Chapter 9, "Developing Client/Server Applications."

Microsoft Access, which relies on Microsoft Jet for its database functionality, has calls to Microsoft Jet written into its internal code, and doesn't call the DAO DLL except when Visual Basic code in a Microsoft Access database calls DAO. In other words, when you display a table through the Microsoft Access user interface, Microsoft Access calls Microsoft Jet directly through hard-coded routines. When you open a table by using Visual Basic code, Microsoft Access hands the request to the DAO component.

DBEngine Object

The IniPath property is set to the path to the Windows registry because .ini files are no longer used by Microsoft Jet. Use the SystemDB property of the Database object to point to the workgroup information file you want to use. The SystemDB property sets or returns the path for the current location of the workgroup information file. DAO 3.5 added the SetOption method to the DBEngine object, which you can use to override registry values during the current instance of DAO.

Workspace Object

You can assign a password to a database. You can specify the password to open a password-protected database in a workspace by using the password argument of the CreateWorkspace method.

In DAO 3.5, the Workspace object now also contains the Connection object, which contains information about a connection to an ODBC data source that uses an ODBCDirect workspace. For more information about ODBCDirect workspaces, see Chapter 9, "Developing Client/Server Applications."

Database Object

There are several new properties that you use when working with replication and Database objects: DesignMasterID, DefaultPartner, MostRecentSyncPartner, Replicable, and ReplicaID. Note that only ReplicaID and DesignMasterID exist on every database, whether it's replicable or not, and the Replicable property exists only if the user creates and appends it. If a database is made replicable (by appending the Replicable property), then the additional properties DefaultPartner and LastUpdated are created by Microsoft Jet.

In addition, Microsoft Jet 3.5 supports partial replication. If you want to replicate only part of the data in a database, you can create partial replicas, which contain a subset of the data in a full replica. You use the PopulatePartial method of the Database object to synchronize any changes in a partial replica with the full replica, clear all records in the partial replica, and then repopulate the partial replica based on the current replica filters.

With the Synchronize method, you can synchronize the current database with a replica database. The MakeReplica method creates a new replica based on the current replicable database.

See Also For more information about replication, see Chapter 7, "Database Replication."

You can use the NewPassword method to assign a new password to the database.

The behavior of the Database object's Close method has changed in the way it closes any pending Recordset changes. For more information, see "Closing Object Variables" earlier in this chapter.

Container Object

The Container object supports the AllPermissions property, which returns all the permissions that apply to the current UserName property of the Container or Document object, including permissions that are specific to the user as well as the permissions a user inherits from membership in groups. The AllPermissions property differs from the Permissions property in that it returns all permissions for an object, including group permissions.

Document Object

The Document object supports the AllPermissions property. For information about the AllPermissions property, see the preceding section, "Container Object."

You can add properties to and delete properties from the Document object.

QueryDef Object

DAO 3.5 supports the new MaxRecords property on QueryDef objects. You can use the MaxRecords property to specify the maximum number of records to return from a query.

Recordset Object

You can use the GetRows method to retrieve records from a Recordset object. The data that is returned is stored in a Variant array.

The AbsolutePosition property, which you can use to set or return the relative record number of a Recordset object's current record, is now supported and documented. The PercentPosition property, which you can use to set or return a value indicating the approximate location of the current record in the Recordset object based on a percentage of the records in the Recordset, now supports TableDef objects without indexes.

Relation Object

DAO 3.5 adds the PartialReplica property to the Relation object. You use the PartialReplica property to set or return a value on a Relation object indicating whether that relationship is considered when populating a partial replica from a full replica.

TableDef Object

For replication, the Replicable, KeepLocal, and ConflictTable properties have been added. For partial replication, the ReplicaFilter property has been added to 3.5 TableDef objects.

Field Object

The FieldSize property of the Field object replaces the FieldSize method used in previous versions. You use the FieldSize property to return the size (in bytes) of a Field object in the Fields collection of a Recordset object.

All Objects

You can specify a 3.5-only DAO type library that removes support for obsolete objects, methods, and properties. For more information, see the next section, "Compatibility Issues."

Compatibility Issues

As DAO has evolved, new objects, methods, and properties have been introduced that render their prior equivalents obsolete. To enhance performance and decrease memory usage, future versions of Microsoft Jet may eliminate support for these obsolete language elements. In order to ease the conversion of applications that use the older objects, methods, and properties, the DAO350.dll file contains two type libraries: the DAO 2.5 type library and the DAO 3.5 type library. These type libraries define the properties and methods that DAO can use.

The DAO 2.5 type library is backward compatible with previous versions of Microsoft Jet and supports all previous objects, methods, and properties. Use this type library if you want existing DAO code to work with Microsoft Jet 3.0 or 3.5 without conversion.

The DAO 3.5 type library removes support for obsolete objects, methods, and properties. Because they are not supported, use of these constructs while the DAO 3.5 type library is in use causes compile errors in your code. The benefit of this behavior is that it allows you to verify that your programs use the latest DAO features by quickly identifying code that uses outdated methods and properties.

In Microsoft Access 97, Visual Basic 5.0, Microsoft Excel 97, and Microsoft Project 97, the Tools menu contains a References command when you are in a code module. Clicking this command gives you a list of object libraries and databases that are available. Two versions of DAO are now available: the DAO 2.5/3.5 compatibility library and DAO 3.5 object library.

By clicking DAO 3.5 Object Library and then recompiling, you can identify any obsolete DAO code with compile errors that place the cursor at the line of code that caused the error.

When you create a new database, the DAO 3.5 Object Library is automatically selected. This ensures that all new DAO code you create uses the new objects. Note that you can't use both libraries at once.

See Also For a list of obsolete DAO features and their replacement features, search the DAO Help index for "DAO, obsolete features in."

Optimizing DAO

When using DAO, you can apply specific techniques to your code to improve its performance. This introductory chapter covers only optimizations that are intrinsic to the operation of DAO as a programming interface. The chapters listed in the following table contain additional information about optimizing performance.

To optimize	See
Accessing local data	Chapter 5, "Working with Records and Fields"
Multiuser environments	Chapter 6, "Creating Multiuser Applications"
Accessing external ISAM data	Chapter 8, "Accessing External Data"
Accessing ODBC data	Chapter 9, "Developing Client/Server Applications"
Performance	Chapter 13, "Optimizing Performance"

Few absolutes exist in the area of performance optimization. While a technique may work well in one piece of code, it may slow down other code. As with any optimization tips, use the following techniques judiciously. Try them in your code, and be sure to verify that they actually improve performance. Additionally, by concentrating on the 10 percent of your code that is executed most frequently, you will achieve better results than spending a lot of time tuning code that is rarely used.

Finally, the techniques shown in this chapter relate to using DAO in a generic sense, or demonstrate good coding practices. Usually, the most important optimizations involve your data, and reading and writing data to the disk. As a general rule, you should concentrate 95 percent of your optimization efforts on disk input/output-related code, and 5 percent on the type of optimizations shown here.

Caching References to Objects and Collections

When you refer to an object or a collection more than once, you can improve performance by caching a reference to that object or collection. There are three ways to do this:

• By returning a reference to an object or collection and assigning it to an object variable

• By using the For Each…Next statement when you refer to each object in a collection

• By using the With statement when you refer to an object and its properties, methods, or collections repeatedly

When you refer to an object or collection more than once in your code, you may want to create an object variable and assign a reference to the object to that object variable. Not only does this make your code faster for lengthy operations, it also makes it easier to read.

The following two examples both return the names of all tables in a database, all fields in each table, and the names and values of all properties on those fields. The first example, which stores references to objects in object variables, is considerably faster than the second. In these examples, strDbPath is the path to a database:

' 1 - Faster, easier to read. Dim dbs As Database, tdf As TableDef Dim fld As Field, prp As Property  Set dbs = OpenDatabase(strDbPath) For Each tdf In dbs.TableDefs Debug.Print tdf.Name For Each fld In tdf.Fields Debug.Print fld.Name For Each prp In fld.Properties On Error Resume Next Debug.Print prp.Name, prp.Value Next prp Next fld Next tdf dbs.Close Set dbs = Nothing  ' 2 - Slower, more cumbersome. Dim intW As Integer, intX As Integer, intY As Integer  OpenDatabase strDbPath For intW = 0 To DBEngine(0)(0).TableDefs.Count - 1 Debug.Print DBEngine(0)(0).TableDefs(intW).Name For intX = 0 To DBEngine(0)(0).TableDefs(intW).Fields.Count - 1 Debug.Print DBEngine(0)(0).TableDefs(intW).Fields(intX).Name For intY = 0 To _ DBEngine(0)(0).TableDefs(intW).Fields(intX).Properties.Count - 1 On Error Resume Next Debug.Print _ DBEngine(0)(0).TableDefs(intW).Fields(intX).Properties(intY).Name, _ DBEngine(0)(0).TableDefs(intW).Fields(intX).Properties(intY).Value Next Next Next

You can improve performance by using the For Each…Next and With…End With statements. Both of these cache a reference to an object, so that your code doesn't have to repeatedly retrieve the reference in order to use the object.

The following example uses both of these statements to print all of the values in a recordset. In this example, strDbPath is the path to a database, and strRstSource is the source for a recordset:

Dim dbs As Database, tdf As TableDef Dim rst As Recordset, fld As Field  Set dbs = OpenDatabase(strDbPath) Set rst = dbs.OpenRecordset(strRstSource)  With rst Do Until .EOF For Each fld In rst.Fields Debug.Print fld.Name, fld.Value Next fld .MoveNext Loop Debug.Print End With rst.Close Set rst = Nothing dbs.Close Set dbs = Nothing

The following example uses a For loop rather than the For Each…Next statement, and it refers to the Recordset object variable explicitly each time it's used rather than caching it with the With statement. The performance differences are not apparent for small recordsets, but become more significant with larger recordsets. When strDbPath is the path to the NorthwindTables database and strRstSource is the Orders table, the following example runs several seconds more slowly than the preceding one:

Dim dbs As Database, tdf As TableDef Dim rst As Recordset, fld As Field Dim intC As Integer  Set dbs = OpenDatabase(strDbPath) Set rst = dbs.OpenRecordset(strRstSource)  Do Until rst.EOF For intC = 0 To rst.Fields.Count - 1 Debug.Print rst.Fields(intC).Name, rst.Fields(intC).Value Next rst.MoveNext Loop Debug.Print rst.Close Set rst = Nothing dbs.Close Set dbs = Nothing

Another important consideration for performance is whether or not your object variables are strictly typed. When you strictly type an object variable, you declare the object variable according to the type of object that it will represent. In the following example, the object variables are not strictly typed according to the object type they are to represent. When a variable isn't typed, Visual Basic automatically designates it as type Variant. This example is otherwise identical to the preceding example, but runs at nearly half the speed for the Orders table in the NorthwindTables database:

Dim dbs, tdf Dim rst, fld Dim intC  Set dbs = OpenDatabase(strDbPath) Set rst = dbs.OpenRecordset(strRstSource)  Do Until rst.EOF For intC = 0 To rst.Fields.Count - 1 Debug.Print rst.Fields(intC).Name, _ rst.Fields(intC).Value Next rst.MoveNext Loop Debug.Print rst.Close Set rst = Nothing dbs.Close Set dbs = Nothing

Using Field Object Variables to Refer to Fields In Loops

To speed up iterative processes through large numbers of records by using DAO, declare object variables to refer to Field objects. The following example doesn't use Field object variables to refer to Field objects. Instead, it refers to Field objects in the Fields collection. In this example, strDbPath is the path to the NorthwindTables database:

Dim dbs As Database, rst As Recordset  Set dbs = OpenDatabase(strDbPath) Set rst = dbs.OpenRecordset("Products")  Do Until rst.EOF With rst .Edit .Fields!UnitPrice = rst!UnitPrice * 1.1 .Update .MoveNext End With Loop

This code performs better if you use Field object variables to refer to fields, as shown in the following example. In the following code, a Field object variable refers to the UnitPrice field in the recordset. When you return a reference to the field and assign it to a Field object variable, Visual Basic stores a reference to the field in the variable. The code runs more quickly because Visual Basic doesn't have to return the reference each time the loop iterates. Again, the difference in performance is more noticeable for large recordsets than for small ones:

Dim dbs As Database, rst As Recordset Dim fld As Field  Set dbs = OpenDatabase(strDbPath) Set rst = dbs.OpenRecordset("Products") Set fld = rst!UnitPrice  Do Until rst.EOF With rst .Edit fld = fld * 1.1 .Update .MoveNext End With Loop

Note that this operation is even faster if you use an update query rather than updating a field in the recordset, record by record. The following example uses an update query to accomplish the same task, where strDbPath is the path to the NorthwindTables database:

Dim dbs As Database, strSQL As String  Set dbs = OpenDatabase(strDbPath) strSQL = "UPDATE Products SET Products.UnitPrice = UnitPrice *1.1;" dbs.Execute strSQL

Refreshing Collections Only When Necessary

You can use the Refresh method to get an up-to-date view of a collection and its objects. This is particularly useful in multiuser environments. Unfortunately, this method is a time-consuming operation relative to most other DAO operations and should be used only when absolutely necessary.

As noted earlier in this chapter, the Microsoft Access CurrentDb function refreshes all of the collections in the current database. To improve performance, you can use the DBEngine(0)(0) syntax and refresh collections only as needed.

See Also For more information about refreshing collections, see "Refreshing Collections" earlier in this chapter.

Taking Advantage of Default Collections

Most DAO objects have several collections, one of which is the default collection for that object. You can use abbreviated syntax when referring to default collections. For example:

DBEngine.Workspaces(0).Databases(0)

can be shortened to

DBEngine(0)(0)

This shorter syntax is also slightly faster. Note that when you use default collections, your code may be difficult to read, so you may want to add explanatory comments.

See Also For more information about default collections, see "Using Default Collections to Write Shorter Code" earlier in this chapter.

Replacing Obsolete Language Elements

Previous versions of DAO included some language elements that have been replaced in DAO 3.5. If your code still uses the old language elements, consider updating it to use newer syntax.

In order to use old language elements, you must set a reference to the Microsoft DAO 2.5/3.5 Compatibility Library. This library is larger than the Microsoft DAO 3.5 Object Library, so it takes longer to load. Your application will run more quickly if you use only the elements available in DAO 3.5. Additionally, Microsoft DAO often maps older language elements to newer elements internally. If you use the newer elements, you can avoid calling the extra mapping code.

See Also For a list of obsolete DAO features and their replacement features, search the DAO Help index for "DAO, obsolete features in."