Total Access Speller for Access 2002/2003

Danny J. Lesandrini

You only get one chance to make a good first impression. What first impression do your Microsoft Access applications give? No matter how clever your code, when a client sees misspelled words on forms and reports, your user interface screams, "Loser!" Spell-checking object properties in Access can be an error-prone manual process, unless you're using Total Access Speller 2002/2003 from FMS. Danny Lesandrini takes it for a test drive.

Note: We are resellers for FMS

Though I was born in the United States, my wife sometimes questions whether or not English is my native language. You see, I'm living proof that a college education doesn't guarantee good grammar and correct spelling. In fact, after learning to speak Russian, I'm now able to misspell words in two languages. I'm not alone—we all make spelling mistakes. Even if we know how to spell a particular word, there's always the possibility that we might "fat finger" the keyboard as we type it. We should be checking our forms and reports for typos, but, again, human imperfection can cause us to overlook our own errors.

I was excited to hear that FMS Inc. has an object property and design spell-checking product for Microsoft Access database applications. The add-in, named, appropriately enough, Total Access Speller 2002 (TAS), has a list price of $199 for a single license. There are separate versions for Access 97, 2000, and 2002, 2003 and 2007.

Installation and getting started

As is true with all FMS products I've used, installation is easy and foolproof. The setup routine puts a program group on the Start | Programs | FMS menu in a folder named Total Access Speller 2002, alongside any other FMS products you may have installed. You can't launch TAS from the menu because it's a Microsoft Access add-in that can only be run from within the Microsoft Access environment with an mdb file opened. The program group menu does, however, provide shortcuts to the Help file and to the Update Wizard (which will keep your product up-to-date). The installation doesn't require a restart of the computer.

To spell-check an application, you begin by opening any database. As always, it's a good practice to make a backup before using any utility that makes changes to object properties. You'll need to close any open forms or reports in order to start TAS, though when I ran tests, it didn't seem to matter if I had tables or queries open. To load the utility, select Total Access Speller from Access's Tools | Add-Ins menu. After viewing the Welcome screen, you're ready to select the objects (tables, forms, queries, and so forth) that you want to include in the spell-checking process from the object selection form (see Figure 1).

Figure 1 - Selecting the objects

Running the Utility

There are a number of things to keep in mind when selecting objects for spell-checking. The first database application I spell-checked was very small (about 600KB) and contained only eight forms, two queries, and one local table. Since the application was so small, I selected all of the objects in the database, which was a mistake. It took forever to spell-check the BuiltIn CmdBars, since all of the properties were programmer-created words like "AutoForm" and "WebViewFolderIcon." Not only did these words all show up as misspelled, changing any of them would surely have caused the command bars to break. Though I can understand why I'd want to spell-check my own custom User CmdBars, I don't see any reason why I'd ever need to spell-check the BuiltIn CmdBars.

Fortunately, the user interface for selecting objects makes it simple to exclude these objects. First, I chose the All Objects option and clicked Select All. Then I chose the BuiltIn CmdBars and clicked on Clear All, which cleared all the toolbars but left all other objects.

You may have noticed that there's a button on the object selection form to edit the Property List (the list of properties to be spell-checked). This list isn't comprehensive, containing only the 20 properties most likely to require spell-checking. By default they're all selected, but you can filter for the specific properties you want by checking or unchecking the check box next to each property. In addition to being able to exclude various properties from the spell-check search, you may add additional properties that aren't in the default list. Caution is needed when adding additional properties. You wouldn't want, for example, to change a BackColor property from "vbWhite" to "White," as this will cause an error when you attempt to compile or run the application. It's appropriate that FMS included this option, especially if you're in the habit of creating custom properties. In all likelihood, you'll probably find that the FMS list is optimal and won't be adding or deleting properties from this list.

Clicking the Next button starts the process of collecting metadata about your application. This was fast for my little database. It initially took hours for my largest database (a 24MB "mother of all Access applications"). It took so long that I finally gave up and canceled the operation after two hours. The problem turned out to be linked tables to a SQL Server database that wasn't available because I was disconnected from the network. Removing the linked tables solved the problem (I could also have plugged in to the network to connect to the database). I also complied, compacted, and repaired my database. After these changes, TAS processed 240 forms, 60 tables, and 170 reports in just a few minutes to present me with the Spell Checking screen, as shown in Figure 2. When I got bored waiting for my initial run to complete, I tried running a second copy of TAS to simultaneously spell-check two different databases. This definitely doesn't work, as it causes a conflict in TAS's metadata tables.

Figure 2 - the Spell Checking screen

From this point on, spell-checking proceeds just as if you were spell-checking field values in an Access table. Clicking the Spell Check button brings up the familiar-looking Access spell-check window, where you can Ignore or Change misspelled words as they're found. The Options button allows you to ignore words all in uppercase, words containing numbers, URLs, and file path names. There's also another button at the top-right of the form, which allows you to exclude all properties of the current field. However, clicking it is the same as saying you want to ignore all misspelled words and will end your spell-check session.

Making changes

It's important to note that no actual changes take place during this process. All you're doing is correcting misspelled words in the metadata table of the FMS mde file, not in your application's mdb. Once you finish spell-checking, you must click the Next button to request that your changes be committed to their corresponding database object properties. Prior to committing the changes, you have the opportunity to view and print a report with all pending changes. Clicking the Back button clears the buffer of pending changes and lets you begin the process anew.

The report of pending changes is clean and easy to read. After confirming the changes, you're presented with the final Changes Report (see Figure 3), which looks a lot like the Pending Report. I strongly suggest that you print a copy of this report so you'll be able to review and correct any mistakes made during the spell-check process. Upon closing the report, you'll find yourself back at the Welcome screen, ready to request another spell-check session on a new group of objects.

Figure 3 - the final Changes Report

What's the verdict?

Personally, I can't imagine an Access developer who wouldn't benefit from this tool. It's a utility that I'll keep handy and use often. I was disappointed that there wasn't an option to spell-check the code in standard and class modules, but I appreciate the complexity of that task. Even from a user's point of view, it's problematic to spell-check code where function, sub, and variable names break all the rules of spelling. Even so, there are times I'd like to have my comments and MsgBox text checked for spelling errors. Perhaps this feature will be included in a future release. (Note to self: Remember to run TAS's Update Wizard from time to time.)

Pricing for upgrades, five-packs, and volume pricing is available from Garry's FMS Software resale page . At first, I thought that $199 for a single-user license was high. Other FMS tools (for example, Total Access Analyzer) do much more and appear to deliver more functionality for the money. After all, the user interface for Total Access Speller is simple, and it provides only one function. However, it's a function that will help you avoid giving an unprofessional first impression. Some tools save you time; some save face. The way I figure it, avoiding personal embarrassment in front of my clients makes this product worth the price.