How to set Compact on Close on BE

The "Compact on Close" setting in the Current Database Options will automatically perform a Compact and Repair (C&R) each time the user closes the database file. Leaving this setting on could keep the file size of the database to a minimum, but it could also result in corruption if the operation is interrupted in the middle of the C&R process. Instead, it is a more common practice to check the file size of the FE and only set the "Compact on Close" option when the file size reaches a predetermined threshold.

Setting the "Compact on Close" option for the FE is a simple matter of executing the following code (as shown in my previous blog):

Application.SetOption "Auto Compact", True

Turning it off would then look like this:

Application.SetOption "Auto Compact", False

However, someone asked me recently how to set the "Compact on Close" setting for the BE from the FE. Obviously, using the above code only works for the FE, so I decided to create the following function. Just pass the filepath to the BE database and an optional True (to set Compact on Close) or False (to unset it) to the function.

Public Function SetAutoCompact(strDB As String, Optional bool As Boolean = True) As Byte
'8/18/2016
'Source: http://www.accessmvp.com/thedbguy
'Sets the Compact on Close flag of an external database

Dim objApp As Object
Dim objAcc As Object

If Dir(strDB) = "" Then
    MsgBox "Cannot find external DB file " & strDB, vbInformation, "Warning"
    SetAutoCompact = 1

Else
    Set objApp = CreateObject("Access.Application")
    Set objAcc = objApp.DBEngine.OpenDatabase(strDB)

    On Error Resume Next
    objAcc.Properties("Auto Compact") = bool
    If Err.Number = 0 Then
        On Error GoTo 0
    
    Else
        objAcc.Properties.Add objAcc.CreateProperty("Auto Compact", dbBoolean, bool)

    End If

    MsgBox "Finished setting 'Compact on Close' for " & strDB, vbInformation, "Done!"
    SetAutoCompact = 0
End If

Set objAcc = Nothing
Set objApp = Nothing

End Function

As previously mentioned, the above function should be used after checking the file size of the BE against a predetermined threshold. Also, compacting the BE over a network connection is not recommended.

As always, any comments are welcome. Thank you.