Figure 14.2 shows an overview of the DAO model for the Jet 4.0 Database Engine. At the top of the hierarchy is the Microsoft Jet Database Engine, referred to as the DBEngine object. The DBEngine object contains all the other objects that are part of the hierarchy. The DBEngine object is the only object in the hierarchy that does not have an associated collection.
Each object within the DAO model is important because you will manipulate the various objects at runtime using code to accomplish the tasks required by your application. The following sections describe each major object and how it affects your programming endeavors.
The Workspaces collection contains Workspace objects. Each Workspace object defines the area in which a particular user operates. All security and transaction processing for a given user takes place within a particular workspace. You can programmatically create multiple workspaces. This is of great value because, by using this technique, you can log in as another user behind the scenes and accomplish tasks not allowed by the security level of the current user. You can log in as a member of the Admins group, for example, change the structure of a table that the current user does not have rights to, and log back out without the user of the system ever knowing that anything happened.
The Users collection contains the User objects for a particular workspace. Each User object is a user account defined by a workgroup database. Because each user is a member of one or more groups, each User object contains a Groups collection that consists of each group of which a particular user is a member. You can easily add and manipulate User objects at runtime.
The Groups collection contains all Group objects for a particular workspace. Each Group object is a group defined by a workgroup database. Because each group contains users, the Group object contains a Users collection that consists of each user who is a member of the group. Like User objects, you can add and manipulate Group objects at runtime.
The Databases collection contains all the databases that are currently open within a particular workspace. You can open multiple databases at a time. These open databases can be Jet databases or external databases. A Database object refers to a particular database within the Databases collection. It is easy to loop through the Databases collection, printing the name of each Database object contained in the collection, as shown in Listing 14.38.
This code loops through the open databases in the current workspace and prints the name of each open database. You can write code to perform all the other tasks required to build, modify, and manipulate database objects at runtime.
The TableDefs collection contains all the tables contained in a particular databasewhether or not they are open. The TableDefs collection also includes linked tables and detailed information about each table. It is easy to loop through the TableDefs collection, printing various properties (for example, the name) of each Table object contained within the collection.
Each TableDef object contains an Indexes collection, which enumerates all the indexes on the table. Each index contains a Fields collection to describe the fields in the index. Listing 14.39 shows an example of using the TableDefs collection to print the properties of each Table object, in addition to printing the properties of each index on the table.
This code loops through the TableDefs in the current database and prints the name of each table in the database. It then prints the name of every index on the table and every field in the index. You can write code that adds, deletes, modifies, and otherwise manipulates tables and indexes at runtime.
The QueryDefs collection includes all the queries contained within a particular database. It contains information about each query. It is easy to loop through the QueryDefs collection, printing various pieces of information about each query, as Listing 14.40 shows.
This code loops through the QueryDefs in the current database and prints the name and SQL statement associated with each QueryDef object. You can write code that adds, deletes, modifies, and otherwise manipulates queries at runtime.
Fields collections are contained within the TableDef, QueryDef, Index, Relation, and Recordset objects. The Fields collection of an object is the collection of Field objects within the parent object. A TableDef object contains Field objects that are contained in the specific table, for example. Using the parent object, you can get information about its Fields collection, as shown in Listing 14.41.
This code loops through the TableDefs collection in the current database. As it loops through each TableDef object, it prints the name and type of each field contained within the Fields collection of the TableDef object. You can also use code to add, delete, or change the attributes of fields at runtime. With a large database, this code is likely to output more information than the Immediate window buffer can contain. You might want to pause the code at some point to view the contents of the Immediate window.
NOTE
Notice that the Type property is an integer value. Each integer returned from this property represents a different field type. You might want to write a case statement that converts the integer value to a more meaningful text string.
Access queries can contain parameters. These parameters are created so that the user can supply information required by the query at runtime. Each QueryDef object has a Parameters collection, which consists of Parameter objects. You can write code to manipulate these parameters at runtime, as Listing 14.42 shows.
This code loops through the QueryDefs collection within the current database. It prints the name of each QueryDef object and then loops through its Parameters collection, printing the name of each parameter. You can add, delete, and manipulate Parameter objects through code at runtime.
Recordset objects exist only at runtime. You use a Recordset object to reference a set of records coming from one or more tables. The Recordsets collection contains all the Recordset objects that are currently open within the current Database object. Recordset objects are covered extensively in the section "Understanding DAO Recordset Types" later in this chapter.
The Relations collection contains all the Relation objects that describe the relationships established within a Database object. The code in Listing 14.43 loops through the current database, printing the Table and ForeignTable of each Relation object.
You can create, delete, and modify relationships at runtime using Visual Basic for Applications (VBA) code.
The Containers collection contains information about each saved Database object. Using the Containers collection, you can view and modify all the objects contained within the current database, as demonstrated in Listing 14.44.
This code loops through the Containers collection, printing the name of each Container object. The results are DataAccessPages, Databases, Forms, Modules, Relationships, Reports, Scripts, SysRel, and Tables objects and collections.
A Document object represents a specific object in the Documents collection. You can loop through the Documents collection of a Container object, as shown in Listing 14.45.
This code points a Container object to the forms in the current database. It then loops through each document in the Container object, printing the name of each Document object (in this case, the name of each form).
NOTE
It is important to understand the difference between the Forms container and the Forms collection. The
Forms container is part of the Containers collection; it contains all the forms that are part of the database. The
Forms collection contains all the forms open at runtime. The properties of each form in the Forms container differ from the properties of a form in the Forms collection.
Each DAO has a Properties collection. The Properties collection of an object is a list of properties associated with that particular object. This gives you a generic way to view and modify the properties of any object, as shown in Listing 14.46.
You can use this collection to create generic routines to handle common tasks. You could write a routine to set the font size of any control to 8 points, for example. Your routine could use the Properties collection to verify that the control has a Font property before attempting to set the size.
This code loops through each form in the current database, printing all the properties of each Form object.
The Errors collection consists of Error objects. An Error object contains information about the most recent error that occurred. Each time an operation generates an error, the code clears the Errors collection of any previous errors. Sometimes a single operation can cause more than one error, so one or more Error objects might be added to the Errors collection when a single data access error occurs.