I work on my own computers, a laptop and a desktop to do my main work on customer Access databases. Once I am at a point where I have something I want to give to the customer I want that process to work as quickly and as seamlessly as possible. Part of this process is to do the following steps:

• Copy my live database to a new file
• Run an Access Compact and Repair on the new file.
• Run an Access Decompile on the new file.
• Run an Access Compact and Repair on the new file again.

This process optimizes the file and prepares it for the customer’s live system.

The first compact and repair removes temporary data (written to something called pages internally) among many other things. See this article on Microsoft’s web site for some more info.

To understand the decompile action, let me explain what Access does with VBA code. Access doesn’t really run the code you type in. Access has to first compile the code which translates it for Access into code that is prepared for Access to run. Whenever you change the VBA code in your Access database it will flag the code as needing to be recompiled, but the old compiled code will remain until it is next executed and Access will then compile the new code.

The default settings will cause Access as described above, to compile code on demand, only when it needs to. The code will also be compiled differently for 32-bit and 64-bit systems. The decompile function of Access will mark all compiled code as needing to be recompiled, but will not delete the existing compiled code.

Hence, finally, once the database has been decompiled, you need to Compact and Repair the file again to remove the unused old compiled code.

This whole process often drastically reduces the file size and preps the file to be run on any system then compiling just the code it needs. Usually the customers aren’t modifying the code.

Doing the process is relatively easy, but it’s 4 more steps that I can cut out of my deployment process through automation. This way I will never forget to do it also which is a bonus!

So how do I automate the process?

I use VBA code in a module called Deploy in the existing file:

Public Sub DeployTest()
   Dim FileSystem As Object
   Dim LiveFilePath As String
   Set FileSystem = CreateObject("Scripting.FileSystemObject")
   LiveFilePath = CurrentProject.Path & "\MakeLive\" & CurrentProject.Name
   
   FileSystem.CopyFile CurrentProject.FullName, LiveFilePath
   
   DecompileCompactAndRepair LiveFilePath
   
   Set FileSystem = Nothing
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"
    
    Shell "cmd /c """"" & FullyQualifiedAccessApp & """ /repair """ & FullyQualifiedFileName & """"""
    
    Shell "cmd /c """"" & FullyQualifiedAccessApp & """ /decompile """ & FullyQualifiedFileName & """ /cmd Decompiling"""
    
    Shell "cmd /c """"" & FullyQualifiedAccessApp & """ /repair """ & FullyQualifiedFileName & """"""

End Sub

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

Making the code above work:

• First you need to create a regular module in the Access VBE window. You can copy the above code into it.
• Replace the path to your installation of Access in the DecompileCompactAndRepair sub.
• Create a folder in the same directory as your Access file. Make sure it is empty.
• Add the following line to the beginning of the Autoexec Macro (create one if you don’t have one)

Following the above steps will use Access command line flags to repair, then decompile, then repair the database again. Now, if your database runs a menuing system or anything else, the Autoexec Macro has to be added because it will start compiling code again right after it’s decompiled and you will have to manually shut down your database. Note that I am passing another command line parameter when decompiling so that the database will minimize it’s recompilation by only needing to compile that one simple function and then it will close automatically.

There are more adjustments I’ve made to this routine like deleting an existing file before copying the new one and you could add code to detect the folder and create it if it isn’t there.

Hope this helps you in your quest to do things faster through automation!