Compact and Repair MS Access Database

[LalitPandey87]

The easiest way to avoid Database corruption is to compact & Repair it regularly.
In Access 2007, you can manually compact by clicking the Office Button, select Manage, and then choose Compact And Repair Database from the Manage This Database list.
To automate the process using VBA here is the Code.

Function Database_Compact_and_Repaire(strDbPath As String, Optional strDBPass As String = "") As Long

        On Error GoTo ErrFailed
            'Delete the existing temp database
            If Len(Dir$(strDbPath & ".tmp")) Then
                VBA.Kill strDbPath & ".tmp"
            End If
            With CreateObject("JRO.JetEngine")
                If strDBPass = "" Then 'DB without password
                    .CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDbPath, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDbPath & ".tmp;Jet OLEDB:Encrypt Database=True"
                Else             'Password protected db
                    .CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDbPath & ";Jet OLEDBatabase Password=" & strDBPass, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDbPath & ".tmp;Jet OLEDB:Encrypt Database=True;Jet OLEDBatabase Password=" & strDBPass
                End If
            End With
        On Error GoTo 0
            VBA.Kill strDbPath 'Delete the existing database
            Name strDbPath & ".tmp" As strDbPath 'Rename the compacted database
ErrFailed:
            Database_Compact_and_Repaire = Err.Number
            
            strDbPath = vbNullString
            strDBPass = vbNullString
    
End Function

You can call this function as below.
Without Password

Call Database_Compact_and_Repaire("d:\test.MDB")

With Password

Call Database_Compact_and_Repaire("d:\test.MDB", "password")