Automating Microsoft Access with VBA

You know, of course, that Access has all sorts of objects in each database: tables, forms, macros, and so on. And you've already worked with some of those objects in VBA. For example, you know how to open a form or preview a report from VBA by now. But there are other ways to work with Access objects by using the built-in application collections. Figure 15.1 shows the portion of the Access object model that includes these collections.

The Access Application object supplies two main ways to navigate through the object model to individual objects. The CurrentProject object leads to collections for each of the user interface objects: AllDataAccessPages, AllForms, and so on. The CurrentData object leads to collections for each of the data-related objects: AllQueries, AllTables, and so on. Each of these collections in turn contains AccessObject objects that represent the individual items in the collection.

NOTE

Technically, the CurrentProject object gives you the objects maintained by the Access program itself, whereas the CurrentData object leads to the underlying database engine. In regular Access databases, the database engine is Microsoft Jet; in Access projects, it's Microsoft SQL Server. Most of the CurrentData collections (AllDatabaseDiagrams, AllFunctions, AllStoredProcedures, and AllViews) are applicable to Access projects using the SQL Server database engine. We don't discuss those objects in this book.

CAUTION

Don't confuse the AllForms and AllReports collections with the Forms and Reports collections. The former pair contains all their respective objects in the entire database, whereas the latter two contain only open forms and reports.

This chapter shows you what you can do with these objects. They provide ways for your code to work with the information that you see in the Database window when you're interacting directly with Access, and they can be very handy when you're writing tools in VBA. Each of the object collections supports four properties:

• Application This property returns the parent Application object.

• Count The number of AccessObject objects in the collection.

• Item Indexed property to return individual AccessObject objects.

• Parent Pointer to the parent object (such as CurrentData or CurrentProject).

NOTE

In some cases, the Parent property can return a CodeProject or CodeData object. These objects are similar to the CurrentProject and CurrentData objects, but refer to databases loaded in code. You don't run into these objects when just working with normal databases through the user interface; they're important for add-in developers.