Alison Balteramp;#039;s Mastering Microsoft Office Access 1002003 [Electronic resources]

Alison Balter

نسخه متنی -صفحه : 544/ 238
نمايش فراداده

Examining the DAO Model

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.

Figure 14.2. The DAO model.

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

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

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

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

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.

Listing 14.38 Printing the Name of Each Database in a Workspace
Sub EnumerateDBs() Dim ws As dao.Workspace Dim db As dao.Database Dim db1 As dao.Database Dim db2 As dao.Database Set ws = DBEngine(0) 'Point the db1 database object at a reference to the 'current database Set db1 = CurrentDb 'Point the db2 database object at a reference to a 'database called Chap2.mdb Set db2 = ws.OpenDatabase(CurrentProject.Path & "\Chap2.mdb") 'Loop through all of the databases in the workspace, 'printing their names For Each db In ws.Databases Debug.Print db.Name Next db End Sub

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

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.

The Indexes 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.

Listing 14.39 Using the TableDefs and Indexes Collections
Sub EnumerateTablesAndIndexes() Dim db As dao.Database Dim tbl As dao.TableDef Dim idx As dao.Index Dim fld As dao.Field 'Point the db object at a reference to the current database Set db = CurrentDb 'Loop through each TableDef object in the TableDefs 'collection in this database For Each tbl In db.TableDefs 'Print the name of the table Debug.Print "Table: "; tbl.Name 'Loop through all indexes associated with the table For Each idx In tbl.Indexes 'Print the name, primary, and unique properties of 'the index Debug.Print " Index: "; idx.Name Debug.Print " Primary="; idx.PRIMARY; ", Unique="; idx.Unique 'Loop through each field in the index, printing its name For Each fld In idx.Fields Debug.Print " Field:"; fld.Name Next fld Next idx Next tbl End Sub

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

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.

Listing 14.40 Printing Information About Each Query Using the QueryDefs Collection
Sub EnumerateQueries() Dim db As dao.Database Dim qry As dao.QueryDef 'Point the db object at a reference to the current database Set db = CurrentDb 'Loop through each QueryDef object in the QueryDefs 'collection of the database For Each qry In db.QueryDefs 'Print the name and the SQL statement behind the query Debug.Print qry.Name Debug.Print qry.SQL Next qry End Sub

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.

The Fields Collection

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.

Listing 14.41 Getting Information from the Fields Collection
Sub EnumerateFields() Dim tbl As dao.TableDef Dim fld As dao.Field 'Point the db object at a reference to the current database Set db = CurrentDb 'Loop through each TableDef object in the TableDefs 'collection of the database For Each tbl In db.TableDefs 'Loop through each Field object in the Fields 'collection of the table For Each fld In tbl.Fields 'Print the name and type of each field Debug.Print fld.Name Debug.Print fld.Type Next fld Next tbl End Sub

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.

The Parameters Collection

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.

Listing 14.42 Listing the Parameters of Every Query
Sub EnumerateParameters() Dim db As dao.Database Dim qry As dao.QueryDef Dim prm As dao.Parameter 'Point the db object at a reference to the current database Set db = CurrentDb 'Loop through each QueryDef object in the QueryDefs 'collection of the database For Each qry In db.QueryDefs 'Print the Name of the Query Debug.Print "*****" & qry.Name & "*****" 'Loop through each Parameter object in the Parameters 'collection of the query For Each prm In qry.Parameters 'Print the name of the parameter Debug.Print prm.Name Next prm Next qry End Sub

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.

The Recordsets Collection

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

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.

Listing 14.43 Using the Relations Collection
Sub EnumerateRelations() Dim db As dao.Database Dim rel As dao.Relation 'Point the db object at a reference to the current database Set db = CurrentDb 'Loop through each Relation object in the Relations 'collection of the database For Each rel In db.Relations 'Print the names of the Primary and Foreign key tables Debug.Print rel.Table & " Related To: " & rel.ForeignTable Next rel End Sub

You can create, delete, and modify relationships at runtime using Visual Basic for Applications (VBA) code.

The Containers Collection

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.

Listing 14.44 Listing Every Container in a Database
Sub EnumerateContainers() Dim db As dao.Database Dim cnt As dao.Container 'Point the db object at a reference to the current database Set db = CurrentDb 'Loop through each Container object in the Containers 'collection of the database For Each cnt In db.Containers 'Print the name of the container Debug.Print cnt.Name Next cnt End Sub

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.

The Documents Collection

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.

Listing 14.45 Printing the Names of Document Objects
Sub EnumerateForms() Dim db As dao.Database Dim cnt As dao.Container Dim doc As dao.Document 'Point the db object at a reference to the current database Set db = CurrentDb 'Point the Container object at the Forms collection of 'the Container Set cnt = db.Containers!Forms 'Loop through each Document object in the Documents 'collection of the container For Each doc In cnt.Documents 'Print the name of the document Debug.Print doc.Name Next doc End Sub

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.

The Properties 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.

Listing 14.46 Printing Every Property of Document Objects
Sub EnumerateProperties() Dim db As dao.Database Dim cnt As dao.Container Dim doc As dao.Document Dim prp As dao.Property 'Point the db object at a reference to the current database Set db = CurrentDb 'Point the Container object at the Forms collection of 'the Container Set cnt = db.Containers!Forms 'Loop through each Document object in the Documents 'collection of the container For Each doc In cnt.Documents Debug.Print doc.Name 'Loop through each Property object in the 'Properties collection of the document For Each prp In doc.Properties 'Print the name and value of the property Debug.Print prp.Name & " = " & prp.Value Next prp Next doc End Sub

This code loops through each form in the current database, printing all the properties of each Form object.

The Errors Collection

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.