Code Snippets → Compact Database

ACCESS 2000 – 2003

Public Sub CompactData()
 
' you must make sure all objects are closed before trying to compact
Dim frm As Form
Dim rpt As Report
For Each frm In Forms
   DoCmd.Close acForm, frm.Name, acSaveNo
Next frm
For Each rpt in Reports
   DoCmd.Close acReport, rpt.Name, acSaveNo
Next rpt
      SendKeys "%(TDC)", False
End Sub

 ACCESS 2007

Public Sub Compact Data()
 
' you must make sure all objects are closed before trying to compact
Dim frm As Form
Dim rpt As Report
For Each frm In Forms
   DoCmd.Close acForm, frm.Name, acSaveNo
Next frm
For Each rpt in Reports
   DoCmd.Close acReport, rpt.Name, acSaveNo
Next rpt
                SendKeys "%(FMC)", False
End Sub

 ANOTHER WAY (multiple versions)

Public Function CompactDb()
Dim strDbNameOld As String
Dim strDBNameNew As String

strDbNameOld = "YourPathAndNameToTheFileYouWantToCompact"

        DBEngine.CompactDatabase strDBNameOld, strDBNameNew
        Kill strDBNameOld
        Name strDBNameNew As strDBNameOld
End Function