I wrote a script to deploy my Microsoft Access application which utilizes my company’s Access JumpStart framework which is available for purchase from our store. In the previous parts I’ve explained how I’ve done the first 6 steps of my script:

Step 1 – Update the version number based on the latest commit to my versioning repository.

Step 2 – Create a copy of the currently running development database into a new folder.

Step 3 – Connect to the new database and set the Access JumpStart specific option override table for the live system option

Step 4 – Make sure the database properties in the live database are set correctly.

Step 5 – Delete temp table data.

Step 6 – Remove VBA references used for development and update compiler constants to do late binding.

And now I am moving on to discuss the final step of the script:

Step 7: Compact, Decompile, then Compact again.

I have found that this series of actions reduces the Access file to the minimum size possible. It doesn’t take a huge amount of time to do this manually if you have a Send To shortcut configured on your system to use for Decompiling, but I have to hold down shift a lot and it’s just cumbersome and didn’t feel streamlined. So how did I automate it? Here is the line in the Deploy sub and the helper functions I wrote to do this:

Public Sub Deploy()

   ' ...

   Dim LiveFilePath As String
   LiveFilePath = CurrentProject.Path & "\MakeLive\" & CurrentProject.Name

   ' ...
   
   ' Step 7 - Compact, Decompile, then Compact again
   DecompileCompactAndRepair LiveFilePath

End Sub

Public Sub DecompileCompactAndRepair(FullyQualifiedFileName As String)
    Dim appAccess As Access.Application
    Dim FullyQualifiedAccessApp As String
    FullyQualifiedAccessApp = "C:\Program Files\Microsoft Office\root\Office16\MSACCESS.EXE"
    
    HALsys.Shell "cmd /c """"" & FullyQualifiedAccessApp & """ /repair """ & FullyQualifiedFileName & """""", , True
    
    HALsys.Shell "cmd /c """"" & FullyQualifiedAccessApp & """ /decompile """ & FullyQualifiedFileName & """ /cmd Decompiling""", , True
    
    HALsys.Shell "cmd /c """"" & FullyQualifiedAccessApp & """ /repair """ & FullyQualifiedFileName & """""", , True

End Sub

You can see right away in the applicable section in the Deploy routine that I call a function to do this. In some of my deployment apps I do this for several databases so writing a function made more sense to me than repeating the code.

In the DecompileCompactAndRepair function I am first setting the path to Access on my dev system. There are ways to get this programmatically, but it can get pretty complicated if there are multiple versions of Access installed.

Then using a wrapper function I have in my library called HALsys.Shell which waits for the program to finish before moving to the next line as per this article from Microsoft: Determine when a shelled process ends | Microsoft Learn

I am running the cmd command with the /c flag to run the specified command line item. then I am using the /repair flag which auto opens, compacts, and closes the database. This needed no special setup in my database.

The second Shell command runs with the /decompile flag. This flag will decompile the database, but then Access continues to open the database with the default autoexec macro or opens the default form if it’s set. We don’t want that, because it compiles some code, and it doesn’t shut down by itself.

So in order to avoid this, I have added a new function at the beginning of my autoexec macro. This function looks for the command line flag “Decompiling” which is sent using the “/cmd Decompiling” flag when launching the database. Here is my autoexec macro:

And here is the function it is running initially.

Public Function CheckStartupConditionForDecompileFlag()
    If Command() = "Decompiling" Then Application.Quit
End Function

You can see that it just uses the VBA Command function to check for the Decompiling flag and if it exists, we immediately quit the application and nothing else runs.

So this fully automates my decompile and compact / repair actions.

Now my new database is ready to deploy with the same name as my dev database, in the MakeLive subfolder.

Thanks for joining me on this journey. Do you have any deployment tips or tricks you use? Let me know just by replying if you received this as an email! Otherwise you can leave a comment on the blog below. Note that it requires you to create a login and uses 2 factor authentication to keep the spammers away.