MS Access: Restarting and compacting the database programmatically

Tuesday, May 6, 2008

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.

Entry Filed under  :  Database,MSAccess,Programming,sysadmin,Web Design

38 Comments Add your own

  • 1. Tony D'Ambra  |  August 6th, 2008 at 9:13 am

    Compliments on your very elegant blog and code.

    A couple of months back I tackled the issue of forcing a restart by calling a VB6 EXE on an Access UNLOAD event and passing the database file path as a command-line parameter. Now I can see your self-contained BAT file approach is better. I used WAIT to delay the restart, and then trapped the Unable to Unlock File error in a resume loop until Access unlocked the MDB file and deleted the LDB file. I was wondering why in your code, you try deleting the LDB file outside Access? Is there not a risk of corruption as the previous instance of Access would still exist until after its deletion of the LDB file?

  • 2. Renaud  |  August 6th, 2008 at 10:38 am

    Hi Tony,
    thanks for your message.

    Regarding the lock file, I assume that its deletion by Access is the last action performed once a database is closed, which would make sense since it’s the whole purpose of a lock file.
    I do not attempt to delete the lock file, Access does that; the last sentence of the batch file, the DEL %0 actually deletes the batch file itself to keep things clean.

    Thank you again for dropping by.

  • 3. Jacques  |  August 8th, 2008 at 8:53 pm

    Nice, simple and clean.

    A few comments:

    1) I realise you work with Access 2007 (with new extensions) – all previous versions use .mdb for the DB itself and .ldb for the lockfile – by adding the ‘l’ in line 3, it doesn’t work for older versions (it should replace the ‘m’) – any idea how to make it generic?

    1a) Say you run a DB in execute-modus (extension = .accde) – then looking for the ‘l’+’accde’-file is not going to work, as the lockfile will still be ‘.laccdb’ – right?

    2) When Access crashes, it doesn’t delete the lockfile – in case you run this batchfile and the .ldb not being removed, you end up in an infinite loop – not a resource-hog, I assume, but on creating this batchfile again (on restarting Access), you might run into trouble, as the previous batchfile is still present and you can’t create a new batchfile with the same name. Perhaps you should build in some timer that self-deletes the batch file (after 2 minutes). And give batchfiles an unique name (e.g. incl. a timestamp).

  • 4. Renaud  |  August 9th, 2008 at 1:29 pm

    @Jacques: you make some valid points and I have updated the article, the code and test database to reflect these improvements and corrections.
    Thanks for the comments.

    The test database for restarting the application is now available in Access2007 and Access2000 versions.
    If someone could test the MDB version and let me know if it works properly or not I’d be grateful.

  • 5. Lu Anne  |  August 13th, 2008 at 12:14 am

    I am trying your restart function in Access 2003 – if I don’t set the database to compact on close, the function works fiune. When I set the database to compact on close it never reopens the data abse. any suggestions?

    Thanks, Lu

  • 6. Renaud  |  August 13th, 2008 at 9:39 am

    @Lu: it’s a good point. When access compacts on close it will take over its own restarting process and remove the database lock file.
    In that case the batch file will assume that the database is closed and try to re-open it, which Access will not do since it is currently compacting.

    I’ll see what can be done. My guess would be to try to detect whether the database is set to compact on close and find a way to detect when access has completed the compacting process.

  • 7. Lu Anne  |  August 13th, 2008 at 10:25 pm

      Dim compactonclose As Variant
        compactonclose = Application.GetOption("Auto Compact")
        If compactonclose Then
            MsgBox "I am set to compact on close"
      End If
    

    The above routine will detect if compact on close is selected. As for detecting when the database has actually closed, still working on that. I’ll keep you posted.

  • 8. Jacques  |  September 5th, 2008 at 5:03 pm

    @ Lu

    It’s is not recommended to use ‘Auto Compact’ in shared environments\over networks etc. – see this thread (which basically confirms my own findings): http://www.utteraccess.com/forums/showflat.php?Cat=&Number=603048&Main=601621

    Compacting is a delicate process, which should be executed with ‘care’ – only in a stable environment (local), making a backup first etc. (ok, I don’t always do that myself either, but we all should…).

    So, compacting from code (http://www.mvps.org/access/general/gen0041.htm), you know when it is finished as it returns the result (http://msdn.microsoft.com/en-us/library/ms696119.aspx) – after that you could use Renaud’s batch file to restart.

    This thread contains some code to compact when your MDB reaches a certain size: http://www.tek-tips.com/viewthread.cfm?qid=642227

    And here some sleeker code tha gives the user 3 options: http://forums.devarticles.com/microsoft-access-development-49/compact-database-via-vba-24958.html

    I haven’t tested all the code, so don’t shoot the messenger…

  • 9. bil643  |  October 13th, 2008 at 11:07 pm

    We don’t save data after inserting it in MS Access table. However when we open the table we observe that data is not vanished? Why?

    Dear can u help me to solve this question? I hope to listen from you soon.

    Thanks

  • 10. Renaud  |  February 13th, 2009 at 12:22 pm

    About Compacting: now the code offers you the option to compact the database during restart.

  • 11. Andrew  |  April 17th, 2009 at 10:16 pm

    Hi I am running your code in Access 2003 but in Access 2000 file format

    I get the following error:

    “Could not use ‘Z:\Project Metrics\Project Metrics -test.mdb'; file already in use”

    I assume this is because it tries to open the database too quickly is this the case and if so can be further slowed down?

  • 12. Renaud  |  April 21st, 2009 at 10:08 am

    @Andrew,
    could you send me your test database (see email at the bottom of the page).
    Also, the code should only be used for a local database that is currently opened only once, not a shared one that could be opened by multiple users.

    Thinks you can try:

    • make sure you have the PING command. It should be there, but open a command prompt and check that it’s installed.
    • Try to change the wait duration for the PING command in the VBA code for Restart():
      Change the line that contains:
      ping 0.0.0.255 -n 1 __-w 100__ > nul to
      ping 0.0.0.255 -n 1 __-w 1500__ > nul
  • 13. Martin  |  September 11th, 2009 at 10:07 pm

    Here’s a solution to make the excellent Restarter handle when Access is set to compact on exit. Works fine in Access XP (2002). No responsobilities taken.

    Best regards Martin 'Usage in conjunction with the Restarter: 'Restarter.Restart Compact:=bHandleCompactOnExit() 'And for the AutoExec macro or startup form: 'RestoreAutoCompactSetting 'You also need a table with the fields Parameter and Value _ and a record where Parameter='CompactOnExit'. Public Function bHandleCompactOnExit() As Boolean ''Compact on exit' might need to be shut off, if so, save current setting _ and shut off. Use code at application startup (i.e. the AutoExec macro _ or starter form) to restore 'Compact on exit'. Dim iAutoCompact As Integer 'get setting iAutoCompact = AutoCompactSetting 'Store the value in a way that it is available at next startup. DoCmd.RunSQL "UPDATE LocalSettings SET LocalSettings.[Value] = '" _ & iAutoCompact & "' WHERE (((LocalSettings.Parameter)='CompactOnExit'));" If iAutoCompact = True Then _ AutoCompactSetting = False bHandleCompactOnExit = iAutoCompact End Function Public Sub RestoreAutoCompactSetting() 'Restore previous setting for 'Compact on exit'. Call this sub from _ AutoExec macro or your startup form. 'Get previously saved setting and set it for your application AutoCompactSetting = DLookup("Value", "LocalSettings", _ "Parameter = 'CompactOnExit'") 'Save a value for next startup that does not _ affect 'compact on exit' DoCmd.RunSQL "UPDATE LocalSettings SET LocalSettings.[Value] = '1' " _ & "WHERE (((LocalSettings.Parameter)='CompactOnExit'));" End Sub Public Property Get AutoCompactSetting() As Integer 'Get current setting for 'Compact on exit' AutoCompactSetting = Application.GetOption("Auto Compact") End Property Public Property Let AutoCompactSetting(iOnOffVoid As Integer) 'On/Off for 'CompactOnExit' _ iOnOffVoid decides: True (-1) 'CompactOnExit' on _ False (0) 'CompactOnExit' off _ else (e.g. >0) no change If (iOnOffVoid = True) Or (iOnOffVoid = False) Then _ Application.SetOption "Auto Compact", iOnOffVoid End Property

  • 14. Martin  |  September 11th, 2009 at 10:30 pm

    or alternatively, you might not want to compact at this moment: bHandleCompactOnExit Restarter.Restart Compact:=False

  • 15. Naphta  |  October 7th, 2009 at 5:52 am

    Hello,

    It seems that it works as for self updating access frontend !

    the first tests are good !

    It’s great !!

    Thanks

  • 16. naphta  |  October 7th, 2009 at 10:33 pm

    Hello,

    All tests are fines !

    I made test with an accdr connected by the network on a huge backend.

    No problemo I give the code to a french forum.

    Bye

  • 17. erwin  |  August 7th, 2010 at 6:26 am

    can you please contact me – I could use your help, importing into access via ODBC, and it shuld auto refresh on a daily basis

    Erwin

  • 18. Kevin  |  August 17th, 2010 at 3:13 am

    Hello! I’ve been looking for code to allow me to restart/compact a database for a long time. In summary, I am pulling Automatic Vehicle Location data from a 911 system in order to create KML files of unit and incident locations, to be displayed in GoogleEarth. Every so often, the database crashes, and I have to manually restart it.

    I slipped your code into my process, and it creates the batch file and relaunches the .mdb perfectly once, but the second time through, it doesn’t create the batch file. It just quits Access.

    Any thoughts on why it’d do that?

  • 19. HUgh  |  October 24th, 2010 at 4:54 am

    Hi All, I’ve added this module to my db with the intention of being able to replace the front end with a new version.

    What I’ve added to the batch file is: s = s & "IF EXIST ""%~f2.old"" DEL ""%~f2.old""" & vbCrLf then s = s & "REN ""%~f2.%3"" ""%~f2.old""" & vbCrLf and s = s & "REN ""%~f2.upd"" ""%~f2.%3""" & vbCrLf directly after: "IF EXIST ""%~f2.%4"" GOTO CHECKLOCKFILE" & vbCrLf

    However this doesn't trigger at all. Any suggestions how I can make it work would be appreciated

  • 20. Button to restart a user-&hellip  |  July 28th, 2011 at 3:19 pm

    […] restart, relogin). I have found this code, but this doesn't apply to user-level protection : http://blog.nkadesign.com/2008/ms-ac…grammatically/ Any hint ? […]

  • 21. Daniele  |  August 3rd, 2011 at 3:49 am

    Me too ! I will appreciate a version of your function/module for databases with user-level protection. Restart would be enough without passing username/password since main aim would be to change active user or opening db with Shift key pressed… Please help!

  • 22. Any way to quickly switch&hellip  |  August 3rd, 2011 at 3:54 am

    […] would be good enough, but the only one I have found doesn't support user-level protection : http://blog.nkadesign.com/2008/ms-ac…grammatically/ Any other solution/shortcut ? […]

  • 23. schmoe  |  March 14th, 2012 at 3:51 am

    What happens in (the very common) instance of the lock file not “disappearing” as it should?

  • 24. mor10  |  September 27th, 2012 at 12:52 am

    With newer faster computers I have found that upping the -n to a higher number is necessary to allow enough time for the lock file to go away.

  • 25. Jason  |  January 7th, 2013 at 11:09 pm

    The situation I have with using this paritcular method is that the database that I need to have restarted is accessed from a network drive and multiple users could be in it at the same time. Therefore the lock file may still be existent even after an instance of the database has been closed.

    What I run into is that I run a sub that handles the movement of data from an external device to a temporary table. Unfortunately, this table has to be deleted at the end of the process. Deletion of this table causes the navigation pane to appear which is undesirable. I must then manually close the navigation pane and then restart the database so that the navigation pane is completely hidden.

    Any way to not have to wait for the lock file to be deleted by Access? Would it just be the removal of the first If..Then at the top of the Restart sub?

  • 26. mh  |  April 10th, 2013 at 8:36 pm

    I am getting error after applying same batch code which was posted by Renaud.

    Error : Could not use c:\\…\test.accde file as file already in use.Let me know if any changes needs to be done in the given batch file.

    Dim s As String s = s & "SETLOCAL ENABLEDELAYEDEXPANSION" & vbCrLf s = s & "SET /a counter=0" & vbCrLf s = s & ":CHECKLOCKFILE" & vbCrLf s = s & "ping 0.0.0.9000 -n 1 -w 100 > nul" & vbCrLf s = s & "SET /a counter+=1" & vbCrLf s = s & "IF ""!counter!""==""" & TIMEOUT & """ GOTO CLEANUP" & vbCrLf s = s & "IF EXIST ""%~f2.%4"" GOTO CHECKLOCKFILE" & vbCrLf If Compact Then s = s & """%~f1"" ""%~f2.%3"" /compact" & vbCrLf End If s = s & "start "" "" ""%~f2.%3""" & vbCrLf s = s & ":CLEANUP" & vbCrLf s = s & "del %0" ' Write batch file Dim intFile As Integer intFile = FreeFile() Open scriptpath For Output As #intFile Print #intFile, s Close #intFile

  • 27. Ed  |  April 16th, 2013 at 12:51 am

    Thanks for the utility… The problem is that newer computers are so fast that when Access closes, the shell process spawned immediately got terminated. I have to use the compact option to delay just enough that restart works.

  • 28. Mitko  |  April 19th, 2013 at 10:25 pm

    Is it possible to use it with Database which has passwords and protections. I have tried but after the restart I could not log in. I received a message sayng that I have no permission. I would appreciate any comments.

  • 29. James Barker  |  April 25th, 2013 at 11:16 pm

    I also found that with faster computers I have found that upping the -n to a higher number is necessary to allow enough time for the lock file to go away.

  • 30. besi  |  July 25th, 2013 at 6:25 pm

    thank you. which one is the updated code

  • 31. Randy T  |  February 23rd, 2014 at 3:02 am

    That code rocks! That is exacly what I was trying to figure out how to do.

  • 32. Randy T  |  February 26th, 2014 at 3:59 am

    Exactly what I have been looking for! Nicely written and well documented… Thanks!

  • 33. Allan B  |  May 26th, 2014 at 1:43 pm

    Thanks so much for sharing your expertise. With a little tweaking I was able to add a line to the batchfile so that it ran my updatefrontend.bat before restarting, creating a built-in automatic update and restart function. Just the sort of thing I have always wanted!!

  • 34. Tim D  |  May 29th, 2014 at 11:30 pm

    Hello Renaud,

    I just downloaded and tried your restart module on a couple of my Access 2010 DBs. It successfully shut down and restarted both DBs, but even using restart Compact:=true, it did not compact either of them.

    Any ideas?

    Tim

  • 35. Tim D  |  May 29th, 2014 at 11:31 pm

    FYI – I ran them off a form command button with no other code behind it for testing (just in case that helps)

  • 36. Allan B  |  June 6th, 2014 at 9:01 am

    I have inserted a line in the batch file to run a batch file that will copy the latest version of the FE to the users machine before restarting.

    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 copy C:\users\Allan\Database\UpdateDatabase.bat ************ start ” ” “%~f2.%3″ :CLEANUP del %0

    When the batchfile runs the db closes, I can see that the batch file is created but then a error message pops up headed C:\users\Allan\Database\database.accdb saying: “There was a problem sending the command to the program” but everything seems to work. The db goes on to open and the batch file deletes itself. Any idea on how to get rid of this message? Thank you.

  • 37. Dave  |  July 11th, 2014 at 11:38 pm

    Thank you very much for sharing this. It works perfectly for what I wanted to do. I had struggled trying to use VBA functionality and nothing seemed to work. Within a few minutes, this solution is up and running and seems very reliable.

  • 38. MS Access: Restarting and&hellip  |  November 16th, 2014 at 12:03 am

    […] http://blog.nkadesign.com/2008/ms-access-restarting-the-database-programmatically/ […]

Leave a Comment

(Will not be shown)
Notify me of follow-up comments via e-mail

Some HTML allowed:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Trackback this post  |  Subscribe to the comments via RSS Feed


about

Renaud This is a simple technical weblog where I dump thoughts and experiences from my computer-related world.
It is mostly focused on software development but I also have wider interests and dabble in architecture, business and system administration.
More About me…

My StackOverflow Profile
My (sporadically active) StackOVerflow account

Most Recent Posts

Categories

Links