In order to access the properties or invoke the methods of an object, we need to first define an object variable to reference that object.

VBA and DAO offer a wide variety of object data types . There is a slight difference in syntax when declaring and setting an object variable, as opposed to a standard variable. For instance, here is an example using the Database object type. Note that the full pathname of the LIBRARY database on my PC is d:dbaselibrary.mdb:

Dim dbLibrary as Database
Set dbLibrary = "d:dbaselibrary.mdb"

In general, the syntax is:

Dim objectVariable as ObjectDataType
Set objectVariable = ObjectName

Note that the only difference between setting object variables and setting standard variables is the keyword Set. However, this minor syntactic difference belies a much more significant difference between standard variables and object variables.

In particular, a standard variable can be thought of as a name for a location in the computer’s memory that holds the data. For instance, in the code:

Dim intVar As Integer
intVar = 123

the variable intVar is a 4-byte memory location that holds the integer value 123. Figure 14-1 illustrates the variable intVar. (Actually, the 4-byte memory location holds the value 123 in binary format, but that is not relevant to our discussion.)

Figure 14-1. An example of the intVar variable

Of course, if we were to write:

Dim intVar As Integer
Dim intVar2 As Integer
intvar = 123
intVar2 = intVar
intVar2 = 567

we would not expect the last line of code to have any effect upon the value of the variable intVar, which should still be 123.

On the other hand, an object variable is not the name of a memory location that holds the object’s “value,” whatever that means. Rather, an object variable holds the address of the area of memory that holds the object. Put another way, the object variable holds a reference to, or points to, the object. It is therefore called a pointer variable. The idea is pictured in Figure 14-2, where rsBooks and rsBooks2 are object variables, both pointing to an object of type Recordset.

Figure 14-2. An example of a pointer variable

To illustrate this further, consider the code in Example 14-1.

Sub exaObjectVar(  )

'Declare some object variables
Dim dbLib As DATABASE
Dim rsBooks As Recordset
Dim rsBooks2 As Recordset

'Set dbLib to the current database (i.e. LIBRARY)
Set dbLib = CurrentDb

'Open a recordset object for the BOOKS table
Set rsBooks = dbLib.OpenRecordset("BOOKS")

'Two object variables will refer to the same object
Set rsBooks2 = rsBooks

'Use a property of this object
MsgBox "BOOKS record count: " & rsBooks.RecordCount

'Destroy the object using rsBooks2 reference
rsBooks2.Close

'Now rsBooks has nothing to refer to, so we get error
MsgBox "BOOKS record count: " & rsBooks.RecordCount

End Sub

First, we declare two object variables of type Recordset (we will discuss this type in detail later). The line:

Set rsBooks = dbLib.OpenRecordset("BOOKS")

sets rsBooks to point to (or refer to) a Recordset object created from the BOOKS table. Note again that, unlike standard variables, setting an object variable requires the use of the keyword Set. The line:

Set rsBooks2 = rsBooks

sets rsBooks2 to point to the same Recordset object as rsBooks, as shown in Figure 14-2.

Next, the line:

MsgBox "BOOKS record count: " & rsBooks.RecordCount

displays the message box in Figure 14-3, showing that there are 14 books in the recordset.

Figure 14-3. The message box from the exaObjectVar( ) example

To illustrate the fact that both variables point to the same object, the line:

rsBooks2.Close

uses the pointer rsBooks2 to destroy (or close) the Recordset object. Then, when the line:

MsgBox "BOOKS record count: " & rsBooks.RecordCount

is executed, the Recordset object that both variables referred to is gone, and so the expression rsBooks.RecordCount causes an “Object invalid or no longer set” error, as shown in Figure 14-4.

Figure 14-4. Error message from the exaObjectvar( ) example

The moral of this example is that it is important to remember that object variables refer to objects and that more than one variable can refer to the same object. Despite this, it is customary to use the misleading statement “the objVar object” when we really should be saying “the object referred to by objVar.”

Tables Table 14-1 and Table 14-2 describe the naming convention for both standard and object variables that we will (try to) use in this book. (Table 14-1 is a repeat of Table 10-3.) We will explain the various object types as we proceed through this chapter.

The general syntax for referring to an object’s properties and methods is very simple. Suppose that objVar is a variable that refers to an object. If AProperty is a property of this object, then we can access this property using the syntax:

objVar.AProperty

If AMethod is a method for this object, then we can invoke that method with the syntax:

objVar.AMethod(any required parameters)

To illustrate, consider the code in Example 14-2.

Sub exaPropertyMethod(  )

Dim dbLib As DATABASE
Dim qdfExpensive As QueryDef

' Get current database (LIBRARY)
Set dbLib = CurrentDb

' Show Name property
MsgBox dbLib.Name

' Invoke the CreateQueryDef method to create a query
Set qdfExpensive = dbLib.CreateQueryDef("Expensive",_
"SELECT * FROM BOOKS WHERE Price > 20")

End Sub

The line:

Set dbLib = CurrentDb

sets the object variable of type Database to point to the current database, that is, the LIBRARY database. The line:

MsgBox dbLib.Name

displays the value of the Name property of dbLib. The line:

Set qdfExpensive = dbLib.CreateQueryDef("Expensive",_
"SELECT * FROM BOOKS WHERE Price > 20")

invokes the CreateQueryDefmethod to create a new query named Expensive and defined by the SQL statement:

SELECT * FROM BOOKS WHERE Price > 20

Note that the code:

dbLib.CreateQueryDef("Expensive","SELECT * FROM BOOKS WHERE Price > 20")

invokes the method, which returns the QueryDef object, which is then pointed to by the object variable qdfExpensive. If you run this program, you will notice a new entry in the Query tab of the Database window. (If the query Expensive is already in the database, delete it before running this program. Also, you may need to switch away from and then return to the Query tab to refresh the list.)

There is a problem with these names. For instance, to which object does Fields(0) refer? There are several Fields collections in the DAO hierarchy, as can be seen from Figure 14-5. Let us refer to the names described in the previous syntax as semiqualified names. To avoid the problem that a semiqualified name may not be unique, we must use the fully qualified object name, which is formed by tracing the entire hierarchy from the top (DBEngine) to the desired object. For instance, the fully qualified name for BOOKS is:

DBEngine.Workspaces(0).Databases![d:dbaselibrary.mdb].TableDefs!BOOKS

Let us examine this name. It is composed of four separate semiqualified object names, separated by periods. These periods are referred to as dot operators :

DBEngine.
Workspaces(0).
Databases![d:dbaselibrary.mdb].
TableDefs!BOOKS

Perhaps the easiest way to make sense of this name is to start from the bottom. The semiqualified name of the object we are interested in is:

TableDefs!BOOKS

This object is contained in the TableDefs collection for the Database object named:

Databases![d:dbaselibrary.mdb]

This object is, in turn, contained in the Databases collection of the default Workspace object (more on this later), which is:

Workspaces(0)

which, in turn, is contained in the DBEngine object. Separating each of these object names by the dot operator gives the fully qualified object name.

In general, the syntax for a semiqualified object name is:

Collection!Object

and for a fully qualified object name, it is:

DBEngine.Collection1!Object1. · · · .CollectionN!ObjectN

There seems to be much confusion over when to use the bang operator (!) and when to use the dot operator (.). Perhaps the following will help:

This naming convention is really not as confusing as it may look at first, if you remember the previous three maxims. However, if you want confusing, stay tuned for default collections.

As you can see, a fully qualified object name can be quite lengthy. This problem is compounded by the fact that it may be necessary to refer to the same object many times in a program. There are two common ways to deal with this issue.

One way is to use object variables. Consider the code in Example 14-3 to display the RecordCount property of the BOOKS table.

Sub exaObjVar(  )

Dim ws As Workspace
Dim dbLib As DATABASE
Dim tdfBooks As TableDef

Set ws = DBEngine.Workspaces(0)
Set dbLib = ws.Databases![d:dbaselibrary.mdb]
Set tdfBooks = dbLib.TableDefs!BOOKS

MsgBox tdfBooks.RecordCount

End Sub

By defining three object variables, ws, dbLib , and tdfBooks , we were able to avoid writing the fully qualified name of BOOKS (on a single line, that is). Also, the line:

MsgBox tdfBooks.RecordCount

is much easier to read. (It reads: “Message me the record count of TableDef tdfBooks.”)

The use of object variables in this way has several advantages and is highly recommended. First, it tends to make the lines of code shorter and more readable. Second, we can refer to the object variable tdfBooks many times without having to write the fully qualified object name each time. As a result, the program will run somewhat faster, since VBA does not have to resolve the object name by climbing down the object hierarchy more than once.

There is another method that can be used for shortening fully qualified object names. In particular, each object has a default collection , which can be used as follows. Consider a portion of a fully qualified name:

Collection1!Object1.Collection2!Object2

If Collection2 is the default collection of Object1, then this name may be shortened to:

Collection1!Object1!Object2

where we have omitted the default collection name Collection2, as well as the preceding dot.

For instance, the default collection of DBEngine is Workspaces. Hence:

DBEngine.Workspaces!MyWorkspace

can be shortened to:

DBEngine!MyWorkspace

and the phrase:

DBEngine.Workspaces(0)

can be shortened to:

DBEngine(0)

Also, since the default collection for a Workspace object is Databases, the phrase:

DBEngine.Workspaces(0).Databases(0)

can be shortened to:

DBEngine(0)(0)

Table 14-3 shows the default collections in the DAO and Access object model.

The use of default collections can save space. However, it does very little for readability (to say the least) and is probably best left to programmers with so much experience that they hardly read the names anyway! To emphasize the point, each of the lines in Example 14-4 displays the RecordCount property of the BOOKS table. Note that the full name of the database library file on my computer is d:dbaselibrary.mdb.

Sub exaDefaultCollections(  )

MsgBox DBEngine.Workspaces(0).Databases![d:dbaselibrary.mdb]. _
TableDefs!BOOKS.RecordCount

MsgBox _
DBEngine(0).Databases![d:dbaselibrary.mdb].TableDefs!BOOKS.RecordCount

MsgBox DBEngine(0)![d:dbaselibrary.mdb].TableDefs!BOOKS.RecordCount

MsgBox DBEngine(0)![d:dbaselibrary.mdb]!BOOKS.RecordCount

MsgBox DBEngine(0)(0)!BOOKS.RecordCount

End Sub

The Access collections Forms, Reports, and Controls have no methods and only one property, Count , which reports the number of objects in the collection. Thus, the line:

Forms.Count

reports the number of opened forms in the current database. (When we discuss Container objects, we’ll see that there is a way to get the number of saved forms as well.)

DAO collections fall into two categories with respect to their properties and methods. All DAO collections have a single property: Count. All DAO collections also have the Refresh method, which we will discuss a bit later. In addition, some of the collections have the Append and corresponding Delete methods, while others do not.

Evidently, some collections do not have Append or Delete methods because DAO does not want the user to append or delete objects from these collections. This is reasonable because DAO takes care of collection housekeeping automatically for these collections. For example, DAO automatically appends new databases to the Databases collection whenever they are created using the CreateDatabase method. However, it does not do so for new TableDef or QueryDef objects, for instance.

Note that Microsoft Access will do the housekeeping chores for you when objects are created and saved using the Access interface.

User-defined Collection objects have one property: Count. They have three methods: Add , Remove , and Item. Add and Remove perform as advertised by their names, and we will see an example shortly. The Item method is used to identify the items in the collection, since they may or may not have names.

A single user-defined collection can contain objects of various types, including other collections. Here is an example to illustrate the Add method.

In Example 14-5, we create two collections: colParent and colChild. We then place colChild inside colParent, along with the BOOKS TableDef object. Thus, the colParent collection contains two objects of very different types—one Collection object and one TableDef object. (While this example is not of much practical value, it does illustrate the point.)

Sub exaCollections(  )

' Declare two variables of type collection
Dim colParent As New Collection
Dim colChild As New Collection

Dim tdfBooks As TableDef
Dim objVar As Object

Set tdfBooks = DBEngine(0)(0).TableDefs!Books

' Use Add method of collection object
' to add objects to colParent collection
colParent.Add colChild
colParent.Add tdfBooks

' Display size of collection
MsgBox "Size of Parent collection " & colParent.Count

' Iterate through collection. Note use of
' TypeOf statement
For Each objVar In colParent
    If TypeOf objVar Is Collection Then
        MsgBox "Collection"
    ElseIf TypeOf objVar Is TableDef Then
        MsgBox objVar.Name
    End If
Next

End Sub

In Example 14-5, we used the Add method of the Collection object to add items to the collection and the Count property of the Collection object, which returns the size of the collection. Note also the use of the TypeOf statement to determine the type of each object in the collection.

Now let us consider the Item method, which returns a specific object from a collection. The general syntax is:

Collection.Item(index)

where index is an index into the collection. Note that DAO collections begin with index 0 and go to index Collection.Count - 1.

To illustrate the Item method, in place of the code:

For Each tbl In db.TableDefs
    strTbls = strTbls & vbCrLf & tbl.Name
Next tbl

we could have written:

For i = 0 To db.TableDefs.Count - 1

  strTbls = strTbls & vbCrLf & _
db.TableDefs.Item(i).Name

Next i

We should remark that an object’s ordinal position in a collection is never guaranteed and can sometimes change without warning. Thus, for example, it is unwise to rely on the fact that the object that is Item(0) at some time will always be Item(0).

Incidentally, one of the drawbacks of collections that contain different types of objects, as in the previous example, is that we can seldom do the same thing to all of the objects in the collection. For this reason, creating collections containing different types of objects is generally not very useful.

It is worth re-emphasizing that the collections in the DAO hierarchy are not contained in their parent collections (as is the case for the user-defined collections in the previous example). For example, the TableDefs collection contains only TableDef objects (table definitions). It does not contain the Fields collection. Rather, each TableDef object contains a Fields collection. We can confirm this with the code in Example 14-6, which displays the size of the TableDefs collection for the LIBRARY database as 14 and then displays the names of each of its 14 objects, showing that there is nothing but TableDef objects in the TableDefs collection.

Sub exaCheckTableDefs(  )

Dim db As DATABASE
Dim tbl As TableDef
Dim strTbls As String

Set db = CurrentDb

strTbls = ""
MsgBox db.TableDefs.Count
For Each tbl In db.TableDefs
    strTbls = strTbls & vbCrLf & tbl.Name & " - " & TypeName(tbl)
Next

MsgBox strTbls

End Sub

Running the code in Example 14-6 produces two message boxes; the second is shown in Figure 14-8, which also shows that most of the TableDefs in the database are system-table definitions, created by Microsoft Access for its own use. ( Just in case some additional tables get added to the LIBRARY database after this book goes to print, you may find a different list of tables when you run this example.) Figure 14-8 also illustrates the use of the function TypeName.

Figure 14-8. A list of TableDefs generated by exaCheckTableDefs( )

There are times when the Microsoft Jet engine does not have the latest information on the contents of a collection. For example, this can happen in a multiuser environment, when one user makes a change to a collection. It can also happen when a host environment, such as Microsoft Access, makes a change to the environment. To see this, try the following simple experiment.

Enter the following code:

Sub temp(  )

Dim db As DATABASE
Set db = DBEngine(0)(0)

' db.TableDefs.Refresh
MsgBox "Table count: " & db.TableDefs.Count

End Sub

Run the procedure. You should get a message that there are 13 tables in the TableDefs collection. Now use Microsoft Access to create a new table, and save the table. Then rerun the previous code. It will still report that there are 13 tables! Now remove the comment mark on the line:

' db.TableDefs.Refresh

and rerun the code. You should now get an accurate table count.

The point here is that the Jet engine does not keep track of the machinations of its host application—Microsoft Access. Hence, to be certain that a collection is up to date, you may need to use the Refresh method.

There are several virtues to the existence of Properties collections. One is that it is possible to iterate through all of the properties of an object, using the For Eachsyntax discussed earlier, for instance, without even knowing the names of the properties.

For example, the following simple code:

Dim db As DATABASE
Dim prp As Property
Set db = CurrentDb

For Each prp In db.TableDefs!BOOKS.Properties
    Debug.Print prp.Name
Next prp

produces the following list of all properties of the BOOKS object:

Name
Updatable
DateCreated
LastUpdated
Connect
Attributes
SourceTableName
RecordCount
ValidationRule
ValidationText
ConflictTable
OrderByOn
OrderBy

Another virtue of Properties collections is that they allow for the creation (and storage) of new properties. We discuss this next.

In general, the properties of an object can be classified into three groups, depending upon their origin:

The Jet database engine defines built-in properties for its objects. For instance, a TableDef object has a built-in Name property. In addition, Microsoft Access (and other applications that may be using the Jet engine) can create application-defined properties. For example, if you create a table in Microsoft Access and fill in the Description field in the View...Properties dialog box, Access creates a Description property for the table and appends it to the Properties collection for that TableDef object. Finally, as we will see later, the user can create his own properties.

It is important to note that an application-defined property is created only if the user assigns a value to that property. For example, if you do not specifically type a description in the Description field, as discussed earlier, then Access will not create a Description property. In other words, Access does not create a blank Description property. If you then use this property in your code, an error will result. Thus, when you write programs that refer to either application-defined or user-defined properties, it is important to check for errors, in case the referenced property does not exist.

Of course, each Property object, being an object, has its own properties, but you will be glad to hear that these properties do not have Property objects. (Where would this end?)

We should also mention that properties can be classified as read/write, read-only, or write-only. A read/write property can be both read and written to (i.e., changed), whereas a read-only property can be read but not changed, and a write-only property can be changed but not read. When an object is first created, its read/write properties can be set. However, in many cases, once the object is appended to a collection, some of these properties may become read-only and can therefore no longer be changed.

The properties of a Property object are described as follows. A Property object has no methods.

Property: Value

Finally, we get to the main property of a Property object—its value, which can be any value commensurate with the assigned Type property of the Property object.

Let us consider another example of how to use the Properties collection. The code in Example 14-7 will display the entire contents of the Properties collection for the BOOKS TableDef object in the LIBRARY database.

Example 14-7. A Properties collection example

Sub exaProperties(  )

Dim db As DATABASE
Dim tbl As TableDef
Dim prp As Property
Dim str As String

Set db = CurrentDb
Set tbl = db!BOOKS

str = ""
For Each prp In tbl.Properties
    
    str = str & prp.Name
    str = str & " = " & prp.Value
    str = str & " (" & prp.Type & ") "
    str = str & prp.Inherited & vbCrLf

Next prp

MsgBox "BOOKS has " & tbl.Properties.Count _
& " properties: " & vbCrLf & str

End Sub

Running this procedure gives the window shown in Figure 14-10, where each line has the form Name = Value (Type) Inherited.

Figure 14-10. Window generated from executing exaProperties

We mentioned that a user can add user-defined properties to an object. Let us consider an example of adding a new property to the BOOKS TableDef object.

The code in Example 14-8 adds the user-defined property named UserProperty to the BOOKS table. It uses the CreateProperty method of the TableDef object.

Sub exaUserDefinedProperty(  )

' Add user-defined property to BOOKS TableDef object

Dim db As DATABASE
Dim tbl As TableDef
Dim prp As Property

Dim str As String

Set db = CurrentDb
Set tbl = db!BOOKS

' Create new property using CreateProperty method
Set prp = tbl.CreateProperty("UserProperty", dbText,"Programming DAO is fun.")

' Append it to Properties collection
tbl.Properties.Append prp

' List all properties
str = ""
For Each prp In tbl.Properties
    str = str & prp.Name
    str = str & " = " & prp.Value
    str = str & " (" & prp.Type & ") "
    str = str & prp.Inherited & vbCrLf
Next prp

MsgBox "BOOKS has " & tbl.Properties.Count & " properties: " & vbCrLf & str

End Sub

This procedure produces the window shown in Figure 14-11. Note the last property on the list.

Figure 14-11. Window generated from executing exaUserDefinedProperty

The DBEngine object, of which there is only one, represents the Jet database engine. This is the only object in the DAO that is not contained in a collection. We have seen several examples of its use, along with the fact that the default collection for the DBEngine object is Workspaces, and so:

DBEngine.Workspaces(0)

is equivalent to:

DBEngine(0)

We have also seen that:

DBEngine(0)(0)

denotes the first database in the first (default) workspace.

The DBEngine object has methods to create a new workspace (CreateWorkspace), to compact a database (CompactDatabase), and to repair a database (RepairDatabase), among others.

From time to time, an operation may cause one or more errors to occur (or so I am told). When this happens, the Errors collection is first emptied and then filled with one Error object for each error that the operation caused. (Some operations may cause more than one error.) Note that if no errors occur, the Errors collection remains as it was before the operation.

Example 14-9, which deliberately produces an error, illustrates the use of the Errors collection. It also demonstrates the use of three Error object properties: Number (the VBA error number), Description (a description in words of the error), and Source (the object or application that generated the error).

Sub exaErrorsCollection(  )
    
' Note declaration of object variable of type Error
Dim dbsTest As DATABASE
Dim txtError As String
Dim errObj As Error

On Error GoTo ehTest

' A statement that produces an error
Set dbsTest = _
DBEngine.Workspaces(0).OpenDatabase("NoSuchDatabase")

Exit Sub

ehTest:

txtError = ""
' Loop through the Errors collection,
' to get the Number, Description and Source
' for each error object
For Each errObj In DBEngine.Errors
    txtError = txtError & Format$(errObj.Number)
    txtError = txtError & ": " & errObj.Description
    txtError = txtError & " (" & errObj.Source & ")"
    txtError = txtError & vbCrLf
Next
    
MsgBox txtError 

Exit Sub

End Sub

Running this code produces the window in Figure 14-12.

Figure 14-12. Error message from executing exaErrorsCollection

There is one Workspace object for each Access user session. In a single-user environment, there is generally only one session running. When a user starts Access with no security options enabled, Access automatically creates a Workspace called:

DBEngine.Workspaces(0)

Since we are not concerned in this book with multiple users or with database-security issues, we will not be creating multiple workspaces.

The values of the Name and UserName properties of the default Workspace object are easily determined by running the following code:

Sub Test(  )

MsgBox "Count: " & DBEngine.Workspaces.Count
MsgBox "Name: " & DBEngine.Workspaces(0).Name
MsgBox "UserName: " & DBEngine.Workspaces(0).UserName

End Sub

This code should produce three message boxes, indicating that there is only one open workspace, with name #Default Workspace# and username admin.

Among the methods of a Workspace object are CreateDatabase (for creating a new database) and OpenDatabase (for opening an existing database). Another interesting group of methods is BeginTrans, CommitTrans , and Rollback, which allow the programmer to group several operations into one transaction. At the end of the transaction, the programmer can commit the operations—or rollback the database to its state prior to any of the operations in the transaction. One use for this is in updating related tables (as in transferring money from one table to another). If the entire group of operations is not completed successfully, then a rollback is probably desirable.

Workspace objects also have a Close method for closing opened workspaces. However, the method is ignored when applied to the default Workspace under Microsoft Access.

The Jet engine provides security by assigning access permissions to users of the engine. A User object represents a user of the Jet engine. The Users collection contains all User objects. (Of course, female users are never to be considered objects.)

A Group object represents a set of User objects (users) that have a common set of access permissions. By using Group objects, a new user can be given a set of access permissions simply by adding the corresponding User object to the appropriate Group object. The Groups collection holds all Group objects.

A Database object represents a currently open database. In Microsoft Jet, you can have multiple databases open at one time (using the OpenDatabase function, discussed in Chapter 15). However, the Microsoft Access environment can display a graphical interface for only one database. In the Microsoft Access environment, when a database is opened, it is assigned to DBEngine.Workspaces(0).Databases(0).

Database objects have a variety of methods for creating new objects: CreateProperty, CreateQueryDef, CreateTableDef , and OpenRecordset. There is also an Execute method for running action queries or executing SQL statements on the database. As mentioned earlier, Database objects also have a Close method.

A TableDef object represents a table definition for a saved table in the database. A TableDef object is more than a table scheme, in that it also has a RecordCount property that gives the number of rows in the table (and thus, in some sense, reflects the data in the table). However, it is less than a table, in that it does not describe the actual data in the table. The TableDefs collection contains all TableDef objects for a given database. TableDef objects have methods for creating fields (CreateField), indexes (CreateIndex), and opening recordsets (OpenRecordset).

A QueryDef object represents a saved query in the database. The QueryDefs collection contains all QueryDef objects for a given database. One of the most interesting properties of a QueryDef object is SQL, which can be used to set or read the SQL definition of the QueryDef object.

A Recordset object represents data from one or more tables or queries, and is used to manipulate that data. Note that a Recordset object is temporary, in that it is not saved with the application. In fact, recordsets are created in code using the OpenRecordset function. The Recordsets collection contains all open Recordset objects in the current database.

Recordset objects are the workhorses of the DAO object model, with about 15 different methods and about 20 different properties. There are actually three types of Recordset objects—Table-type, Dynaset, and Snapshot—used for different purposes. We will discuss recordsets in Chapter 15.

A Relation object represents a relationship between certain fields in tables or queries. The Relation object can be used to view or create relationships. The Relations collection contains all Relation objects for a given database. We will discuss how to create a relation in .

The Microsoft Jet engine provides the Containers collection as a location where a host application, such as Microsoft Access, can store its own objects. This is done through the use of Container objects, as shown in Figure 14-13.

Figure 14-13. Container objects diagram of the MS Jet engine

The Jet engine itself creates three Container objects:

It is important not to confuse these Container objects (which are not collections, despite their names) with the Databases, TableDefs, and Relations collections. Indeed, these objects are at entirely different locations in the DAO object hierarchy and serve different purposes, as we will see.

In addition to the Container objects created by the Jet engine, Microsoft Access stores its forms, reports, macros, and modules in the Containers collection. Hence, the Containers collection also contains:

The Forms and Reports Container objects should not be confused with the Microsoft Access collections of the same name (in the Access object model). In particular, the former contains information about all saved objects, whereas the latter contains information about all open objects.

To illustrate the aforementioned difference, create and save two forms in an Access session, and make sure that only one form is open. Then run the code in Example 14-10, which should report that the open form count is 1 but the saved form count is 2.

Sub exaFormsContainer(  )

Dim db As DATABASE
Dim frm As Form
Dim doc As Document

Set db = CurrentDb

Debug.Print "Opened form count: " & Forms.Count
For Each frm In Forms
    Debug.Print frm.Name
Next
Debug.Print

Debug.Print "Saved form count: " & db.Containers!Forms.Documents.Count
For Each doc In db.Containers!Forms.Documents
    Debug.Print doc.Name
Next

End Sub

Note that a user cannot create new or delete existing Container objects—they are controlled by the Jet engine only. Put another way, there is no such thing as a user-defined Container object. The properties of a Container object generally reflect security-related issues, such as permission and user/group names. Container objects have no methods.

We have seen that applications (including Jet and Access) store objects through the use of Container objects. However, the Forms Container object, for example, is not of any real interest per se. The Form objects that reside within the Forms container are of interest. Actually, these Form objects are referred to as Document objects and are contained in the Documents collection of the Forms container, also shown in Figure 14-6. (If you are getting a bit confused, Figure 14-6 should help—it always helps me.)

Thus, it is the Document objects (in a Documents collection) that are the raison d'être for the Container objects. Example 14-11 illustrates a few of the properties of a Document object: Container, DateCreated, LastUpdated, Name, and Owner. It displays the value of various properties of the Document objects in the Documents collection of the Tables Container object.

Sub exaTablesDocuments(  )
Dim db As DATABASE
Set db = CurrentDb
Dim docs As Documents
Dim doc As Document

Set docs = db.Containers!Tables.Documents
Debug.Print "Count: " & docs.Count

For Each doc In docs

    Debug.Print "Container: " & doc.Container
    Debug.Print "DateCreated: " & doc.DateCreated
    Debug.Print "LastUpdated: " & doc.LastUpdated
    Debug.Print "Name: " & doc.Name
    Debug.Print "Owner: " & doc.Owner
    Debug.Print

Next doc

End Sub

Here is a portion of the output from executing Example 14-11:

Count: 16 
Container: Tables
DateCreated: 10/22/96 3:16:44 PM
LastUpdated: 10/24/96 1:36:16 PM
Name: AUTHORS
Owner: admin

Container: Tables
DateCreated: 10/22/96 3:19:47 PM
LastUpdated: 10/24/96 1:36:16 PM
Name: BOOK/AUTHOR
Owner: admin

Container: Tables
DateCreated: 5/15/96 6:16:29 PM
LastUpdated: 5/15/96 6:16:29 PM
Name: MSysACEs
Owner: Engine

Container: Tables
DateCreated: 5/15/96 6:16:31 PM
LastUpdated: 5/15/96 6:16:31 PM
Name: MSysIMEXColumns
Owner: admin

The Fields collection contains Field objects, which describe the various fields in a TableDef, QueryDef, Index, Relation, or Recordset object.

The parameters of a parameter query are represented by Parameter objects, contained in the Parameters collection for that QueryDef object. Note that Parameter objects cannot be added to or deleted from the Parameters collection—Parameter objects represent existing parameters. Let us consider an example.

The code in Example 14-12 creates a parameter query named ParameterQuery and demonstrates some of the properties of a Parameter object—namely, Name, Type, and Value.

Sub exaParameters(  )
Dim db As DATABASE
Dim qdf As QueryDef
Dim strSQL As String

Set db = CurrentDb

' Create an SQL statement with parameters
strSQL = "SELECT * FROM BOOKS WHERE _
Price > [Enter minimum price]"

' Create a new QueryDef object
Set qdf = db.CreateQueryDef("ParameterQuery", strSQL)
    
' Supply value for parameter
qdf.PARAMETERS![Enter minimum price] = 15

' Now query query
Debug.Print qdf.PARAMETERS![Enter minimum price].Name
Debug.Print qdf.PARAMETERS![Enter minimum price].Type
Debug.Print qdf.PARAMETERS![Enter minimum _
price].Value

End Sub

An Indexes collection contains all of the saved Index objects (i.e., indexes) for a TableDef object. We will discuss how to create an index in Chapter 15.