Ask Our Advisors

PC Magazine

March 25, 1997

Documenting Microsoft Access Databases

I have developed some Microsoft Access databases for use at my office that have become large and complex. I spend a lot of time manually trying to keep the documentation of the structure of the tables, queries, forms, reports, macros, and modules current so that I know how all of the pieces fit together. I do this by keeping the structure in a Microsoft Word document. Is there an add-on software package for access that does this automatically, or do you have an suggestions for making this an easier task?

Mel A. St. John
Thousand Oaks, California

PC MAGAZINE: I'm aware of one built-in tool and one third-party product that can help. Starting with Version 2.0, Access has included a database documenter that you may have overlooked. To see it in Access 2.0, select the File | Add-Ins | Database Documenter. In Access 95 or Access 97, select Tools | Analyze | Documenter. This built-in documentation tool provides an easy way to view, print, output, or save object-design characteristics. For example, with the Access 97 version, you can produce a printout that includes information about the table, such as table properties, column definitions, column properties, table relationships, table indexes, and user and group permissions. You can output this information to an Excel file, a text file, a rich-text-format file or an .HTML file; you can also send it to a table in your database. You can produce similarly voluminous output regarding queries, forms, reports, macros, modules, and/or the current database.

I've seen one third-party package that's very good. Total Access Analyzer, from Vienna, Virginia-based FMS, goes well beyond the built-in tools provided by Microsoft. For example, Total Access not only fully documents objects, it also detects and reports many types of errors. It will detect command buttons that call non-existing functions, for instance, and recognize places where you've assigned the same hotkey to more than one control. Total Access also lists unused tables, queries, and macros.

The product produces over 130 high-quality customizable reports:

Figure 1: FMS's $199 Total Access Analyzer can produce reams of detailed information about your Microsoft Access databases. Over 130 high-quality reports are available.

For example, you can create a comprehensive application report that details how your application works by showing all procedure calls, macro references, and form and report events in bracketed, hierarchical fashion. You can print a relationships report that shows individual table relationships with property definitions, or a relationships-window report that looks like Access's own relationships window. A variety of dictionary and cross-reference reports are available too.

The program works by analyzing your database, collecting the results into another database, then producing reports on the results. You don't have to print the reports; you can view the results directly without producing hard-copy output. The reports are ordinary Access reports-you can even modify their design-but they're good enough that you'll probably have little need to change them.

If you do a lot of custom programming, you'll appreciate Total Access's module reports. You can specify the font and color of comments and code, and you can generate procedure indexes and cross-reference reports. You can also choose "bracketed" module printouts, with block constructs clearly indicated and code properly indented.

Total Access Analyzer is comprehensive and flexible, and it can produce a lot of detailed documentation. At $199, it's not for casual users, but if you regularly develop Access databases it's well worth the investment. As of this writing, only Access 2.0 is supported.