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 Code2. Compact
the MDB file3. Decompile
VBA Code4. Backup
the MDB file5. 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.
