MS Access for the Business Environment: MS Access as a Documentation Tool: Display Object Dependencies - Page 4

Before going further, we will need to ascertain the appropriate setting for a specific feature in MS Access that must be turned on before we can view object dependencies. That feature, used for tracking object name changes, is Name AutoCorrect, a feature added in MS Access 2000. The Name AutoCorrect feature must have at least its tracking component enabled before we can view object dependencies.

The primary function of Name AutoCorrect is to "monitor" changes made in the names of objects or fields, and to optionally make the associated change in any queries, reports or forms where the associated object appears. Name AutoCorrect does not perform this process with macros or procedures that reference the changed name; these must be modified manually anytime a referenced database object undergoes a name change.

When Name AutoCorrect tracking is enabled, MS Access generates and maintains a name map listing the objects that it monitors. The name map is updated anytime a change is made to the name of an underlying object. These updates trigger subsequent attempts to update all related references to the effected name. With a few limitations, the feature is generally effective in keeping the objects it monitors in sync.

We may not need or want MS Access to do more than track the objects in a given database, and so the option exists to leave the Perform Name Correct (the automatic attempt by MS Access to make changes to the references it determines to be misaligned based upon a change in the name of a corresponding object) disabled. In this case, MS Access will restrict its actions to updating the name map, without attempting to rename the related references that exist within the database.

It is because of its reliance upon the Name AutoCorrect tracking mechanism that viewing object dependencies cannot be accomplished without the tracking feature. This relationship also explains why we cannot view dependency information for objects that are not supported by the Name AutoCorrect feature, including the following object types:

• MS Access Projects
• Macros and Modules within the Database
• Action Queries
• SQL-Specific Queries
• Union Queries
• Data Definition Queries
• Pass-through Queries
• Subqueries

Other potential limitations of the capability to view object dependencies include the fact that hidden objects will not be listed unless the Hidden Objects option on the View tab in the Options dialog box is selected, as might be expected. In addition, if the user of the functionality does not have permissions to access the Design view of a given object, then dependency information surrounding that object will be, predictably, unavailable to that user.

Nested queries are partially supported, in that the capability to view object dependencies extends to the outermost query, and not beyond. Finally, according to the online documentation, the source tables and queries of a query's subdatasheet and lookup fields do not appear in the dependency view as objects upon which the query depends.

Let's check our settings, in preparation for viewing object dependencies, so we can get started with helping our client meet the needs it has expressed.

The multi-tabbed Options dialog opens.

Here we can see the status of the Track Name AutoCorrect info setting. While it is likely that the feature is enabled in the sample Northwind database, it might not be in a database that we actually inherit from past developers / users in our organization. Before making this step in the real world, a little thought should be given to other considerations, perhaps, but, in general, simply tracking (versus performing auto-fixes of) AutoCorrect information is not likely to mean undue risk.

The Options dialog - General tab, with our setting (and, in this case, the Perform name AutoCorrect setting as it comes "out of the box," for the Northwind sample database), appears as shown in Illustration 5.

If the checkmark is already in place (as it likely is), the name map we discussed earlier already exists. In a case where we are enabling the feature from an "unchecked" status, MS Access will build the name map as soon as we click OK.

We return to the Database window. As we have ascertained that MS Access has Track Name AutoCorrect enabled, we will proceed directly to viewing object dependencies.