For Microsoft Access |
 |
by Robby Thompson |
Background
SPEED Ferret (SF) is an Access Basic program that performs global search and replace features on your Access databases. This third-party utility is available in both Access 2.0 and 7.0 versions from Black Moshannon Systems. Currently, Microsoft doesn't give you a way to search objects globally in a query format like SPEED Ferret can. The closest you get in native Access is to do a find and replace (use the verify choice) and go to each object reference one by one to ensure you change the right object reference. This works fine within a module, but what about in queries, tables, and so on?
The need for such a tool is in high demand for primarily three reasons. First of all, Access distributed programs are growing in sophistication as users become accustomed to more and more features, thus driving the need to better organize code during development and after it is released. Haven't you ever wanted to rename a reference or two? The problem is that if you forget to catch even one, your program component(s) will not work. SPEED Ferret allows you to propagate references name changes throughout the database (tables, queries, forms, reports, macros, and modules).
Secondly, most independent and corporate developers inherit an existing application that begs for cleanup. For example, have you ever taken over the stewardship of an app only to find it riddled with references like Forms!frmCustomer.Field20? Allowing the name of something to remain with the default given by Access is just a plain poor programming habit. Believe me, bad programming habits will bite you in the "south forty" sooner or later.
Finally, SF will aid you in finding that cool code you have already authored and need to find. Just type in part of it and let SF chase it down. I think this alone will make it worthwhile to have SF as part of my development toolkit.
How To
Once SF is added in from the Access Add-In menu, it runs at the same time as your source code does. From the File menu, choose Add-Ins, then SPEED Ferret. Just beware of your parameter choices; otherwise, you can get Field 120 when you really meant Field 12. Keep in mind, you can document the changes you made as well. SPEED Ferret allows users the flexibility for custom report headings to provide as little or as much information as you want. However, the value of documentation is easily lost if you go overboard documenting to the "nth degree". Just record what you need to and you'll be happy you did. Searches are fairly straight forward and easy. Just type into one field which reference to search for and its replacement in the other field. SF provides a view of the object name, the reference, the new reference change, and a few other pieces of information. If desired, sorting is just a click away with sort buttons to rearrange the order of the searched information for easier management. Another feature is the ability to quit a search at any time in case the database is huge and you don't have a few minutes.
Support
Also, the instructions are concise and flow well so you owe it to yourself to read through them before using this cool tool. If you are using Access 7.0, make sure you use the corresponding version of SF. A listing of what's new in the Access 7.0 version of SF is provided in the front portion of the instructions. Additionally, technical support is available by phone, fax, or Internet. The author brings up a very important point: create a backup from a known stable starting point before beginning your changes. It is just good programming practice and a way to get back to the way things were in case your changes produce radical side effects. For answers not listed in SPEED FerretHelp, the Session Journal feature of SF provides the events that have occurred in the most recent session to help you with your technical assistance call.
Room for Improvement
If SPEED Ferret updates any Access Basic code you wrote in Access 2.0, it shuts down SPEED Ferret. This isn't a problem for novice Access users since they probably aren't writing any Access Basic code, but if you are using an add-in in the first place like SPEED Ferret, then you are probably at the intermediate or better level. It isn't much fun having to reopen a program over and over and over. Of course, I am like most programmers who stand in front of a microwave impatiently saying, "C'mon, I don't have all minute." Fortunately, it does show you what objects it is inspecting and provides a status meter bar for some eye candy instead of just an hour glass. Even as slow as the Access 2.0 version is, it is tremendously faster than trying to do it yourself by hand. I think a better strategy would be to convert your 2.0 app to Access 7.0 and then use SF to do your modifications. SF for Access 7.0 is so much faster because it just searches your objects once instead of re-reviewing each time a global search and replace is launched. Hallelujah!!
Another area of improvement I would like to see in both versions is the ability to pick nonsequential lines to delete in the search results update view. What I am referring to is similar to what is available in File Manager or Win95 Explorer by selecting a line, then picking another one while holding down the CNTL key. In fairness to SF, I think this is a limitation of Microsoft Access more than the code driving this utility. If there is a way Moshannon Systems can provide this feature, it would clearly be a value-added addition.
Thirdly, a scroll bar on the popup zoom box (top half) for "value" is desperately needed. When the results come back from your search you can look at the code for what you have in the top half of a form and in the bottom half is what will replace it. There is a scroll bar on the bottom half for long code, but no scroll bar for the top one! It escapes me why this never got into either version. There is a work around by "zooming" on it (shift F2) when in that column. Also, the ability to set "default settings" for the search window is currently not available. It gets tiring re-selecting my unique criteria combinations every time I use this product. Along the same lines, I'd like the print preview of the "Replace with" log report as the default title to GlobalSearch and Replace.
Further, the search engine form is on autocenter so I can't move it around and there is no title bar handle to grab. This feature would allow me to see the underlying object so I can save time if I need to refer to something there. As a result, I have to close SPEED Ferret to get back in to change the next one. One work-around is to print a list of all the object names I want to change before opening this add-in, but I thought this was supposed to be the paperless society era.
To Sum Up
Finally, if you are an Access developer, SPEED Ferret will make your life easier by providing a combination of ways to globally search your entire database and replace it with whatever you want. The newer version for Access 7.0 is a great deal more robust than the version for Access 2.0, which is still faster than trying to do it yourself by hand. Black Moshannon Systems does have a Productivity Pack that includes both versions of SPEED Ferret at a reduced rate for programmers developing in both versions of Microsoft Access. Time is of the essence to a programmer and the need for quality programming tools is a never-ending quest. Remember, the best developers know "speed is life and attitude is everything."
Robby Thompson is a HAL-PC member and software developer for Microsoft Access, Excel and Visual Basic. / Copyright 1996 Robert D. Thompson. All Rights Reserved.
E-mail me at webmaster@hal-pc.org with any comments you have and tell me what you want to see here.
Back to the User Journal Home Page