[RESOLVED] Programmatically repairing and compacting Access databases

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

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:

Code:

---

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:

Code:

---

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

---

Perfect response. :) Ta dude!

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

OK thanks.