[RESOLVED] Programmatically repairing and compacting Access databases

[klempie]

Can anyone advise or point me to an API which has a function I can call to compact and repair mdb's programmatically? TIA

[Ellis Dee]

Depends on if you're using ADO or DAO. Technically speaking, ADO can't do it, but if you're using ADO you can use JRO to compact & repair.

If you're compacting & repairing a database that's currently open, you need to close it before calling either of these functions and then open it again after they finish.

For ADO/JRO:

Private Const Provider = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
Private Const JetVersion = ";Jet OLEDB:Engine Type=5"

Private Sub CompactDatabase(pstrDatabase As String, Optional pstrPassword As String)
On Error GoTo CompactDatabaseErr
    Dim jro As JetEngine
    Dim strPassword As String
    Dim strTemp As String
    
    ' Generate temporary file name
    strTemp = Left(pstrDatabase, InStrRev(pstrDatabase, "\")) & "Compact.mdb"
    If Len(Dir(strTemp)) <> 0 Then Kill strTemp
    ' Create password string
    If Len(pstrPassword) <> 0 Then strPassword = ";Jet OLEDB:Database Password=" & pstrPassword
    ' Compact database
    Set jro = New JetEngine
    jro.CompactDatabase Provider & pstrDatabase & strPassword, Provider & strTemp & JetVersion & strPassword
    Set jro = Nothing
    ' Copy compacted version over old one
    Kill pstrDatabase
    Name strTemp As pstrDatabase
    
CompactDatabaseExit:
    Exit Sub
    
CompactDatabaseErr:
    MsgBox Err.Description, vbInformation, "Notice"
    Resume CompactDatabaseExit
End Sub

For DAO:

Public Sub CompactDatabase(pstrDatabase As String, Optional pstrPassword As String)
On Error GoTo CompactDatabaseErr
    Dim strTemp As String
    
    ' Generate temporary file name
    strTemp = Left(pstrDatabase, InStrRev(pstrDatabase, "\")) & "Compact.mdb"
    If Len(Dir(strTemp)) <> 0 Then Kill strTemp
    ' Compact
    If Len(pstrPassword) <> 0 Then
        DBEngine.CompactDatabase pstrDatabase, strTemp, ";pwd=" & pstrPassword, , ";pwd=" & pstrPassword
    Else
        DBEngine.CompactDatabase pstrDatabase, strTemp
    End If
    ' Copy compacted version over old one
    Kill pstrDatabase
    Name strTemp As pstrDatabase
    
CompactDatabaseExit:
    Exit Sub
    
CompactDatabaseErr:
    MsgBox Err.Description, vbInformation, "Notice"
    Resume CompactDatabaseExit
End Sub

[klempie]

Perfect response. Ta dude!

[Ellis Dee]

Fair warning: I was editing the code when you responded. Make sure you copy the code after you see this post.

[klempie]

OK thanks.