If you have spent any time developing and programming applications, you're familiar with some of the maintenance requests that come in. For example, one client wanted me to go through a production program and change all occurrences of the word "client" to "customer." This involved wading through 10 Visual Basic/VBA programs, one Access 97 database, and a SQL Server database. Needless to say, it was time-consuming and mind-numbing work and not an experience I want to duplicate.

Luckily, Black Moshannon Systems (BMS) has a simple, yet elegant solution to this problem. SPEED Ferret 4.0 saves you time and energy, not to mention a lot of headaches.

What is SPEED Ferret?

In short, SPEED Ferret is a general search-and-replace program for Visual Basic/VBA programs, as well as Access and SQL Server databases. The program helps you find and replace strings in programs, forms, reports, and databases.

Even though all source code editors provide find and replace functionality, these built-in tools are still rather limited. They can't, for example, locate the text in label captions, field names, or database tables, not to mention strings embedded in forms, reports, and queries.

SPEED Ferret solves this global update problem through a four-step process:

Step 1: Search -- SPEED Ferret searches your project to find all the properties that match your search criteria. The results of the search are displayed in a datasheet.

Step 2: Review/Edit -- You can simultaneously review and edit all the references SPEED Ferret has located. You can select the specific rows you want to replace, then directly edit their replacement values. You can sort the search results in any desired fashion and print a neatly-formatted hardcopy.

Step 3: Save -- After you're satisfied with the list of replacements, you can save all your replacements at one time.

Step 4: Testing -- You can test your changes by exiting SPEED Ferret. If your changes have caused any problems, simply restart SPEED Ferret, restore the previous session, and undo any specific replacements, while leaving all other changes intact.

Interface and use

SPEED Ferret has a simple interface that makes the program easy to use. It presents you with a small window with six tabs. The first tab is where you choose the project you want to work on. This can be an Access database, SQL Server database, or a Visual Basic project. There isn't support for applications written in VBA for the other Office applications, such as Outlook, Excel, or Word -- a major omission I hope will be rectified in future versions.

After you add your project to the list, you're ready to go. You can perform a search and replace by going to the Search tab and clicking on the New button. A wizard helps you choose which areas of the project to search. There's one caveat, however: The more areas you choose to search, the longer your search will take. For example, if you know you only have to change the text of a field label on forms and reports, you don't have to search all the source code, queries, macros, etc. You can just select forms and reports and go from there.

The Design view gives you a second way to create searches. In this case, you have to specify everything except the project to search manually.

The Design view also acts as the secondary search mode for the Wizard view. After you fill out the Wizard's forms, the Design view displays, and you can tell the program in more specific detail if there are properties, objects, and collections you want to search. There seems to be no advantage to using the Design view first -- I'd use the Wizard as my primary search mode, then do the fine detailing using the Design view.

After you choose the search areas, you must tell the program what to search for. A new dialog will open (the same dialog used in the Design view), and you must specify the type of search: find, find and replace, multiple replace, or a list showing where you can find the information you want.

SPEED Ferret lets you set both object and property filters, giving you some more advanced features. These filters let you select only those objects that meet extended criteria, such as read-only objects, labels, etc. You can also set the object and property filters outside a search and use them in other searches. There are individual tabs in the dialog box for each of these criteria.

After you have all your search criteria, click on the Start button. A list of results will appear in a datasheet, which you can use to verify the changes to be made. If you want to exclude any object, highlight it, right-click on the changes, then select Exclude from the pop-up menu.

Another powerful feature of SPEED Ferret is the ability to set substitution lists. Substitution lists let you perform multiple find and replace operations in a single pass. This can make life simple if you have several changes to make. With Substitution lists, you can set them all in place at the same time and execute the program once. It might take a long time, but it'll be faster than performing multiple replacements in the program because you won't have to go through the selection process again.

Manual and help

The manual and the help files contain the same information. The text is clearly written, and there are plenty of screen shots to help you understand what is going on and why. The documentation covers all aspects of the program and is useful as you decide to get into the more advanced functionality of the program.

Get the job done

I like SPEED Ferret because it is simple, easy-to-use, and gets the job done. I have only one minor complaint: the lack of support for other VBA-oriented applications. With more people using VBA to tie together the Microsoft Office suite, this needs to be addressed. However, if you need to make global changes in Access, SQL Server, or Visual Basic, this tool is for you.