Compact Access Database
All Access databases benefit from periodically being compacted and repaired. Compacting and repairing performs several necessary tasks for the health of our Access databases:
Re-organizes the database's indices
Reclaims empty space from deleted records or objects, as well as temporary objects that Access created for us
Shared use of a database can slightly increase the risk of corruption. Compacting and repairing can reduce this risk.
Public Sub CompactDatabase()
Application.StatusBar = "Compacting Data in Database..."
'--------------------------------------------
' Compact & Repair Database
'--------------------------------------------
Dim dbCompact As String
Dim dbTempPath As String
Dim dbPath As String
dbPath = ThisWorkbook.Path & "\mydbname.accdb"
dbCompact = "mydbnametemp.accdb" 'Database to copy too
dbTempPath = ThisWorkbook.Path & dbCompact 'Path of new database
Call DBEngine.CompactDatabase(dbPath, dbTempPath) 'Compact data
DoEvents
Kill dbPath 'Delete original
DoEvents
Name dbTempPath As dbPath 'Rename new database to original name
Application.StatusBar = ""
'MsgBox "Data Compacted in DB - Completed", vbExclamation
End Sub
Subscribe
More to Explore
Tags
VBA Outlook KPI Excel Alt+F11 Dashboards Macros Recording Graphs Automation Developer WaterFall Powerpoint Charts Pivot Tables Forecast Charts