Compacting An MS Access Database

 The code below can be easily added to a module and even includes code to make a backup (Access is notorious for corrupting databases at a drop of a pin). Compacting large databases can take a long time, so provide your user with a “Please wait…” type message.

NOTE: Remember, you cannot compact a database while it’s open. Be sure to run this code before any code is run that opens the database, or any form with a data control is loaded. You could also run this at the end of your program.

Function bCompactMDB (sDatabase As String, bBackup As Integer) As Integer
Dim sNewFile As String
Dim sBakFile As String
     bCompactMDB = False
     MousePointer = 11
     sNewFile = Left$(sDatabase, Len(sDatabase) - 3) & "NEW"
     sBakFile = Left$(sDatabase, Len(sDatabase) - 3) & "BAK"
     On Error GoTo CompactError
     If Dir(sNewFile) <> "" Then
        Kill sNewFile
     End If
     CompactDatabase sDatabase, sNewFile
     If Dir(sBakFile) <> "" Then
       Kill sBakFile
     End If
     If bBackup = True Then
       Name sDatabase As sBakFile
     End If
     If Dir(sDatabase) <> "" Then
       Kill sDatabase
     End If
     Name sNewFile As sDatabase
     bCompactMDB = True
     MousePointer = 0
     Exit Function
     bCompactMDB = False
     MousePointer = True
End Function

This tip is reprinted from the VB Tips & Tricks Volume 1 book.
Parts of this tip was submitted by: David McCarter

Compatible With Visual Basic
Applies To Access 2.x Database File