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.
- 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.
- 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!
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!”
This article originally appeared in the November 2001 issue of Smart Access, Pinnacle Publishing, Inc. (c) All rights reserved.