Home Excel Powerpoint Word Outlook Error Codes

VBA Useful Codes

Welcome to the World of VBA
Home > VBAUsefulCodes

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
 Kill dbPath 'Delete original
 Name dbTempPath As dbPath 'Rename new database to original name
 Application.StatusBar = ""

 'MsgBox "Data Compacted in DB - Completed", vbExclamation
 End Sub


Enter your e-mail below and get notified on the latest blog posts.


VBA Outlook KPI Excel Alt+F11 Dashboards Macros Recording Graphs Automation Developer WaterFall Powerpoint Charts Pivot Tables Forecast Charts

Follow Us