CompileCurrentAccessApp(): Use VBA to Create an ACCDE or MDE of the Currently Open Access File

A couple of years ago, fellow Access MVP Colin Riddington wrote a series of articles with information on using an undocumented SysCmd function to compile an .accdb/.mdb into an .accde/.mde:

One of the limitations of that SysCmd function is that it only works on other files and cannot be used to compile the currently open Access file.  

Let's overcome that limitation.

The Approach

At a high level, here's what we will do:

1. Create a temporary copy of the current Access file
2. Compile it using the undocumented SysCmd command
3. Remove the temporary file

The Build Subfolder

To keep things simple, I've chosen to hard code the destination folder to be a subfolder named \Build\ under the current file's folder.

I did this for a few reasons:

• Avoid annoying errors: If we create a file whose name differs from the original only by its file extension, then when we try to launch the newly created .accde/.mde we get the error, "The database has been placed in a state by user 'Admin' on machine 'MyMachine' that prevents it from being opened or locked," unless we close the original Access file first.  It appears Access is just looking for a lock file with the same name as the launched file and a .laccdb or .ldb file extension; thus, the lock file for the already open .accdb file will prevent an otherwise same-named .accde file from opening at the same time.
• "Build" files are inherently temporary: Most programming languages generate compiled files from their source code in a subfolder named "Build." Since the files created in that subfolder are automatically created from the source code, there is no expectation that those files will be preserved.  Rather, they can be considered "temporary" in the sense that they can easily be recreated at any time.
• Makes it easier to run: At one point, I introduced an optional parameter which you could use to explicitly set a destination path if you wanted.  The problem is that once you introduce a parameter to a method in VBA, you can no longer run the method from the VBE by simply placing your cursor in the method and pressing [F5].  Instead, you would have to call the procedure from the immediate window.  Not a big deal, but it introduces friction into the process.

The Temporary File Name

I use the CreateGuid() function to build a temporary file name in the same folder as the original file.

I do this for a few reasons:

• Using a GUID means I don't have to worry about collisions with existing file names.
• Creating a copy of the file in the same folder means I don't have to worry about Access Trust Center settings (assuming the original file is in a "Trusted Location" and is not simply a "Trusted Document").
• If something breaks in the process and the file with the GUID doesn't get cleaned up, I won't mistake it for anything other than a temporary file.

The Code

I've included the code for the CreateGUID() function in the code below.

The code can be copied and pasted into a blank standard module for a fully-working solution that can be easily integrated into your projects.

'These Declare lines for CreateGUID() go at the very top of the code module
#If VBA7 Then
    Private Declare PtrSafe Function CoCreateGuid Lib "ole32" (id As Any) As Long
#Else
    Private Declare Function CoCreateGuid Lib "ole32" (id As Any) As Long
#End If


' ----------------------------------------------------------------
' Procedure : CompileCurrentAccessApp
' Date      : 4/25/2024
' Author    : Mike Wolfe
' Source    : https://nolongerset.com/compilecurrentaccessapp/
' Purpose   : Creates a compiled .accde or .mde version of the current file.
' Notes     - The compiled file is created in a subfolder named \Build\.
'           - For example: C:\Dev\MyApp.accdb -> C:\Dev\Build\MyApp.accde
'           - Any existing .accde/.mde file is overwritten without warning.
'           - The current file must be inside a Trusted Location.
' ----------------------------------------------------------------
Public Sub CompileCurrentAccessApp()
    'Get full path of current Access file
    Dim fpSrc As String
    fpSrc = CurrentProject.Path & "\" & CurrentProject.Name
    
    'Determine source and destination file extensions based on current Access file
    Dim feSrc As String, feDest As String
    If fpSrc Like "*.accdb" Then
        feSrc = "accdb"
        feDest = "accde"
    ElseIf fpSrc Like "*.mdb" Then
        feSrc = "mdb"
        feDest = "mde"
    Else
        'Throw "Unsupported file extension: {0}", CurrentProject.Name
        MsgBox "Unsupported file extension: " & CurrentProject.Name, vbExclamation, "Error"
        Exit Sub
    End If
    
    
    'Create a temp file to copy the open Access file to
    '   NOTE: we create the temp file in the current folder under the assumption that the
    '         current folder is a Trusted Location (and the user's Temp folder is likely not)
    Dim fpTemp As String
    fpTemp = CurrentProject.Path & "\" & CreateGUID() & "." & feSrc
    
    'NOTE: the built-in FileCopy method fails when trying to copy an open file,
    '      so we have to use the FileSystemObject's CopyFile method instead
    'FileCopy fpSrc, fpTemp
    Dim Fso As Object  'Scripting.FileSystemObject
    Set Fso = CreateObject("Scripting.FileSystemObject")
    Fso.CopyFile fpSrc, fpTemp

    
    'Create a \Build\ subfolder if one does not exist
    Dim foDest As String
    foDest = CurrentProject.Path & "\Build"
    If Not Fso.FolderExists(foDest) Then MkDir foDest
    
    'Create a destination file name that matches the source file name but is the compiled version
    '   (e.g., .accdb -> .accde OR .mdb -> .mde)
    Dim fnDest As String
    fnDest = Left(CurrentProject.Name, Len(CurrentProject.Name) - 1) & "e"
    
    'Create a full path to the destination file name (inside the \Build\ subfolder)
    Dim fpDest As String
    fpDest = foDest & "\" & fnDest
    
    'The host Application instance cannot be used; we must instantiate a separate process:
    Dim oApp As Access.Application
    Set oApp = New Access.Application
    
    'The acSysCmdCompile constant did not get introduced until Access 365 version 2308;
    '   we're explicitly defining it here so that this code will compile on earlier versions of Access
    '   for more info, see: https://isladogs.co.uk/sys-cmd-actions/index.html
    Const acSysCmdCompile As Long = 603
    oApp.SysCmd acSysCmdCompile, (fpTemp), (fpDest)  'use parentheses to force path arguments to be passed by value
    
    'Make sure the compile finishes before trying to remove the temporary file
    DoEvents
    
    'Remove the temporary file
    Kill fpTemp
    
    'Clean up the msaccess.exe process we created
    Set oApp = Nothing
    
    Debug.Print "Done."
End Sub


' ----------------------------------------------------------------
' Procedure  : CreateGUID
' Author     : Dan (webmaster@1stchoiceav.com)
' Source     : http://allapi.mentalis.org/apilist/CDB74B0DFA5C75B7C6AFE60D3295A96F.html
' Adapted by : Mike Wolfe
' Republished: https://nolongerset.com/createguid/
' Date       : 8/5/2022
' ----------------------------------------------------------------
Private Function CreateGUID() As String
    Const S_OK As Long = 0
    Dim id(0 To 15) As Byte
    Dim Cnt As Long, GUID As String
    If CoCreateGuid(id(0)) = S_OK Then
        For Cnt = 0 To 15
            CreateGUID = CreateGUID & IIf(id(Cnt) < 16, "0", "") + Hex$(id(Cnt))
        Next Cnt
        CreateGUID = Left$(CreateGUID, 8) & "-" & _
                     Mid$(CreateGUID, 9, 4) & "-" & _
                     Mid$(CreateGUID, 13, 4) & "-" & _
                     Mid$(CreateGUID, 17, 4) & "-" & _
                     Right$(CreateGUID, 12)
    Else
        MsgBox "Error while creating GUID!"
    End If
End Function

Cover image generated by DALL-E-3