Access Databases Revealed!

Total Access Analyzer 97 offers comprehensive analysis and documentation of Access databases from version 1.1 and on up.

By Dave Chowning
Access/Visual Basic Advisor, January 1998
Copyright © Advisor Publications. All rights reserved.

Whether you're developing small applications of two or three tables with data-entry screens and reports, or a client-server system, or downloading a huge data warehouse, at some point you have to think about documentation. Good documentation not only explains to others what you've done, but helps in the development, expansion, and maintenance of the system.

With Total Access Analyzer's latest release for Access 97, you have good documentation and analysis with cross-referencing, error checking, performance analysis, and tips for optimizing your databases and applications.

---

New features

Since Access 97 has more objects, VBA (instead of Access Basic) and many new properties, much of Total Access Analyzer 8.0's new features are expansions of previous capabilities that cover added Access functionality, including:

• New Access 97 objects such as Command Bars, hyperlinks, ActiveX controls, and new object properties.
• User-defined properties for any object.
• Analysis of VBA syntax including new commands and class modules.
• Over 225 reports with increased support for object filtering and thumbnail previews.
• Expanded error checking with over 160 tips to improve your database.
• Quick Doc feature now generates simple documentation for any object, rather than just relationships and basic code.
• New hierarchical TreeView explorer to easily view all the results from a single form; includes a property filter to view a single property's values across multiple objects.

There are many more new features, but before looking at some of them, I'll discuss the basics of creating and printing documentation.

---

Documentation Wizard

With all the new features, Total Access Analyzer needed an expanded interface for creating documentation. This is provided by the Documentation Wizard, which has three pages of forms/dialogs.

With the opening page, you select the object containers or types from an Outlook-style toolbar at the side (figure 1), then select the individual objects from the middle pane, and finally select the options at the right. Note: For the command bars, just select custom for the ones you've created for you application. There's no need to document all the built-in Access command bars.

Figure 1: Documentation Wizard - This is page one of the Documentation Wizard for selecting objects to document.

With page two of the wizard, you select the general options including cross references (Xrefs), diagrams, security, relationships, and whether to compact the database that stores the generated documentation.

On page three of the wizard, you select the path for the output (documentation) database, and whether to run the documentation immediately (press the Finish button), or at a later time. If you choose to schedule documentation at a later time, you can't close total Access Analyzer or Microsoft Access. Once you start documentation, the generating documentation form is displayed showing progress.

When your documentation has been generated, the Documentation Summary Form is displayed with information on analysis of errors and performance problems. Choose the Print button from the Main Menu to print your documentation. There are special reports for errors, suggestions, and performance that can help you both correct problems before they happen, and improve the performance.

---

Quick Doc

The Quick Doc feature provides much more information on this new release. The only drawback is that with the Quick Doc feature you can only document one object category at a time, such as tables or queries. This valuable expanded feature also gives you a number of reports you can select.

---

TreeView Explorer

The new Explorer form provides a folder-style tree view of your documentation When you select a folder, category, or object on the left tree view, the right side provides a tab form with information about the object (figure 2).

Figure 2: TreeView Explorer - This is the top level of TreeView Explorer.

This is an innovative and fantastic way to navigate through hundreds of pages of documentation before printing. Although not a data dictionary, it provides a great way to study your database, for example, the Xref tab.

The Xref tab is a great way to review quickly where a table is used or referenced in an application. Additionally, when you have drilled down to the field level, you can see where a field is used - all without printing the documentation.

The new TreeView Explorer needs some work in the tab forms. There are some inconsistencies in the way the information is presented, such as in the Xref tabs. For tables the order is CallingObjectName and CallingObjectType, and for fields it is CallingObjectType, then Name. I found that although I could hide columns in tabs to make them easier to read, I couldn't unhide them during the current TreeView session. When I exited the TreeView back to the main menu, the columns were reset and unhidden for the next use. Also, the right-most field in a tab isn't expandable or long enough for you easily to read the information. Perhaps a right click to display the entire field in a text box would be easier. The TreeView Explorer is great for viewing errors, suggestions, and performance analysis.

---

Did I do that?

The Exec97.MDB is the application I chose to test for the review. This application is one of several databases in a client's data warehouse, chosen for the review because it was based not on normal Access database procedures, but on automated downloading and processing of data. This definitely provides results for error and performance analysis during the documentation process. The database is query- and macro-based, containing numerous queries, dynamic temporary tables, and reports, but no forms, and only minimal VBA code. This unfinished application also has some unused elements.

One of the errors Total Access Analyzer found during documentation was a macro named RunNoQuit that was not referenced (or used) in the database (figure 3). It's important to be able to know during development if a macro, query, or VBA function isn't referenced, since it would explain why something isn't running or data is missing. This type of error report can also point out an object that's created for testing during the development phase, which should be deleted afterward to clean up the production database.

Figure 3: Error Tab - TreeView Explorer's Error tab for the database NorthWind.MDB shows all errors including unrefferenced (unused) macros.

The Suggestions tab (figure 4) provides all kinds of great suggestions. In this case, analyses found that there were identically named fields in various tables with inconsistent data types. This can be the source of problems with data integrity and calculations. To set your mind at ease, I was aware of these inconsistencies during development. The data warehouse recreates manual spreadsheet reports from dissimilar tables and data, and stores the report data in a holding table. The different data types were Double data fields stored in Currency fields. This is still a good suggestion and would also help in avoiding potential errors in other types of applications.

Figure 4: Suggestions tab - TreeView Explorer's Suggestion tab with suggestions for the NorthWind.MDB database lists possible problems or elements that break good Access database rules and suggestions to fix them.

When I checked the performance tab for the database (figure 5), I found that since there was a VBA module, the database should have been made into a .MDE file. In applications where there's a lot of code and forms, a .MDE file loads and runs faster. In this case, there are only a couple VBA functions that enforce business rules or queries, so a .MDE file wouldn't be a performance improvement.

Figure 5: Performance tab - This is the performance tab for the database, listing tips for improving the performance.

---

Installation

Total Access Analyzer 8.0 works with Access 95* or Access 97. It doesn’t work with Access 2.0, for which you need Total Access Analyzer 2.5. If you're still using Access 2.0, install Total Access Analyzer 8.0 in a different directory from Total Access Analyzer version 2.5. (the two versions use different files so you don't have to worry about overwriting anything.) During installation, you have the option to back up files replaced during installation.

To run Total Access Analyzer 8.0, you need a minimum 486 processor with 16 MB RAM. A Pentium with 32MB RAM is recommended. You need 10MB free hard drive space for installation plus space for your documentation files and another 100MB for Windows to use for a swap file while running Access. Installation is done from a standard Windows 95 setup.

You can also uninstall the application using the Add/Remove Programs from the Windows Control Panel. If you need to uninstall it, the documentation databases will still be in whichever Output directory you specified during installation. You'll have to delete the directory manually using Windows Explorer.

---

Documentation

Despite the greater number of features of version 8.0, the manual is about 60 pages shorter than the manual for version 2.5. The difference is that the version 2.5 manual had about 90 pages of samples of printed output in an appendix. The new TreeView explorer provides information that printed reports provided in the past. The current manual uses one printout sample in the chapter on printing documentation. Nevertheless, the manual tells you how to install, run, print, and manage database documentation. The FMS manuals have been well-organized and well-written from the start, and whether you're new to Total Access Analyzer or upgrading from a previous version, you'll find good documentation, supplemented by context-sensitive online Help via the Help button or F1 key.

---

Summary

Total Access Analyzer 8.0 is an indispensable tool for debugging, analysis and documentation. This add-on runs inside Microsoft Access to document the current open database. You need to take some precautions to run it on a network. The best solution is to copy a network database to a local drive or another network directory to document it, so that no objects are opened, changed, or locked while you are creating documentation. A minor drawback to running the program as an add-on instead of a standalone is that you must open a database in Access to view existing documentation. Since it's run inside Access, network users can only use it one-at-a-time. Additionally, you have to open a database in Access to view existing documentation.

I've used this product since version 1.0 for Access 1.0 and 1.1. Version 8.0 is a great help during the development and testing phases of any application, and in generating final documentation to help clients and management staff better understand their databases and applications.

FMS NOTE: Access 95 databases can be documented, but they need to be converted to Access 97 first.

Dave Chowning is the author of numerous articles on database systems, development and tools.  Dave is a database developer, writer, and instructor based in Bellevue, Washington.

---