MS Access: how to compact current database in VBA

Question

Pretty simple question, I know.

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

Answer 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