Charlie Kindschi
Microsoft Corporation
February 20, 1998
This article shows you how to use implicit references to default collections to write shorter, more efficient code. It includes code examples that you can run in Microsoft® Access, Microsoft Excel, Microsoft Word, or Microsoft PowerPoint®. The code examples use data from the Northwind sample database (Northwind.mdb), which contains the sales data for a fictitious import/export company called Northwind Traders. The Northwind database is included with Microsoft Office, as well as with other development products, such as Visual Basic®. The default location of the Northwind database is C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb.
You can use the objects in the Data Access Objects (DAO) object hierarchy to access data programmatically. The DAO object hierarchy is an arrangement of parent objects composed of collections of child objects. The DBEngine object is at the top of the DAO object hierarchy; it contains all other objects in the hierarchy and represents the Microsoft Jet database engine. Nearly all the DAO objects below the DBEngine object are members of collections of objects. These collections have default child objects, which contain collections, which contain child objects, and so on.
To reference DAO objects, you refer to them in relation to the DBEngine object, working your way through the object hierarchy. For example, immediately below the DBEngine object in the object hierarchy is the Workspaces collection. To refer to the Workspaces collection, first refer to the DBEngine object, as shown in the following code:
Dim wrk As Workspace
Set wrk = DBEngine.Workspaces(0)
You can refer to DAO objects by ordinal number or by name. In single-user environments, it is common practice to use the default Workspace object of the Workspaces collection by referring to its ordinal number (zero), as shown in the preceding code. Members of most collections are numbered beginning with zero, based on the order in which they were created. In a multiuser environment, you must refer to a Workspace object by using its name, as shown in the following line of code:
Dim wrk As Workspace
Set wrk = DBEngine.Workspaces("MyWorkspaceName")
Note To see a graphical representation of the DAO object hierarchy, search the Access Help index for "object models, DAO." For more information about the DAO object hierarchy, search the Access Help index for "data access overview."
Most DAO objects have a default collection. When referring to a default collection, you do not need to explicitly specify the collection in your code. When you include the name of the default collection in your code, it's called an explicit reference; when you omit the default collection name, it's called an implicit reference.
For example, here's an explicit reference to a TableDef object:
Set tdfNewTable = DBEngine.Workspaces(0).Databases(0).TableDefs(0)
And here's an implicit reference to the same TableDef object:
Set tdfNewTable = DBEngine(0)(0)(0)
Using implicit references when referring to default collections produces shorter code that may be more efficient. However, code with implicit references can be harder to read. You may want to include comments in your code to provide information about what your code is doing.
The following table lists the default collections for DAO objects.
| Object | Default Collection |
| DBEngine | Workspaces |
| Workspace | Databases |
| Database | TableDefs |
| Container | Documents |
| QueryDef | Parameters |
| Recordset | Fields |
| Relation | Fields |
| TableDef | Fields |
| Index | Fields |
| Group | Users |
| User | Groups |
The following code example uses 11 lines of code to display the name of a table and the number of records it contains. It uses explicit references.
Sub CountRecords()
Dim wrk As Workspace
Dim dbs As DAO.Database
Dim tdfNew As TableDef
Dim intRecs As Integer
Dim strTableName As String
Set wrk = DBEngine.Workspaces(0)
Set dbs = wrk.Databases(0)
Set tdfNew = dbs.TableDefs("Categories")
intRecs = tdfNew.RecordCount
strTableName = tdfNew.Name
MsgBox "There are " & intRecs & " records in the " _
& strTableName & " table."
End Sub
You can accomplish the same task with only 7 lines of code by using implicit references to the default collections of the DBEngine, Workspace, and Database objects, as shown in the following code:
Sub CountRecordsCompact()
Dim tdfNew As TableDef
Dim intRecs As Integer
Dim strTableName As String
Set tdfNew = DBEngine(0)(0)(0)
intRecs = tdfNew.RecordCount
strTableName = tdfNew.Name
MsgBox "There are " & intRecs & " records in the " _
& strTableName & " table."
End Sub
The CountRecords and CountRecordsCompact procedures are functionally equivalent. The only difference is the way the code references default collections. In the CountRecordsCompact procedure, the implicit references to default collections are made in the line of code that reads Set tdfNew = DBEngine(0)(0)(0). The first (0) references the default Workspace object of the Workspaces collection, which is the default collection of the DBEngine object. The second (0) references the default Database object of the Databases collection, which is the default collection of the Workspace object. The third (0) references the default TableDef object of the TableDefs collection, which is the default collection of the Database object.
Note DAO objects receive ordinal numbers based on when they are created. We can surmise that the Categories table was the first table created in the Northwind database because TableDef(0).Name returns the Categories table.
To test the CountRecords and the CountRecordsCompact procedures in Access, follow these steps:
Note This code will work with any Access database that contains at least one table. To try the code with another database, change the value of the strFilePath variable to reflect the path and file name of another Access database.
To see the code examples work in an Office application other than Access, you can use the ReadData procedure provided below. In Excel, Word, or PowerPoint, follow these steps:
Sub ReadData()
Dim dbs As DAO.Database
Dim tdfNew As TableDef
Dim intRecs As Integer
Dim strTableName As String
Dim strFilePath As String
strFilePath = "C:\Program Files\Microsoft Office" & _
"\Office\Samples\Northwind.mdb"
Set dbs = DBEngine(0).OpenDatabase(strFilePath)
Set tdfNew = dbs.TableDefs(0)
intRecs = tdfNew.RecordCount
strTableName = tdfNew.Name
MsgBox "There are " & intRecs & " records in the " _
& strTableName & " table."
End Sub
strFilePath variable to reflect this difference. Note You can also use implicit references for default properties. For more information, see "Using Default Properties in Microsoft Excel" at www.microsoft.com/exceldev/tips/defprop.htm.