Product Review: SPEED Ferret 4.0



What’s in your toolbox? There was a time, not that long ago, that the only 3rd-party tool I used on a regular basis was SPEED Ferret 3.0, a well known global search and replace add-in for Access 97. Over the years, I’ve picked up a few others, but SPEED Ferret is still the first and most important Access tool I install when reloading my development box. How does version 4.0, released last year, compare with the previous version? Does SPEED Ferret 4.0 belong in your toolbox?

What is SPEED Ferret?

SPEED Ferret began as a simple search and replace tool for Microsoft Access. I’ve been using it since Access 97 was released, and I can’t tell you how many times it’s paid for itself. One particularly memorable example was when one of my clients asked for a change in his hearing aid business database. Throughout the application, salespeople were referred to as Specialists, and he wanted the term changed to Practitioners. That meant changing field names in tables, as well as references to those fields in queries and form & report controls. What would have required days of tedious development time was reduced to a few minutes, thanks to SPEED Ferret. I was able to make the changes quickly without breaking the application, and I came out looking like a hero to my client. SPEED Ferret 3.0 paid for itself on that single occasion, and I became a loyal customer.

Version 4.0

The latest release of SPEED Ferret, version 4.0, is considerably more powerful. In order to keep the product up to date with current practices and technologies, the developers at Black Moshannon determined to include functionality to search and replace across the MDB boundary. They realized that many Access databases were split into front-end/back-end applications, some being Access ADP’s and that, more and more often these days, data is being stored in SQL Server databases. The developers realized a new version of SPEED Ferret would best serve developers if it included the ability to search and replace in multiple projects simultaneously. So, you can now search multiple MDB files, SQL Server databases, and, as an added bonus, Visual Basic projects all at the same time!

How it Works

Unlike the Access add-in that was its predecessor, SPEED Ferret 4.0 provides its own separate interface, which makes sense since it now searches SQL Server and VB projects, in addition to MDB files. The GUI is well designed and relatively intuitive, considering the complexity of what it has to accomplish. As you can see in Figure 1, the program provides a tab which lists user-defined and saved projects, searches, object filters, property filters and substitution lists. In my example, I saved four Access 97 MDB projects, an Access XP (Access 2000 format) project with its corresponding SQL Server 2000 database, and a VB application named TestADOSQL. Note that although SPEED Ferret only officially supports SQL Server 7, I had no difficulty working with SQL Server 2000 databases. Your results may vary, however, depending on whether or not your database uses SQL Server 2000-specific features (See Figure 1).

SPEED Ferret main browser window

The top and side toolbars are easy to understand and use. They remain constant regardless of which tab is currently active. For example, no matter which tab you’re in, you add new items to the list by clicking New. Clicking Open brings up a window with details about the selected object. In Figure 2, you see an example of the details given for a saved project. Since the object is an Access database, you have the ability to browse the list of forms, tables, queries and the like. As you can see, the right-click menu exposes some interesting features, such as properties of the object, a quick search called “Where Used” and even the ability to open the object in design view. (See Figure 2.)

Project object detail window

The real work is done in the Search window. When creating a new search, you can go directly to design view, indicating that you want to search all saved projects, or use a wizard interface to select specific projects. Once at the search window itself, you can specify the sort of operation, be it a Find, Replace or Enumerate, which simply gives you a list of all selected objects and their properties. Multiple Find and Multiple Replace take advantage of user-defined substitution lists, where multiple criteria can be searched at the same time. (See Figure 3.)

SPEED Ferret search criteria and results screens

In addition to defining substitution lists, there are tabs where the user can pre-define object and property filters. Object filters allow you to customize the type of objects searched. For example, you could create a filter which examines only a particular category of objects, such as forms or tables. You can also limit the search by object name, using wild card characters to search for patterns. The same is true for property filters. You could define a filter that searched only Tool Tip and Status Bar text properties. For that filter, the “builder” generated the following string: {*.*.ToolTipText} + {*.*.StatusBarText}. Tips for creating complex searches can get very involved and could constitute an entire article of their own, but suffice it to say that with the use of wildcard characters you can greatly decrease the scope of your searches, saving time and focusing on only those objects and properties that you really want to find and/or replace..

The search screen allows you to flip back and forth between criteria and results. Options on the file menu of the results tab provide a way to save the results to a text file, commit some or all pending replacements and even undo previously committed replacements. That’s a handy feature that didn’t exist in the previous version! Also, it’s noteworthy that SPEED Ferret automatically determines the correct sequence in which to apply replacements, saving you the trouble of running search and replace multiple times.

One interesting new feature is the Interfaces tab, which some Access programmers may find a little confusing. Even after reading the help file, I wasn’t sure how to use it myself. The fact is, you could run SPEED Ferret indefinitely without ever concerning yourself with Interfaces. It’s there, primarily, because the utility needs to have an interface for each type of object that it inspects. The interface provides information that the control’s type library does not provide. Once a DLL or OCX has been loaded, its object model is exposed and a report of its properties and methods can be generated. I’ve used this report to print object model documentation for VB components created by previous developers. While not originally intended as a tool for documenting the design of components, it does a decent job of it and has come in handy more than once.

Issues Fixed in Service Release 1

Some Access programmers I’ve spoken with believe that the developers at Black Moshannon have bitten off more than can be “chewed”, in a technical sense. Indeed, the objective list for the new version was considerable and would prove to be technically challenging. Providing global search and replace for multiple Access, SQL Server and Visual Basic projects is no small task. The first time I reviewed SPEED Ferret 4.0 back in August, 2001, I ran into some issues that caused some problems.

For example, there was a problem with linked tables. Remember, SPEED Ferret 4.0 is able to make changes to multiple projects, updating the “front-end” and “back-end” MDB files simultaneously. The named links to tables in the “back-end” database were changed, as were the actual table names in the “back-end”, but the SourceTableName property of the link could not be changed. As a result, the link no longer worked, requiring manual deletion and relinking for the affected table. I’m happy to report that this issue has been fixed in SR-1.

Another error fixed in the latest service release has to do with the way SPEED Ferret handles VBA references while scanning Visual Basic projects. Before SR-1, I had to remove references in my VB project to get SPEED Ferret to run. Upon reporting this to Black Moshannon I received the following reply: “None of the rules used by VB6 in checking these libraries is documented, so we had to make some assumptions. We’ll be posting a knowledge base article on this issue in the near future, and a free update will be provided to correct this and several other issues.” According to my tests, this issue no longer exists in the most recent release. If you currently own a pre-SR-1 version of SPEED Ferret, I encourage you to get the free download from their web site, http://www.moshannon.com.

After that issue was resolved, the search and replace worked great with Visual Basic 6 and, as would be expected, the changes were “real time”. I purposely committed some changes that would break my code so I could test the Undo feature. Even after shutting down and reopening SPEED Ferret, I was able to review the changes I had committed (which broke my VB project) and undo selected replacements, restoring my app to a functional state. SPEED Ferret remembers changes made. That’s impressive!

What’s the Verdict?

Prior to the recent service release, I described SPEED Ferret 4.0 as a “version 1.0” utility, suggesting that it was more like a new product than an upgrade from the version 3.0 add-in. As such, it was prone to the sort of issues associated with an immature product. Although I still hold to the idea that SPEED Ferret 4.0 should be viewed as “new”, considering the radical changes to the user interface, the way the product is used and the extensive new feature set, I can’t say that it’s immature anymore. The core technology used is based on years of research and experience, and as far as I can tell, the issues that originally concerned me have all been fixed.

The ability to work with multiple projects and the inclusion of VB and SQL Server is great. Also, saving project groups, search definitions, filters and substitution lists makes using this tool truly convenient. On top of that, this is one reasonably-priced utility. All of the functionality described above for just $149! (Only $49 for a product upgrade.) For a consultant who charges $50 per hour, that’s only 3 hours work. This is one tool that easily pays for itself and is, in my opinion, well worth the money.


This article originally appeared in the Smart Access Newsletter, Pinnacle Publishing, Inc. (c) All rights reserved.




See All Articles by Columnist
Danny Lesandrini

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles