How to compact ACCDB (Access 2007) Database using VBA?

[Blasphemer]

How to compact ACCDB (Access 2007) Database using VBA?

Please help

[CreganTur]

You can accomplish this using ADO and a direct connection to the JET engine. The following example also deals with setting passwords an dopening databases. Just take what you need ;)

You'll need a reference (Tools->References) to the Microsoft ActiveX Data Objects library, and the Jet and Replication Objects Library.


Sub setPass_AndOpenDB_withADO()
Dim jetEng As JRO.JetEngine
Dim conn As ADODB.Connection
Dim strCompactFrom As String
Dim strCompactTo As String
Dim strPath As String

strPath = CurrentProject.Path & "\"

strCompactFrom = "Northwind.mdb"
strCompactTo = "Northwind_P.mdb"

On Error GoTo ErrorHandler

Set jetEng = New JRO.JetEngine

' Compact the database specifying
' the new database password
jetEng.CompactDatabase "Data Source=" & _
strPath & strCompactFrom & ";", _
"Data Source=" & strPath & strCompactTo & ";" & _
"Jet OLEDB:Database Password=welcome"

MsgBox "The database file " & strPath & strCompactTo & _
" has been protected with password."
Set jetEng = Nothing

' now open the password-protected MDB database
Set conn = New ADODB.Connection
With conn
.Provider = "Microsoft.Jet.OLEDB.4.0;"
.ConnectionString = "Data Source=" & _
strPath & strCompactTo & ";" & _
"Jet OLEDB:Database Password=welcome;"
.Open
End With

If conn.State = adStateOpen Then
MsgBox "Password protected database was opened."
End If

conn.Close
MsgBox "Password protected database was closed."

Set conn = Nothing
Exit Sub
ErrorHandler:
If Err.Number = -2147217897 Then
Kill strPath & strCompactTo
ElseIf Err.Number = -2147467259 Then
MsgBox "Make sure to close the " & strCompactFrom & _
" database file prior to compacting it."
Exit Sub
Else
MsgBox Err.Number & ": " & Err.Description
Exit Sub
End If
Resume
End Sub