MS-Access / Getting Started

---

Referring to DAO Objects

In code, you refer to objects in the DAO hierarchy by working your way down the object hierarchy. The following format illustrates generally how to reference DAO objects:

DBEngine.ParentCollection.ChildCollection!Object.Method_or_Property

You might recall from earlier discussion that a collection is a container for a group of objects of the same type. Many DAO object collections contain still other collections, so it is sometimes necessary to drill down through several collections before you get to the object that you want to operate on. This provides a highly structured and predictable method for accessing data and schema.

With the exception of the DBEngine object, all DAO objects are contained within their own collections. For example, the TableDef object is part of a TableDefs collection, and the Group object is part of a Groups collection. As a way of distinguishing between collections and individual objects, those that are named in the plural (ending with the letter s) are collections, whereas those named in the singular are individual objects.

Collections provide an easy way to enumerate their members by allowing you to refer to them by their name or ordinal position. You can also populate a variable with the object's name and use it instead. For example, the following examples show how you can refer to the same object in different ways.

Syntax	Description	Example
Collection("name")	Literal string	DBEngine.Workspaces(0).Databases ("myDB")
Collection(position)	Ordinal collection position	DBEngine.Workspaces(0).Databases (0)
Collection(variable)	String or variant variable	strVar = "myDB" DBEngine.Workspaces(0).Databases (strVar)
Collection![Name]	Object name	DBEngine.Workspaces(0).Databases !myDB

Where the object name contains nonstandard characters, such as spaces, you must enclose the object name in square brackets ([]).

Finally, this tutorial uses the convention of capitalizing the first character of object and collection names, to highlight the difference between them and the casual use of the same word for other purposes. The following example illustrates this convention:

The Database object is an instance of a connection to a database or other data source. It is a member of the Workspace object's Databases collection, which is a container for a group of Database objects that represent connections to one or more databases.

Let's say you wanted to retrieve the DefaultValue property for a field called PaymentDate in a table called tblPayments. This is the long way of doing it:

DBEngine.Workspaces(0).Databases(0) .TableDefs!tblPayments.Fields!PaymentDatei.DefaultValue

As you can see, referring to objects, properties, and methods can sometimes result in quite long lines of code. This can get pretty tedious after a while, so you can also refer to objects by their parent collection's default item. Assuming tblPayments is the first table in the TableDefs collection, and PaymentDate is the first field in that table's Fields collection, here is the shortened version:

DBEngine(0)(0)(0)(0).DefaultValue

The default item for any DAO object collection is the item that occupies ordinal position 0. This is in contrast to VBA collections, in which the first member occupies position 1 - an important fact to remember.

The following table lists examples of the two ways you can use to refer to DAO collection members.

Collection	Default Member	Example
Contains	Documents	DBEngine.Workspaces(0).Databases(0) .Containers(0).Documents(0) DBEngine(0)(0).Containers(0)(0)
Databases	TableDefs	DBEngine.Workspaces(0).Databases(0) .TableDefs(0) DBEngine(0)(0)(0)
DBEngine	Workspaces	DBEngine.Workspaces(0) DBEngine(0)
Groups	Users	DBEngine.Workspaces(0).Groups(0).Users(0) DBEngine(0).Groups(0)(0)
QueryDefs	Parameters	DBEngine.Workspaces(0).Databases(0) .QueryDefs(0).Parameters(0) DBEngine(0)(0).QueryDefs(0)(0)
Recordsets	Fields	DBEngine.Workspaces(0).Databases(0) .Recordsets(0).Fields(0) DBEngine(0)(0).Recordsets(0)(0)
Relations	Fields	DBEngine.Workspaces(0).Databases(0) .Relations(0).Fields(0) DBEngine(0)(0).Relations(0)(0)
TableDefs	Fields	DBEngine.Workspaces(0).Databases(0) .TableDefs(0).Fields(0) DBEngine(0)(0)(0)(0)
Users	Groups	DBEngine.Workspaces(0).Groups(0) .Users(0).Groups(0) DBEngine(0).Groups(0)(0)(0)
Workspaces	Databases	DBEngine.Workspaces(0) DBEngine(0)