VBA/Excel/Access/Word/Access/Database Compact

Compacting a Database

 
" use the References dialog box to set up a reference to the
" Microsoft Jet and Replication Objects Library
Sub CompactDb()
   Dim jetEng As JRO.JetEngine
   Dim strCompactFrom As String
   Dim strCompactTo As String
   Dim strPath As String
   strPath = CurrentProject.Path & "\"
   strCompactFrom = "mydb.mdb"
   strCompactTo = "mydbComp.mdb"
   On Error GoTo HandleErr
   Set jetEng = New JRO.JetEngine
   jetEng.rupactDatabase "Data Source=" & strPath & strCompactFrom & ";", "Data Source=" & strPath & strCompactTo & ";"
   Kill strPath & strCompactFrom
   Name strPath & strCompactTo As strPath & strCompactFrom
ExitHere:
   Set jetEng = Nothing
   MsgBox "Compacting completed."
   Exit Sub
HandleErr:
   MsgBox Err.Number & ": " & Err.Description
   Resume ExitHere
End Sub