Have you ever needed to perform some action that would close Access and then would like to restart it automatically? Perhaps something along the lines of performing a Compact, or some update process.

As part of my last post regarding automating the Compact of the Front-End component of a split database I was faced with this very dilemma. What I came up with was a very straightforward VBScript.

The script, provided below, simply loops until no lock file is present (so the current instance shutdown) and then re-launches the database again. To work, it expects 2 input arguments: (1) the full path and filename of the database, (2) the full path and filename of the lock file.

'Input into a standard text editor, save it with the name RestartDb.vbs 
'and place in the same folder as the front-end
Dim Database
Dim LockFile
Dim i
Dim sAppEXE
Dim sRegKey

Database = WScript.Arguments(0)
LockFile = WScript.Arguments(1)

Set WshShell = CreateObject("WScript.Shell")
i = 0
'Wait until the lock file is gone
Do While FileExists(LockFile) = True
       PauseScript WshShell, 250
   i = i + 1
   if i >= 40 Then 'Number of loops to test for before exiting without success.  This number can be adjusted
       MsgBox "Unable to restart the Database as the Lock File is not going away.  Please try to relaunch it manually."
       Wscript.Quit
   End If
Loop 
PauseScript WshShell, 750

sAppEXE = "MSACCESS.EXE"
sRegKey = "HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\App Paths\" & sAppEXE & "\Path"
sAccessPath = WshShell.RegRead(sRegKey)
WshShell.Run _
        chr(34) & sAccessPath & sAppEXE & _
        chr(34) & " " & chr(34) & Database & chr(34)

Set WSHShell = Nothing



Function FileExists(FilePath)
  Set fso = CreateObject("Scripting.FileSystemObject")
  If fso.FileExists(FilePath) Then
    FileExists = True
  Else
    FileExists = False
  End If
End Function

Function PauseScript(WshShell, Delay)
   'Delay in milliseconds
   If WshShell Is Nothing Then Set WshShell = CreateObject("WScript.Shell")
   WScript.Sleep Delay
End Function

Then, in my calling database, I do something like:

Dim sDatabase             As String
Dim sLockFile             As String

' Input argument 1
sDatabase = Application.CurrentDb.Name 
' Input argument 2
sLockFile = Replace(sDatabase, ".accdb", ".laccdb") 
' Launch the Restart VBScript
Shell "wscript """ & Application.CurrentProject.Path & "\RestartDb.vbs"" """ & _
      sDatabase & """ """ & sLockFile & """"
'Close Access
Application.Quit

The key here being the fact that you launch the script prior to quitting Access.

Obviously, Database and LockFile could be hardcoded, but I wanted to make the script more versatile so it could be used in any application without modification.

The above is just one way such a script can be used, but it can easily be built upon and/or adapted for many other scenario.