Review: Total Access Analyzer for Access 2000

Database Journal by Danny Lesandrini

January 11, 2002

How well do you know your own Microsoft Access database applications? Over time, even simple FMS Inc. has always been at the forefront, arming developers with analyzer tools to simplify application problem solving and documentation. Join me as I explore features of their latest release.

Installation and Getting Started

It's worth mentioning that installation was completely painless and the product setup routine ran flawlessly on every machine I tested. Since Total Access Analyzer 2000 functions as a Microsoft Access Add-In, it's launched by first opening the database you wish to analyze and then selecting Total Access Analyzer 2000 from the Tools >> Add-Ins menu. You may run multiple versions of Total Access Analyzer, just as you can have multiple versions of Access itself, so long as they are installed in separate directories.

There are a couple of things to keep in mind before launching this utility. First and most important: make a copy of your database! Though I have never experienced corruption while using an FMS utility, this is just common sense. Also, due to a bug in Access 2000, some properties may be deleted when some objects are examined programmatically. So, always make a backup before running any documentation tool. Finally, in order to launch Total Access Analyzer your mdb file must be in a fully compiled state with no forms or reports open.

Documenting your Database Application

Once opened, the user interface is clean and easy to understand. The top-most button starts the wizard to generate database documentation. The first page of the wizard prompts you to select the specific database objects (tables, forms, modules, etc.) you wish to document, as well as the object related options. On the second page of the wizard you select additional options to document, such as relationships, security and cross-reference analysis. The final page allows you to designate the location for the output database that will be generated.

By default, all objects and options are selected, but if your application contains many objects, this process will require a great deal of time. (You may want to start the documentation generation and break for lunch!) If you can afford to wait, I say, "Go for it!" You will be amazed at the sheer magnitude of information that is collected about your application.

Alternatively, you could elect to run the Quick Doc wizard. With Quick Docs, you choose a single category, like modules for example. Next you select some simple criteria and run the wizard to perform the analysis in a fraction of the time described above. I found this very useful for printing out module code. Of course, you can print code directly from modules in Microsoft Access, but the FMS report includes line numbers, page headers and the ability to create a "green bar" effect with alternating white and gray sections. This makes the code easier to read, and, by default, is enabled.

Navigating the Documentation

Once the documentation is complete, you have two ways to view the results. First, there is an Explorer style interface that allows you to view information by object group, such as tables, forms, modules, etc. in hierarchical TreeView format.

Object groups in the left panel may be expanded by clicking on the plus sign (+) to expose the documented components that make up each object. As you continue navigating through the hierarchy, the right panel of the explorer displays the corresponding properties and their values, as well as any errors, suggestions and performance tips. By using the explorer interface in this way, you can quickly locate information about a group of objects, such as all queries or tables, and you can zoom in on a specific object to retrieve detailed information about its condition.

The other method for viewing the documentation is through the reports menu. Total Access Analyzer 2000 contains over 280 customizable reports. From the report interface you can elect to run a single report, multiple reports or a saved set of reports. The filter mechanism allows you to include (or exclude) specific database objects. Additional tabs of the filter interface let you select specific errors, suggestions and/or performance tips to report on. I found the reports to be clean and well arranged. Though the volume of pages generated is so great that you'd never want to print the whole set of reports, the filtering interface is easily configured and powerful enough to help you focus in on the specific information you need to extract. For example, there are a set of cross reference reports that make it easy to see where queries are referenced by reports, forms and controls like combo and list boxes.

What's New in Total Access Analyzer?

For those of you familiar with previous versions of FMS Total Access Analyzer, you will be pleased to learn of the following enhancements and additions. I've included the version 9.0 features for those who haven't seen the last release of the product.

Version 9.0

• Support for Access Data Project (ADP) objects with SQL Server 2000.
• Locates variables not expressly defined as either ADO or DAO.
• Support for VBA 6.0 syntax, including Enum, Events and Raise Events.
• Additional report filtering and grouping ability.
• Documentation to improve cross-referenced object reporting.
• Form and Report control blueprints.

Version 9.5

• Search feature for locating specific word or phrase across all objects.
• Improved performance.
• Improved international support.
• Enhanced module documentation and suggestions.

What I Liked Most

After running the documenter on a second database I began to panic, thinking that perhaps I had overwritten the documentation of my first database (which required an hour to compile). I scrambled for the user manual (it comes with a printed manual) and quickly located the section, Selecting Another Output Database to View. Yes, multiple output databases are saved for future reference and comparison. Over time you could collect what amounts to "snapshots" of your database schema, objects and their properties. That's a great feature!

The best feature, hands down in my opinion, is the list of Errors, Suggestions and Performance Tips. With each database I tested, Total Access Analyzer 2000 revealed numerous items that required my attention. These included important things that I had missed or overlooked, such as:

• Identically named fields have inconsistent data type and size.
• Table that has no primary key assigned or has duplicate indexes.
• Identify unused objects, like queries.
• Unused constants, variables and procedures.
• Broken references (for example, to control what's been renamed or deleted)
• Variables and procedures that could be scoped as private.
• Recordset and database objects not explicitly declared as ADO or DAO.
• Duplicate letter 'V' as a HotKey (shortcut) for 2 different form controls.
• Debug.Print statements remaining in code modules.

The FMS Web site contains the complete list of errors, suggestions and performance tips that the analyzer searches for and is worth a quick look. In addition to identifying these problems in your application, a complete explanation is given regarding why these issues are undesirable. Reading this documentation taught me things about Access I had not learned in five years of reading Access books and writing Access applications!

The Verdict

I've always considered myself somewhat of an Access purist, sticking with native Access controls, tools and utilities but this is one Access utility that's worth the money. A single user license currently sells for $299, and pricing for upgrades, 5-packs and volume pricing is available at the FMS Web site.

As with every code tool you consider for purchase, you have to ask yourself if, in the long run, it's going to save you development hours. Total Access Analyzer 2000 will not only save you time, but more importantly it will make your deliverable more efficient and will reduce code-related problems. Even the most meticulous programmers would benefit from a second set of eyes when reviewing hundreds of objects and thousands of lines of code. With Total Access Analyzer 2000, you get that second set of eyes, and you'll be able to say with confidence, "I know what's in my Microsoft Access database application!"

Additional Awards and Reviews of Total Access Analyzer