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 FunctionAs 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.