Cypris’ lookout

MS Access: Restarting the database programmatically

Tuesday, May 6, 2008

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.

Being able to cleanly restart 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 programmes.

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.
This will work for normal databases (accdb) and also compiled (accde) and runtime (accdr) databases as well.

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

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:
:CHECKLOCKFILE PING 0.0.0.255 -n 1 -w 100 > NUL IF EXIST "%~f1.l%2" GOTO CHECKLOCKFILE START "" "%~f1.%2" DEL %0 When the application runs the batch file, it passes 2 arguments:

• the full path to the database without the extension
• the database file extension without the leading “.”

This allows us to easily construct the path to the lock file at line 30 (we add the missing “L” in the extension).
The funny use of PING is actually a simple way to wait for 100ms or so before we check if the lock file is still there or not. There is not 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 inexistent, but valid, IP address.
Once the lock file has disappeared, we open the database at line 40 and then delete the batch file itself so we leave no leftovers.

Download the ColorScheme.zip (31KB) containing the ACCDB database (same as the Color Scheme sample).

Entry Filed under  :  Database, MSAccess, Programming