لطفا منتظر باشید ...
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.NOTENotice 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).NOTEIt 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.
