Databases Reference
In-Depth Information
Example 14-3. An object variable example
Sub
exaObjVar( )
Dim ws As Workspace
Dim dbLib As DATABASE
Dim tdfBooks As TableDef
Set ws = DBEngine.Workspaces(0)
Set dbLib = ws.Databases![d:\dbase\library.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.
14.4.3 Default Collections
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
