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, it screams, "Loser"! Up until now, spell checking object properties in Access has largely been a manual process. All that changes with the release of Total Access Speller 2002 from 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. Joking aside, 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. Sure, we should be checking our forms and reports for typo's, but again, human imperfection can cause us to overlook our own errors.
Needless to say, I was excited to hear that FMS Inc. was coming out with a new product that would provide spell checking for Microsoft Access database applications, named appropriately enough Total Access Speller 2002 (TAS). The TAS add-in supports Access 97, 2000 and 2002 versions of Access and costs $199 for a single license.
Installation and Getting Started
As is true with all FMS products I've used, installation is easy and foolproof. Setup 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). There is no shortcut in this menu to launch TAS as it is 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 provide shortcuts to the Help file and to the Update Wizard, which will keep your product up to date. Incidentally, the installation did not require a restart of my 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; although when I ran tests, it didn't seem to matter if tables or queries were already open. Load the utility by selecting Total Access Speller from the Tools | Add-Ins menu. After viewing the Welcome screen, you are ready to select the objects (tables, forms, queries, etc.) that you want to include in the spell checking process. (See figure below)
Tricks to Running the Utility
There are a number of things to keep in mind when selecting objects for spell checking. The first database application I ran a spell check on was very small (about 600 Kb), containing only eight forms, two queries and one local table. Because it was so small, I selected all objects at once, which turned out to be a mistake. It took forever to spell check the "BuiltIn CmdBars", since all the properties were programmer-created words like "AutoForm" and "WebViewFolderIcon". Not only did they all show up as misspelled words, but 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 would ever need to spell check "BuiltIn CmdBars". Fortunately, the user interface for selecting objects in TAS made it simple to exclude them. 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 Figure 1 to edit the Property List. This list is not comprehensive, containing only the 20 properties most likely to require spell checking. By default they are all selected, but you can filter for the specific properties you want by checking or unchecking the checkbox next to each property. In addition to being able to exclude various properties from the spell check search, you may add additional properties not in the default list. Caution is recommended when adding additional properties. You wouldn't want, for example, to change a BackColor property from "vbWhite" to "White", as this would cause an error when you attempt to compile or run the application. It's appropriate that FMS has included this interface, especially if you are in the habit of creating custom properties, but in all likelihood, you probably won't be adding or deleting properties from this list.
Clicking the Next button starts the process of collecting metadata about your application. Although this was fast for my little database, it took hours for my largest one, a 24 MB "mother of all" Access applications. I finally gave up and cancelled after two hours, opting instead to run it on one group of objects at a time. Forms first, then reports, User CmdBars next, and finally queries. It processed the 240 forms in just a few minutes, which surprised me, until I remembered that FMS utilities cache metadata in local tables for future analysis. So, although I had earlier cancelled the process, it was not a complete waste of time. TAS was able to use the metadata previously collected to quickly get me to the spell checking screen, as shown in Figure 2 below. (One down side to this feature is that you cannot run two instances of TAS, simultaneously spell checking two different databases, without causing a conflict in the local metadata tables.)
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 are found. The Options button allows you to ignore words in all upper case, words containing numbers and Internet and file addresses. There's another button at the top right that allows you to exclude all properties of the current field, but clicking it is effectively the same as saying you want to ignore all misspelled words and will end your spell check session.
It's important to note that no actual changes take place during this process. All you are accomplishing is to correct misspelled words in the metadata table of the FMS MDE file. 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 an opportunity to view and print a report with all pending changes. Clicking the Back button clears the buffer of pending changes, and returns you to the beginning of the process.
The report of pending changes is clean and easy to read. After confirming the changes, you are presented with the final Change Report (see Figure 3), which looks a lot like the Pending Report. It is strongly suggested that you print a copy of this report so you will be able to review and correct any mistakes made during the spell check process. Upon closing the report, you find yourself back at the Welcome screen, ready to request another spell check session on a new group of objects.
What's the Verdict?
Personally, I think that the Access developer community is in desperate need of this tool. It's a utility that I will keep handy and use often. I was disappointed that there was no option for examining 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 that Update Wizard from time to time.)
As mentioned above, a single user license currently sells for $199 and as usual, pricing for upgrades, 5-packs and volume pricing is available at the FMS Web site (http://www.fmsinc.com). At first, I thought the price was a little steep; other FMS tools (Total Access Analyzer, for example) provide much more functionality and appear to deliver more bang for the buck. Yes, the user interface for Total Access Speller is simple and it provides only one function, but it's a function that will help you to 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.
This article originally appeared in the Smart Access Newsletter, Pinnacle Publishing, Inc. (c) All rights reserved.