Access VBA Macro Programming

In Access, all objects are either singular objects referenced by name or objects in a

collection referenced by index or name. Collections also have their own properties and

methods apart from the objects that they hold. For example, collections always hold aCount

property that represents the number of objects within the collection, and they sometimes have

anAddmethod to add a new object into the collection.

These objects also have their own properties and methods and can also contain further

collections of objects. An example is theTableDefscollection, which contains a collection of

TableDefobjects (table definitions), representing all tables in Access. As you have already

learned, it has aCountproperty to index the number ofTableDefs, and it has anAppend

method to create a newTableDef. EachTableDefwithin the collection has properties such as

DateCreatedand methods such asCreateFieldorCreateIndex. However, there is also a

collection of Fields within eachTableDefthat then has its own properties, methods, and

collections.

In Access, you can have a collection of Fields inside aTableDefcalled theFields

collection, and each Field inside this collection will have an index number and a name to

identify it. The index number is a reference for an object within that collection, commencing

at 0.

The same thing is true of theTableDefscollection, which defines all the tables within the

Access database. There is a collection of table definitions called theTableDefscollection,

and eachTableDefobject inside will be enumerated with an index number and a name to

identify it. Other collection objects are available, such asQueryDefs,Forms, andReports,

but theTableDefandTableDefscollections make good examples on how to use collections.

Collections can be cycled or iterated through.Cyclingis the best term to describe what

happens in a For Each..Next loop. For Each..Next loops are covered in Chapter 4. You use the

following syntax: For Each Object within Collection, Next. This cycles through each object

within the collection, giving you the chance to examine or manipulate the properties of it or

to use a method by calling it, for example

CurrentDb.TableDefs("MyTable").CreateField("NewField")

Try putting the code shown in the following listing in a VBA module. If you do not

already have a module displayed, use Insert | Module from the VBE menu. Your screen

should look like Figure 14-1.

PressF5to run it and you will see each table’s name displayed, as shown in Figure 14-2.

Sub ShowName()

Dim w As TableDef

For Each w In CurrentDb.TableDefs

MsgBox w.Name

Next w

End Sub

182 Microsoft Access 2010 VBA Macro Programming