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

Compacting a Database

   <source lang="vb">

" 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

</source>