The Access Workbench

Here’s a review of the Access Workbench, a tool that helps manage Access databases.

Access Archon #149

By guest author Danny J. Lesandrini

Database Management Made Simple

Product reviews can get pretty boring, especially when you’re the one writing them, but I’ve found a way to make this one more interesting and informative.  It crossed my mind that I might not need this tool, since the tasks I most frequently perform may be done in several different ways.  I’ll show how you could code the most valuable feature of this utility for yourself, and in the end, you decide if you’d rather do it yourself or shell out the $119.95 cost of admission.

How hard can it be to manage databases?  Well, if you’ve got just a few, and you haven’t implemented security with various workgroup files, then it’s probably pretty simple.  There are three aspects to managing databases:
 

1. Finding and opening them


2. Implementing maintenance (creating backups, compiling and compacting).


3. Working with (not in spite of) active users on the system

As we consider these issues, keep in mind that the product is directed at Access developers, database administrators and maybe power users.  For someone who has two or three files to manage, this utility is major overkill.  Some of my alternate options, however, might be just the right solution for your needs.

Roll Your Own Database Management System

Everyone knows that opening a database is simple; it’s finding the darn thing that taxes my brain.  So, I usually take the poor man’s way out and create shortcuts on the desktop.  I hate shortcuts on my desktop.  They clutter my space; they can be complicated to create when adding special instructions and they frequently become obsolete.

For example, let’s say I create a shortcut to a database file named MDA Absence Manager.mdb and it needs to be opened with Access XP.  Let’s also say this file requires a special workgroup security file and I’d also like to pass in the user name and password so I’m not bothered with supplying it at runtime.  Oh, and by the way, I’d like another shortcut to compact the database on demand. 

No problem!  This can be done using shortcuts; here’s the text that would go in the Target box of the shortcut properties page:

Figure A.  Path images

As you can see, this isn’t unreasonably complex, but it isn’t simple either.  Now, multiply those shortcuts by 15 and you’re approaching the number of links I need to manage my

Decompile the databases, which is something I do from time to time.  So, make that 45 shortcuts on the desktop.

There is an alternative to desktop shortcuts.  Right-click on the Windows Taskbar and choose New Toolbar from the Toolbars menu.  It will ask you to point to a folder that will become a new menu on the Taskbar.  I pointed to a folder named Unicare_build_21 which I knew had several database files in it.  Once created, it shows up on the right side of the Taskbar near the system tray, but you can grab it with a mouse click and drag it to the desktop.  When you do, you get something like the image you see below – a floating toolbar.

Figure B.  A floating toolbar of Access databases

This would work for my sets of development files.  I could create my 45 shortcuts, save them in 15 folders and place 15 floating menus on my desktop.  Actually, this isn’t a bad solution if you have only a few projects to manage, but these little menus can also clutter the desktop and the first time you accidentally close one, the steps to recreate it on your desktop seem burdensome.  (I counted eight mouse clicks to create the floating menu you see here.)

That solution takes care of finding and opening your array of databases, and it even shows how you might force a Compact and Decompile, but how would you perform an automatic Compile or create a Backup?

There’s a script for automating file backups at my site that I created some years ago, saved as a VBScript (.vbs) file.  You can find it by browsing the downloads page,

http://amazecreations.com/datafast/Download.aspx,

or click this link to download this script directly:

http://amazecreations.com/datafast/GetFile.aspx?file=WSHBkup001.zip

To use the Backup scripts, you need to know something about VBScript (please don’t write me for support).  Code at my site is free, but comes with no warranty or support.  Also, I don’t have any idea how to script the Compile command, though I suppose it could be figured out, especially with the help of those friendly developers at the various Access newsgroups.  So, we’re getting close, but our menagerie of shortcuts and script files isn’t quite there yet.

Alternatively, you can add a Backup button to your database’s main menu, powered by Helen Feddema’s Backup code (the latest version of Backup.mdb is included in the accarch143.zip file, which can be downloaded from Helen’s Web site).  This is a useful addition to a database, but it is not automatic, and you can only use it within the database.

Finally, there is the issue of managing the users.  Who’s in the database?  How do you send them an administrative message?  Can we lock them out so that maintenance may be performed on the data file?  Not only do I not have a script at my site for any of this, I couldn’t begin to tell you where to start.  Fortunately, Garry Robinson has already figured out how to do handle database users, and his utility, The Access Workbench makes management a breeze.  You’ll find this, and other cool utilities, articles and VB related resources at Garry’s site, http://www.vb123.com/.

The Access Workbench (TAW) Utility

Allow me to introduce you to TAW with the following screen shot.  This is my favorite part of the utility, the Favorites screen.  I won’t give a click-by-click explanation of how to register files, but suffice it to say that it’s so intuitive that you really don’t need to read the Help file to begin using this tool.

It may not be obvious from the image, but these nine registered files include MDB, MDE and ADP files, and they are opened with various versions of Access, including Access 2000, Access XP and Access 2003, all easily managed from a single, clean interface.

While the user interface exposes no explicit provision for Office 2007 support, ACCDB and ACCDE files will work, provided you plug the MSAccess.exe for Office 12 into one of the Office 2000 slots in the Workbench Options page.  By the end of the year, the Access 97 option will be phased out and Office 2007 will be supported.  For users that purchase the current utility, vb123 Software typically offers a substantially reduced upgrade price for new versions.

Figure C.  The TAW Favorites page

This is an incredibly convenient way to launch databases.  The TAW application sits in my Quick Launch Tray, so it’s easy to load.  Once loaded, all my files (as many as I want) are managed through this clean interface:  no more cluttered desktop.  Recently, Garry added a hyperlink to the top of this page that allows users to open the folder in which the file is located.  A simple adjustment, but one that I really appreciate.  Think about it:  one-click access to all the files related to your project.  It’s like adding an extra shortcut to your Toolbar for the parent folder of the files!

The real work is done by the left menu options.  From this menu you can create a backup, compact, decompile, compile and lock your database, by simply checking a checkbox.  The Lock function is especially valuable where you wish to prevent additional users from logging onto your database.  While this feature doesn’t kick out currently attached users, it does stop the bleeding, so to speak, and simplify the process of getting an exclusive lock on the database.

The Current tab (see screen shot below) is mostly for information purposes, though there is a nice RSS News link that takes you to the Access Unlimited Newsfeed, with lots of great resources and up-to-date information about the latest version of Microsoft Access.  I find myself checking this tab to verify file size and other attributes that help me keep my files under control.

Figure E.  The TAW Current page

Final Thoughts

There are some features of this utility that I am not qualified to discuss, since I don’t generally manage files that are currently in use by several users.  If I did, I would undoubtedly find the Users tab and the Administer dialog of great value. That’s something you’ll have to play with on your own, and you can do that for free.  You may download a copy of The Access Workbench and play with it 50 times before being required to register.  The download link is http://www.vb123.com/workbench/#Download

I know that much of what TAW does can be handled with shortcuts, but saving time and trouble is definitely worthwhile.  The utility is reasonably priced at around $120, which is somewhere between 1 and 4 hours of consulting time.  (If you’re making less than $30/hour as an Access developer in the USA then you should raise your rates.)  Save yourself the frustration and start managing your databases with The Access Workbench.

Supporting Files

The zip file containing this article, in Word format, plus the supporting file, may be downloaded from the Access Archon page of Helen’s Web site.  It is accarch149.zip, which is the last entry in the table of Access Archon columns for Access Watch.

For over 4 years, Danny Lesandrini has been reviewing and reporting on Microsoft Access related books and utilities. Danny was first introduced to Garry Robinson in 2004 when he was asked to review Garry’s new book, Real World Access DB Protection and Security.  Since then he’s become a regular visitor to the vb123 website and a loyal user of The Access Workbench.