Access "Compact and Repair" programatically

Question

Hi,

Is it possible to "compact and repair" an Access database programatically somehow ( using ADOX, using OleDbConnection etc.) ?

All the best, Sebastian

Answer 1

Hi, it'possible compacting and repairing an MS ACCESS database in two ways:

• using DAO: in DAO350 there's a method RepairDatabase(), while in DAO360 there's CompactDatabase()
• using MDAC+JRO:

As an example, in VB6 (old, old, old...) do this:

Dim jro As jro.JetEngine
Set jro = New jro.JetEngine
jro.CompactDatabase "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\db_to_repair.mdb;Jet OLEDB:Database Password=mypass", _ 
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\repaired_db.mdb;Jet OLEDB:Engine Type=4;Jet OLEDB:Database Password=mypass"

As you will notice, the function requires that you specify the name of the db to be repaired and the name of the repaired database.

Answer 2

Sample code for VBScript.

Dim objEngine
Dim objProcess
'Dim objDB
Dim strDb1

Dim strPath
Dim strFile
Dim strDAOversion
Dim strApplicationName
Dim strErr

Dim strMsg
Dim FSO

strPath = "C:\Docs\"

strFile = "Some.mdb"
strDb1 = strPath & strFile

Set FSO=CreateObject("Scripting.FileSystemObject")

strDAOversion = "DAO.DBEngine.36"
strApplicationName = "Some.mdb"

strMsg = "About to perform a COMPACT on "
strMsg = strMsg & chr(10) & chr(10)
strmsg = strMsg & strApplicationName
strMsg = strMsg & chr(10) & chr(10)
strmsg = strmsg & "Please ask everyone to EXIT THE SYSTEM."
strMsg = strmsg & chr(10) & chr(10)
strmsg = strmsg & space(12) & "It is VITAL you do not exit windows until"
strMsg = strMsg & chr(10)
strMsg = strMsg & space(12) & "you receive the confirmation message."
strMsg = strmsg & chr(10) & chr(10)
strMsg = strMsg & space(6) & "Press OK to continue or Cancel to stop the process."


If MsgBox(strMsg, 1, strApplicationName) = 1 Then

  Set objEngine = WScript.CreateObject(strDAOversion)

  Call CompactDB(FSO, objEngine, strDb1, "password")

  If strErr="True" Then
    strMsg = "Please correct the problem and try again."
    MsgBox strMsg, 1, strApplicationName
  Else
    strMsg = "Database compacting complete."
    MsgBox strMsg, 1, strApplicationName
  End If
End If


Function CompactDB(objFSO, objEngine, strDb, pwd)

'Compact the database

Dim strdbtemp
Dim MsgText

strdbtemp = Left(strDb, Len(strDb) - 3) & "ldb"

If FSO.FileExists(strdbtemp) = True Then 'if ldb file exists, db is still open.
MsgText = "You have not exited the file. Please close and try again."
MsgBox MsgText, 1, strApplicationName
strErr="True"
Exit Function
End If

If FSO.FileExists(strDb1) = False Then
MsgText = "Cannot locate the database at " & strDB
MsgBox MsgText, 1, strApplicationName
strErr="True"
Exit Function
End If

strdbtemp = Left(strDb, Len(strDb) - 3) & "tmp"

If pwd = "" Then
objEngine.CompactDatabase strDb, strdbtemp
Else
objEngine.CompactDatabase strDb, strdbtemp, , , ";pwd=" & pwd
End If

If Err = 0 Then
FSO.deletefile strDb
FSO.copyfile strdbtemp,strDb
FSO.deletefile strdbtemp
Else
MsgText = "Error during COMPACT process for " & strDB
MsgBox MsgText, 1, strApplicationName
strErr="True"
End If

End Function