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

CompactError:

     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