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:

• Provides a "favorites" dashboard for launching Access databases
• Guarantees you to open a database with the correct Access version
• Simplifies opening a database with the correct workgroup file
• Allows you to view all currently logged on users
• Standardizes and simplifies file backups
• Makes the following tasks a single-click process:

  1. Compile VBA Code
  2. Compact the MDB file 
  3. Decompile VBA Code
  4. Backup the MDB file
  5. Lock database to new users

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.

Click for larger image

(Screen shot of TAW from Garry's web site.  Check out screen shot of next version beta.)

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.

Here is where the first, and most valuable aspects of the utility reveal themselves. 

• Central location for launching databases
  As mentioned, I develop many different databases for various clients.  I have a development folder with a subfolder for each client with subfolders for each project and sometimes more subfolders for project parts.  Navigating to these databases requires opening Windows Explorer and clicking deeply through the tree.  Provided I don't make a mistake and forget where I put the database I'm looking for, it takes between 6 and 10 seconds for me to complete this process for a given database and I repeat this process dozens of times each day.
   
  I tried putting shortcuts on the desktop, but I hate a cluttered desktop, so they never remain long.  The Access Workbench provides a brilliant dashboard from which I can manage links to all the databases I use on a regular basis and it requires only a single icon on the desktop!
   

• Open databases with correct Access version
  After navigating to a database you still need to open it with the correct version of Access.  My development laptop has three versions of Access installed:  Access 97, XP and 2003.  You may know that the Open With right-click menu option provides a way to specify which version of Access launches the mdb file, but what you probably did not know, or have not noticed, is that by default the Open With menu reveals only two version options:  XP and 2003, despite the fact that I have three versions installed. 
   
  I know, there is probably a way to add Access 97 to the list.  I thought it would be simple, so I opened Windows Explorer and went to
      Tools | Folder Options | File Types >> Select MDB  >> Select Change (wrong) ... >> Select Advanced (wrong)
   
  Ok, so I guess I don't know how to add Access 97.  Glad I have The Access Workbench.  There are other tools out there that help you manage MDB files and open them with the correct version, and I even own one of them, but I never would have bought it if I had already been using TAW.
   

• Automatically associate workgroup file
  This is where I confess that I do not use Access security features the way I should.  It is clumsy to have to associate a workgroup file when opening an MDB file.  Unless there is a trick I do not know about, you have to create a shortcut (back to messy shortcuts again) and set the Target property with the correct command line parameter.  Below is an example of one such shortcut. 
   
  "C:\Program Files\Microsoft Office XP\Office10\MSACCESS.EXE" "C:\Development\Pilcher\Reunions\EmailMonitor\emailData.mdb" /wrkgrp "C:\Development\Pilcher\Reunions\ru.mdw"
   
  Yea, it's a mouthful.  Not difficult, in the sense of requiring great amounts of brain power, but lots of typing, and you had better type it correctly or you will hear about it.  In addition, you notice that the question of Access version is handled in this shortcut too.  Therefore, it appears that one function of The Access Workbench could be handled with an array of shortcuts but as I said above, I hate managing shortcuts.  It takes time, it is prone to type-Os and it is ugly to maintain.

Special Functions

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.

» See All Articles by Columnist Danny J. Lesandrini