Microsoft Access Object Cross-References and Eliminating Unused Objects

by Luke Chung President of FMS

A revised version of this paper is available here:
Finding and Deleting Unused Microsoft Access Objects and Code

Introduction

One of the biggest challenges to Access developers is understanding how all the objects of a database relate to each other. The need for such cross-reference information is critical to ensuring changes to one part of a database do not impact another. Before making changes to one object, one should know all the objects that are dependent upon it. A comprehensive object cross-reference would be extremely helpful.

Without a comprehensive cross-reference, developers face difficulty determining if an object is really unused. Databases get cluttered with queries and other objects that should be deleted but are not because the developer isn’t quite sure if it’s not used.

Fortunately, a tool is available to generate database cross-reference and detect unused objects.

How Objects are Related

Access is very powerful because its database objects are integrated to work with each other very well. We all know queries use tables, and forms and reports display data from tables and queries. While keeping track of this is a challenge, more subtle, yet equally important references also exist:

• Tables can be linked to other tables through referential integrity or field lookups
• Forms can have combo boxes that are linked to tables and queries different from the form’s record source
• Forms can have subforms linked to other tables, queries, forms and reports.
• Forms and reports can have list boxes that are linked to tables and queries different from its record source
• Reports can have subreports linked to other reports
• Forms and reports can have controls that directly reference controls on other forms
• Forms and reports can reference macros and module code directly from their property sheets
• Queries can use module code
• Macros can reference objects, other macros, and module code
• Modules and code behind forms and reports can reference all types of objects

The Danger

If a cross-reference problem exists in a database (for instance a form wasn’t updated after a table or field was renamed or deleted), the problem is not readily apparent. In fact, the database will compile perfectly fine and the problem will only appear when the user tries to use the object with the bad reference.

Different results occur based on the problem encountered. For queries, an input box asks for a user input (Access interprets the missing field as a user provided parameter). For forms and reports, a crash occurs when a control references a missing field, or the record source can’t find a table. Macros halt if a referenced object cannot be found, and module code may or may not stop depending on how error handling is set.

Also note that even though an application may compile, code can be used in places that the compiler does not detect. These include code use in queries, macro run code commands, and the property sheet for events on forms and reports. In these places, the procedure called may not exist, or its number of arguments and/or their types change. The database module code will compile, but the code crashes when an attempt to invoke it occurs.

Developers can avoid these embarrassing crashes due to cross-reference problems with the proper analysis prior to deploying their databases.

The Solution

Total Access Analyzer from FMS, Inc. generates extensive documentation and analysis of Access databases. Total Access Analyzer runs as an Access add-in and can analyze all the objects in your database at once. By doing so, it generates cross-reference information of how objects and code are related to each other, detects bad references, and identifies objects that are not referenced. The results are presented on screen and in 350 presentation quality reports. Empower yourself with the cross-reference information to safely make the changes you need to make.

Conclusion

Because the number of objects in Access databases grow so quickly, understanding their relationships to each other is critical to successful application development. Creating and maintaining this manually is extremely difficult and time consuming. Fortunately, Total Access Analyzer performs these chores for you and empowers you to make the enhancements you need to make. Total Access Analyzer also offers detailed analysis of your Access database and detects over 270 types of errors, suggestions, and performance tips to improve it. Discover why so many Access developers and development shops insist on running Total Access Analyzer during system development and prior to releasing their Access applications.

Total Access Analyzer has been available since 1993 for Access 1.1. Since then, it has won every “Best Access Add-in” award in the industry.

FMS, Inc. (www.fmsinc.com) is the leading provider of 3rd party products for Microsoft Access users and developers. Visit the FMS web site for additional Access resources, utilities, technical papers, demos, and product information.

Main Technical Papers Page