Posts filed under 'Web Design'

MS Access: Restarting and compacting the database programmatically

Microsoft Access In my previous article about changing the MS Access colour scheme I had the need to allow the user to restart the database after the colour scheme was changed.
(Article and Code Updated 13FEB2009.)

Being able to cleanly restart and compact the application is also useful in other instances:

  • Changes made to the environment
  • Recovering from errors (for instance after a network disconnection)
  • Forcing the user to re-log cleanly into the application
  • Automatically restarting a long-running application (for instance so that it may automatically compact on close and restart afresh with or without user intervention).

The problem is that you cannot -to the best of my knowledge- close and open again the same database from within MS Access itself.
Most executables cannot do that and the way to solve the issue is usually to pass the control to another boostrap programme, close the main application and let the bootstrap programme re-open the main application again.
I wanted a simple and clean way of using it. One that would not require shipping external dependencies.

How to use it

Download the sample database below, copy the Utilities module or just the Restart sub defined in it into your own application.

To use it, just call the Restart sub and the application will close and re-open.
If you supply the optional Compact:=true parameter, the database will also be compacted during the restart process.
This will work for normal databases (mdb/accdb) and also compiled (mde/accde) and runtime (accdr) databases as well.

Important note

If you want to use this code do not enable the Compact on Close option in Access for your database as the code doesn’t pick that up yet.
Instead, you can either simply call restart Compact:=true on user action (for instance from a menu) or on other triggers, for instance when the database is being open and hasn’t been compacted for more than a week.

How it works

If you’re curious about the technical details, here is how it was put together.
The main idea is that the MS Access database application has to be self-sufficient and restart itself by performing these steps:

  • create a small batch file
  • run the batch file, passing the path and extension of our database
  • close the main application
  • the running batch file would wait for the MS Access lock file to be removed
  • once the lock file disappears, we open the database after compacting it if required.

The key point here is that the batch file cannot just reopen the database right away: if the application is big or if it’s compacting on close for instance, it may take several seconds to actually close.
The only moment we can be pretty sure that the database is effectively closed is when the lock file is deleted by MS Access.

The batch file is hard-wired in the Restart sub that does all the work:

SETLOCAL ENABLEDELAYEDEXPANSION
SET /a counter=0
:CHECKLOCKFILE
ping 0.0.0.255 -n 1 -w 100 > nul
SET /a counter+=1
IF "!counter!"=="60" GOTO CLEANUP
IF EXIST "%~f2.%4" GOTO CHECKLOCKFILE
"%~f1" "%~f2.%3" /compact
start " " "%~f2.%3"
:CLEANUP
del %0

When the application runs the batch file, it passes 4 arguments:

  • the full path to the MSAccess.exe executable (used for compacting the database)
  • the full path to the database without the extension
  • the database file extension without the leading “.”
  • the appropriate database lock file extension (laccdb or ldb).

This allows us to easily construct the path to either the database or the lock file at line 07 and 09.
Line 08 is actually only inserted if we need to compact the database: it simply launches MSAccess.exe with the /compact command line switch.

The funny use of PING is actually a simple way to wait for some time before we check if the lock file is still there or not. There is no SLEEP or WAIT function provided by default in Windows so we have to be a bit creative and use the time-out option of the PING command trying to ping an nonexistent, but valid, IP address.
Once the lock file has disappeared, we open the database at line 09 and then delete the batch file itself so we leave no leftovers.

The other thing of note is that we now use a counter to keep track of the number of times we checked the existence of the lock file.
Once this counter reaches a pre-determined amount (60 by default, ~ 45 seconds) we consider that there is a problem and the database application didn’t close, so we just exit and delete the batch file.

DownloadDownload the DatabaseRestart.zip (48KB) containing both an Access 2007 ACCDB and Access 2000 MDB test databases.

Other implementations

Code Updates

v1.2: 13FEB2009

  • Added optional parameter to compact the database during restart.

v1.1: 09AUG2008

  • Now a separate test database (used to be bundled with the Colour Scheme sample).
  • Added support for older Access versions (an Access2000 MDB is now included).
  • Corrected wrong lock file extension for accd* files.
  • Added a time-out feature after which the batch file will delete itself after a while if the Access lock file wasn’t released (for instance following a crash).
  • Added checks to delete the batch file if it has not deleted itself for some reason (for instance after a reboot).
  • The batch file now has a unique name based on the name of the database, allowing multiple databases to be restarted from the same directory.
  • Added license notice at top of source code.
  • Updated the article to reflect the changes.

v1.0: 06MAY2008

  • Original version

Creative Commons License
This work is licensed under a Creative Commons Attribution 3.0 Unported License.

37 comments May 6th, 2008

Previous Posts


Most Recent Posts

Categories

Links

Posts by Month