MS-Access / Getting Started

---

Referencing Collections, Objects, and Properties

The most common way to reference objects in the Forms and Reports collections, controls on open forms and reports, and properties of controls. There are two alternative ways to reference an object within a collection. The three ways to reference an object within a collection are as follows:

• CollectionName![Object Name] Example: Forms![frmContacts].
• CollectionName("Object Name") This method is similar to the first method but uses a string constant (or a string variable) to supply the object name. For example: Forms("frmContacts") and Forms(strFormName).
• CollectionName(RelativeObjectNumber) Visual Basic numbers objects within most collections from zero (0) to CollectionName.Count minus 1. You can determine the number of open forms by referring to the Count property of the Forms collection. For example: Forms.Count. You can refer to the second open form in the Forms collection as Forms(1).

Forms and Reports are relatively simple because they are top-level collections within the application engine. When you reference a collection or an object maintained by the DBEngine, the hierarchy of collections and objects is quite complex. If you want to find out the number of Workspace objects that exist in the Workspaces collection, for example, you need to reference the Count property of the Workspaces collection like this:

DBEngine.Workspaces.Count

(You can create additional workspaces from Visual Basic code.)

Using the third technique described earlier to reference an object, you can reference the default (first) Workspace object by entering the following:

DBEngine.Workspaces(0)

Likewise, you can refer to the currently open database in a desktop application (.accdb) by entering the following:

DBEngine.Workspaces(0).Databases(0)

When you want to refer to an object that exists in an object's default (or only) collection, you do not need to include the collection name. Therefore, because the Databases collection is the default collection for the Workspaces collection, you can also refer to the currently open database by entering the following:

DBEngine.Workspaces(0)(0)

As you can see, even with this shorthand syntax, object names can become quite cumbersome if you want to refer, for example, to a particular field within an index definition for a table within the current database in the default Workspace object-or a column within an index definition for a table within the current catalog. For example, using this full syntax, you can reference the name of the first field in the tblContacts table in Contacts.accdb like this:

DBEngine(0)(0).TableDefs("tblContacts").Fields(0).Name

If for no other reason, object variables are quite handy to help minimize name complexity.

In particular, you can reduce name complexity by using an object variable to represent the current database. When you set the variable to the current database, you can call the CurrentDb function rather than use the database's full qualifier. For example, you can declare a Database object variable, set it to the current database by using the CurrentDb function, and then use the Database object variable name as a starting point to reference the TableDefs, QueryDefs, and Recordsets collections that it contains. Likewise, if you are going to work extensively with fields in a TableDef object or columns in a Table object, you are better off establishing an object variable that points directly to the TableDef or Table object. For example, you can simplify the complex expression to reference the name of the first field in the tblContacts table in Contacts.accdb like this:

Dim db As DAO.Database, tdf As DAO.TableDef
Set db = CurrentDb
Set tdf = db.Tabledefs![tblContacts]
Debug.Print tdf.Fields(0).Name

When you use DBEngine.Workspaces(0).Databases(0) (or DBEngine(0)(0)) to set a database object, Visual Basic establishes a pointer to the current database. You can have only one object variable set to the actual copy of the current database, and you must never close this copy. A safer technique is to set your database variable using the CurrentDb function. Using this technique opens a new database object that is based on the same database as the current one. You can have as many copies of the current database as you like, and you can close them when you finish using them. When you use CurrentDb to establish a pointer to your database, Visual Basic refreshes all the collections and keeps them current. If you want to ensure that the collections are current (for example, to be aware of any added or deleted tables or queries), you must refresh the collections yourself when you use DBEngine(0)(0). The one small advantage to DBEngine( 0)(0) is that it is more efficient because it does not refresh all collections when you establish a pointer to it.