Danny J. Lesandrini
How well do you know your own Microsoft Access database applications? Over time, even simple projects collect unused objects, hidden errors, and performance bottlenecks. FMS Inc. has always been at the forefront, arming developers with analyzer tools to simplify application problem-solving and documentation. Join Danny Lesandrini as he explores the features of its latest release.
What’s the latest version of FMS Total Access Analyzer (TAA) like? My initial experience was a good omen—installation was completely painless, and the product setup routine ran flawlessly on every machine I tested. Having experienced no installation problems, I was ready to analyze a database.
Note from Garry. For Pricing on the latest versions of FMS, see our FMS Software resale page and when you purchase from us, you get bonus software.
Documenting your database application
Once TAA opens, the user interface is clean and easy to understand. The top-most button starts the wizard that will generate your database’s documentation. The first page of the wizard prompts you to select the specific database objects (tables, forms, modules, and so on) 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, the analysis process will take a long time. (You might want to start the documentation generation and break for lunch!) If you can afford to wait, I say, “Go for it!” You’ll be amazed at the sheer magnitude of information that’s collected about your application.
Alternatively, you can run the Quick Doc wizard. With Quick Docs, you choose a single category, like modules, and then select some simple criteria before running the wizard to perform the analysis. This will take a fraction of the time described previously. I found Quick Docs 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 and page headers. TAA’s default is to print with a “green bar” effect using alternating white and gray sections. This makes the code easier to read.
Navigating the documentation
Once the documentation analysis is complete, you have two ways to view the results. First, there’s an Explorerstyle interface that allows you to view information by object group (for example, tables, forms, modules, and so on) in hierarchical TreeView format (see Figure 1, on page 14).
Object groups in the left panel may be expanded by clicking on the plus sign (+) to expose the documented components that make up each group. 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 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. TAA 2000 contains more than 280 customizable reports. From the report interface, you can elect to run a single report, multiple reports, or a saved set of reports. The report system’s filter mechanism allows you to include (or exclude) specific database objects. Additional tabs in 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 that you need. For example, there’s a set of cross-reference reports that make it easy to see where queries are referenced by reports or forms and by controls like combo and list boxes.
Figure 1. Total Access Analyzer 2000’s user interface organizes information into a tree and tab format
What’s new in TAA?
For those of you who are familiar with previous versions of FMS TAA, you’ll be pleased to learn that there are several new enhancements and additions. Combining the lists for versions 9.0 and 9.5, the complete enhancement roll call includes:
• Support for Access Data Project (ADP) objects with SQL Server 2000
• Location of 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 words or .phrases across all objects
• Improved performance
• Improved international support
• Enhanced module documentation and suggestions
Some features were more important to me than others. As I was running TAA on a second database I began to panic, thinking that I’d overwritten the documentation of my first database (which required an hour to compile). I scrambled for the user manual (TAA comes with a printed manual) and quickly located the section “Selecting Another Output Database to View.” Fortunately for me, 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.
The best feature, hands down in my opinion, is the list of errors, suggestions, and performance tips. With each database I tested, TAA 2000 revealed numerous items that required my attention. Important things that I’d missed or overlooked included:
• Identically named fields with inconsistent data type and size
• Tables that had no primary key assigned or had duplicate indexes
• Unused objects (for instance, queries)
• Unused constants, variables, and procedures
• Broken references (for example, to controls that have 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 two 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 hadn’t 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, five-packs, and volume pricing is available at Garry's FMS Reseller Page.
As with every code tool you consider for purchase, you have to ask yourself whether, in the long run, it’s going to save you development hours. TAA 2000 will not only save you time, but, more importantly, it will make your deliverable more efficient and 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 TAA 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!”
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’ve never experienced corruption while using an FMS utility, backing up your database is just common sense. Also, due to a bug in Access 2000, some objects can lose some of their properties when they’re examined programmatically. So always make a backup before running any documentation tool. In order to launch TAA, your MDB file must be in a fully compiled state with no forms or reports open. Since TAA 2000 functions as a Microsoft Access Add-In, it’s launched by first opening the database you wish to analyze and then selecting TAA 2000 from the Tools | Add-Ins menu. You can run multiple versions of TAA, just as you can have multiple versions of Access itself, as long as they’re installed in separate directories.
*** A great interactive way to backup your database is with Garry's Access Workbench ***