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