Product Review: Access Workbench

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

Danny Lesandrini
Danny Lesandrini
Danny J. Lesandrini currently works as the IT Director for Pharmatech Oncology Inc. at http://www.pharmatechoncology.com/. He holds Microsoft Certifications in Access, Visual Basic and SQL Server and has been programming with Microsoft development tools since 1995.

Latest Articles