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