How to Compact an Access Database File (.mdb)

With just a few days of regular use, a Microsoft Access database file can grow from a few megabytes to several tens of megabytes. Use this simple code to compact an Access database.

Important Points

1. This code requires a reference to Microsoft's DAO Object Library.

2. The name of the library may be different on your system, e.g. dao.dll or dao360.dll

3. DAO fails on x64 editions of Visual Studio with the error, "Retrieving the COM class factory for component with CLSID ... failed due to the following error: 80040154"

   • If you have a 64-bit development environment, change the Target CPU from "AnyCPU" to "x86" on the Advanced Compiler Settings property page.

Walkthrough

Add a reference to Microsoft's DAO Object Library in your project:

cCompactDB Class

The cCompactDB class takes a fully qualified path and filename to the .mdb to be compacted and returns True or False to indicate success or failure. The compaction code is just one line, and most of the other code is simple file management:

Imports System.IO

Public Class cCompactDB

    Public Function Compact(ByVal FQSourceDB As String) As Boolean

        Dim dbE As New DAO.DBEngine
        Dim FQBackupDB As String = Replace(FQSourceDB, ".mdb", ".bak")
        Dim TempDB As String = Path.GetTempPath & "compacted.mdb"

        Try
            ' Delete old backup file, if any.
            Kill(FQBackupDB)
        Catch ex As Exception
            ' Do nothing. A backup file doesn't exist.
        End Try

        ' Make a new backup.
        FileCopy(FQSourceDB, FQBackupDB)
        ' Compact the current database file
        dbE.CompactDatabase(FQSourceDB, TempDB)

        Try
            ' Delete the source file
            File.Delete(FQSourceDB)
            ' Move the compacted database back
            File.Move(TempDB, FQSourceDB)
        Catch ex As Exception
            Return False
        End Try

        Return True

    End Function

End Class

To use the code above, create a project and add this code to a button on the default form:

    Dim Compactor As New cCompactDB
    Dim dr As New DialogResult
    Dim ofd As New OpenFileDialog
    Dim bResult As Boolean

    ofd.Filter = "Access Databases (mdb)|*.mdb"
    dr = ofd.ShowDialog

    If dr <> Windows.Forms.DialogResult.OK Then
        Exit Sub
    End If

    bResult = Compactor.Compact(ofd.FileName)

    If Not bResult Then
        MsgBox("Compaction failed.")
    Else
        MsgBox("Compaction succeeded.")
    End Ife

Download Sample Project

Important: The download sample project has a reference set to dao260.dll. You may have to remove this reference and set a new one before the code will compile.

	All code examples on this site have been developed for .Net Framework 3.5