Product Review: Access Workbench
February 17, 2005
For some years, I have been asked to review a product that I have only just gotten around to looking at. It was developed by Garry Robinson, who publishes a very useful web resource called vb123.com and who recently wrote one of the best books on Access security, Real World Microsoft Access Database Protection and Security. (Check out my book review here at DATABASE JOURNAL.)
The product is called The Access Workbench (TAW) and what follows is part product review and part basic training on the tasks that this utility simplifies for you. In a nutshell, here's what TAW can do:
Now, if you are an old hand at Access development, you will know the menu options, command line switches and VBA code to perform most of what is described above. However, if you work with many various databases, as most Access developers do, you will find these tasks to be repetitive and tedious. No doubt, Garry Robinson developed this utility precisely because he got tired of performing these tasks manually.
Before we begin with the details, let me say that this product will set you back $89.95 which, while not trivial, is not that bad for a decent utility. As usual, the touchstone I use when deciding whether or not to invest in a new developer toy is the time it will potentially save me, since as a consultant, time is money. Depending on the rate you charge, the $90 represents 1-2 hours of billable time. After using TAW for a while, I decided that the Favorites Dashboard alone would save me an hour a month in file navigation time. The decompile and workgroup file features are also indispensable, but I will talk about that in greater detail later on.
Favorites, Current and Users
As you can see from the application screen shot, the primary tab shows the list of "favorite" databases you have added to the workbench. It is simple to add new ones, edit existing entries and remove them from the list. The Add dialog, shown below, allows you to select, among other things, the file, its workgroup and the version of access with which it should open.
Though the favorites are what justify spending the $89.95, it's the special functions that are the sexy part of this utility. Features like Compile, Compact, Decompile, Backup and Lock are managed in the Who's On section of the main menu. The help file associated with TAW describes these functions as follows:
The Compile checkbox will open the database, find out if it has been compiled and if it has not, it will compile it for you. If a compiler error is encountered, the database stays open so that you can sort it out.
The Compact checkbox will tell the workbench to compact the current database the next time that the Who's On list is empty.
The Decompile checkbox will remove all compilation code from your database. This is useful to run every now and again as it will remove unwanted compilation material and reduce the size of your database.
The Backup checkbox will tell the workbench to backup the current database to the backup folder the next time that the Who's On list is empty
Lock will stop any new users logging into an Access 2000/2002 database. This is useful when you are trying to get everyone off a database for maintenance or installations. This changes to Unlock when the lock has been set. This will be disabled if this program is stopped.
Some of these functions can be handled through menu options in Access itself, like Compile, Compact and Backup (Access 2003 only), but you must have the database open exclusively to do so, which may or may not be possible. Garry's tool allows you to tag a database for these maintenance procedures and it is performed once all users are logged off.
Decompile, however, is an undocumented function and not available from the Access Tools menu. To perform a decompile, you need to create a shortcut specially designed to execute the command line switch. Alternatively, you could enter the correct script into the Run dialog to decompile a database on demand, provided you can open it exclusively. It looks a lot like the Target text quoted above for opening an mdb with the correct workgroup:
"C:\Program Files\Microsoft Office XP\Office10\MSACCESS.EXE" "C:\Development\Pilcher\Reunions\EmailMonitor\emailData.mdb" /decompile
Again, this is a function that could be handled without TAW but it is much easier to click the Decompile checkbox and let the workbench do it for you.
The Lock function is another animal all together. While I could probably come up with a plan for implementing the process, I have no idea how Garry makes this happen. There is no menu option or shortcut text that will accomplish this feat. A while back, I saw some code for performing a similar task and there was nothing simple about it. In my reviewing of the product, I was not able to test this feature but it is clear that for some users, this alone makes the product worth the price.
Who is this Utility For?
It is no accident that I have repeatedly used the term "developer" as I reviewed this utility. It is designed for Access developers who have to manage multiple databases across multiple versions and potentially using multiple workgroup files. If you are a casual user or manage only a few databases, you will not see a great return on your investment. If, however, you are a power developer, you should really meander over to Garry's Site to check it out.