SPEED Ferret Propagates Object Name Changes With Ease

Paul Litwin


This Access add-in from Black Moshannon Systems lets you search and replace object names throughout your database schema. SPEED Ferret is fast, friendly, flexible, and safe¾ and it comes in both Access 2.0 and Access 95 versions. Highly recommended!


Have you ever inherited a project that used field names you wanted to change? Or perhaps youíve created some funky table or query names that you now regret. In either case, if you built many dependent objects on those names, youíll face a major headache when you decide to root out and fix all those dependencies. SPEED Ferret from Black Moshannon Systems (the folks who brought you Tracker) is meant to make the name change process effortlessóor at least a lot less work.

There are two versions of SPEED Ferret: one for Access 2.0, the other for Access 95. Once installed, you invoke SPEED Ferret like any other Access add-in using the Tools|Add-ins (Files|Add-ins in Access 2) menu. SPEED Ferret starts by displaying an opening screen complete with a cute animation of a ferret driving a race car across your screen. I personally enjoy the welcome screen, but if you prefer, clicking on a check box will rid future sessions of the fun. You specify searches using a dialog box thatís similar to the Access Edit|Replace dialog, but while Accessí Replace dialog searches and replaces strings in your data, SPEED Ferret searches and replaces strings in your schema. The add-in looks for strings in either the entire database or some user-specified subset of tables, queries, forms, reports, macros, modules, relationships, and import/export specifications (see Figure 1).



Figure 1. By default, SPEED Ferret searches every object in the database, but you can easily narrow the search by clicking the Selected Objects Only check box and selecting only the desired objects.


By default, searches are unrestricted. SPEED Ferret is quite flexible, however, and allows you to restrict the search to whole word or case-sensitive matches. You can also specify pattern match searches, or you can use the Object Criteria section of the dialog to tell SPEED Ferret to look for the search string only in objects with a name or owner matching a specified pattern, or objects modified after a certain date.

After you specify a search and click on the Preview button, SPEED Ferret displays a progress meter while it pores through your objects looking for the search string. To improve the speed of subsequent searches, SPEED Ferret caches the text properties of all database objects into several external files. After the search is complete, the program displays the results of the search in the Preview Window with a row for each match that was found (see Figure 2). The Preview Window is used for both search and replace operationsóthis is a nice safety feature that prevents you from making any changes before seeing the effect of those changes.



Figure 2. You can discard any false matches by deleting the records in the Preview Window.


If youíve requested a replace operation, the Preview Window displays the effected object name, the effected property name, the current property value, and the proposed property value. If youíve requested a search (without a replace), only the first three columns are displayed. If SPEED Ferret canít change a property because itís read-only, it tells you this but includes the property in the list anyway so you can print out the list and make the change manually. This might happen, for example, if you donít have permission to modify an object or if the object is an attached/linked table.

A variety of options available from the Preview Window let you sort, filter, zoom, and print the search results and the proposed replacements. If you want to skip a proposed changeófor example, you likely wouldnít want to change Caption, Description, and Name properties for the Categories form in Figure 2ósimply delete those rows from the Preview Window. You can also fine tune any replace operations by directly editing entries in the "Replace With" column. Once youíre satisfied with your edits, click on the disk icon to commit your replacements to disk.

When I asked the Access 95 version of the utility to replace every instance of the word "Categories" with "tblCategories" in the Northwind sample database, it took just under a minute to fill the property caches and display the Preview Window on my Pentium 90 with 40M RAM. Because of the property caches that SPEED Ferret built on the first replace operation, subsequent operations took only 20-30 seconds. Committing my changes took, on average, another 10-20 seconds.

SPEED Ferret is a well-designed product with lots of extra touches. For example, the program correctly handles renamed controls with attached event proceduresóa not-so-trivial task. Itís also smart enough to handle all the properties of the custom (ActiveX) controls that ship with the ADT. For all other custom controls, SPEED Ferret handles the Class, ControlSource, ControlTipText, OLEClass, OnEnter, OnExit, OnGotFocus, OnLostFocus, OnUpdates, and Tag properties.

SPEED Ferret has a couple of built-in safety features. First, it will operate in a read-only find mode unless you have the database open exclusively. Second, it makes backup files of your database before it performs any replacements (although the documentation warns you that itís safest if you make your own backups too).

The Access 95 version of SPEED Ferret requires you to set a reference to the Ferret20 library in each database with which you wish to use it. When you attempt to use the program without first setting a reference to it, SPEED Ferret offers to set the reference for you using a SendKeys statement. Itís a tribute to Black Moshannon Systems that they were able to come up with this workaround that is necessitated by a serious shortcoming of the Access 95 add-in model.

Having used both versions of SPEED Ferret for over a year now, itís hard to come up with anything bad to say about the product. Itís fast, friendly, flexible, and safe. I highly recommend SPEED Ferret and canít imagine Access development without it.

 

Paul Litwin is the editor of Smart Access and a senior consultant with MCW Technologies, a Microsoft Solution Provider specializing in desktop solutions employing Access, Visual Basic, and SQL Server. Paul co-authored two Access 95 books: Microsoft Access 95 Developer's Handbook (Sybex) and Microsoft Access 95 How-To (Waite Group Press). Fax 206-281-1933. CompuServe 76447,417. Internet PaulLitwin@compuserve.com, http://ourworld.compuserve.com/homepages/PaulLitwin.



Reprinted with permission from
© Smart Access
All rights reserved
Pinnacle Publishing, Inc.
P.O. Box 888
Kent, WA 98035-0888
Tel 206-251-1900, Fax 206-251-5057