MS Access: how to compact current database in VBA

Solution 1:

If you want to compact/repair an external mdb file (not the one you are working in just now):

Application.compactRepair sourecFile, destinationFile

If you want to compact the database you are working with:

Application.SetOption "Auto compact", True

In this last case, your app will be compacted when closing the file.

My opinion: writting a few lines of code in an extra MDB "compacter" file that you can call when you want to compact/repair an mdb file is very usefull: in most situations the file that needs to be compacted cannot be opened normally anymore, so you need to call the method from outside the file.

Otherwise, the autocompact shall by default be set to true in each main module of an Access app.

In case of a disaster, create a new mdb file and import all objects from the buggy file. You will usually find a faulty object (form, module, etc) that you will not be able to import.

Solution 2:

If you have the database with a front end and a back end. You can use the following code on the main form of your front end main navigation form:

Dim sDataFile As String, sDataFileTemp As String, sDataFileBackup As String
Dim s1 As Long, s2 As Long

sDataFile = "C:\MyDataFile.mdb"
sDataFileTemp = "C:\MyDataFileTemp.mdb"
sDataFileBackup = "C:\MyDataFile Backup " & Format(Now, "YYYY-MM-DD HHMMSS") & ".mdb"

DoCmd.Hourglass True

'get file size before compact
Open sDataFile For Binary As #1
s1 = LOF(1)
Close #1

'backup data file
FileCopy sDataFile, sDataFileBackup

'only proceed if data file exists
If Dir(sDataFileBackup vbNormal) <> "" Then

        'compact data file to temp file
        On Error Resume Next
        Kill sDataFileTemp
        On Error GoTo 0
        DBEngine.CompactDatabase sDataFile, sDataFileTemp

        If Dir(sDataFileTemp, vbNormal) <> "" Then
            'delete old data file data file
            Kill sDataFile

            'copy temp file to data file
            FileCopy sDataFileTemp, sDataFile

            'get file size after compact
            Open sDataFile For Binary As #1
            s2 = LOF(1)
            Close #1

            DoCmd.Hourglass False
            MsgBox "Compact complete " & vbCrLf & vbCrLf _
                & "Size before: " & Round(s1 / 1024 / 1024, 2) & "Mb" & vbCrLf _
                & "Size after:    " & Round(s2 / 1024 / 1024, 2) & "Mb", vbInformation
        Else
            DoCmd.Hourglass False
            MsgBox "ERROR: Unable to compact data file"
        End If

Else
        DoCmd.Hourglass False
        MsgBox "ERROR: Unable to backup data file"
End If

DoCmd.Hourglass False