Access "Compact and Repair" programmatically

Question

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

DAO no good for you? I have some VBScript to hand that uses DAO. — Fionnuala, Aug 18 '10 at 09:31
This is an old post, but a first hit for me on Google. I just learned that an open database cannot be compacted and repaired - instead it must be set to compact on close. See [this Stack Overflow post](http://stackoverflow.com/a/1460506/1582588) on the subject. — Shrout1, Feb 19 '14 at 20:57

score 11 · Accepted Answer · edited Sep 21 '14 at 18:41

I don't have enough rep to reply to a previous "answer", but I wanted to provide some info that might be helpful to someone else in regards to the OP's question.

I have been using the JRO method for years to compact/repair my Access 2000 databases from VB.net. Every once in a blue moon, I have a client that has managed to corrupt a database (usually when connected to the database over a network and they suffer an unexpected network interruption). JRO (in my experience) works fine, as long as the database IS NOT CORRUPTED. I never could figure out why the database COULD be repaired if I used the Access application to do it, but when using MY application (which uses JRO), the compact/repair would always fail (database is in an unrecognized format).

So, after coming across this thread just an hour ago, I dropped a reference to DAO into my app and tried out its ability to repair a corrupted database as I just today had a client corrupt their database (third time its happened in about 8 years). Guess what, DAO was able to repair the database when JRO failed!

OK, so that is my experience with JRO vs. DAO. Hope it helps. Here is a piece of sample code for using CompactDatabase from DAO:

Dim dbCorrupt As String = "c:\CorruptedDB.mdb"
Dim dbRepaired As String = Path.Combine(Path.GetDirectoryName(dbPath), Path.GetFileNameWithoutExtension(dbPath) & "_Repaired.mdb")

Dim dao As New dao.DBEngine
dao.CompactDatabase(dbCorrupt, dbRepaired)

Which specific DAO entry in the Add References dialog did you select? — Dai, Mar 10 '19 at 21:43
Interop.DAO.dll is what I've got referenced. Don't recall exactly where I got that at. You will have to do a little sleuthing for it. — Scuzzlebutt, Mar 12 '19 at 18:53

score 4 · Answer 2 · edited Nov 03 '12 at 07:20

It is just four lines of code in c#.net

First use a library:

using JRO;

You want to compact and repair test.mdb with the following code:

string currentdirectory = System.IO.Directory.GetCurrentDirectory();
string oldmdbfile = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + currentdirectory + "\\test.mdb;Jet OLEDB:Database Password='xyz'";
string newmdbfile = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + currentdirectory + "\\test1.mdb;Jet OLEDB:Database Password='xyz'";
string oldmdbfilepath = currentdirectory + "\\test.mdb";
string newmdbfilepath = currentdirectory + "\\test1.mdb";

JRO.JetEngine engine = new JetEngine();
engine.CompactDatabase(oldmdbfile, newmdbfile);
File.Delete(oldmdbfilepath);
File.Move(newmdbfilepath, oldmdbfilepath);
MessageBox.Show("Database compact and repaired successfully !",);

Thus test.mdb will be compacted and repaired and a new file test1.mdb will be created. Then you just have to delete test.mdb and rename test1.mdb to test.mdb.

score 3 · Answer 3 · answered Aug 18 '10 at 21:41

3

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.

answered Aug 18 '10 at 21:41

Andrea Monelli

71
3

2

DAO is better than JRO, I think. – Fionnuala Aug 18 '10 at 22:08
Hi Remou, I'm afraid I cannot give an informed opinion on this, you know, I've used both but I never went deep into these technologies. – Andrea Monelli Aug 18 '10 at 22:37
Repair was removed from Jet 3.51, c. 1997. One should never use anything but Compact. – David-W-Fenton Aug 19 '10 at 17:43

score 2 · Answer 4 · answered Oct 24 '13 at 14:28

This solution works with the Access 2010 Database Engine:

Required reference:

Microsoft.Office.interop.access.dao

Code:

public void CompactDb(
    string sourceFilePath, string destFilePath, string password)
{
    var dbEngine = new Microsoft.Office.Interop.Access.Dao.DBEngine();

    dbEngine.CompactDatabase(sourceFilePath, destFilePath,
        ";pwd=" + password, null, ";pwd=" + password);
}

(The sourceFilePath and destFilePath should not be the same!)

CompactDatabase method parameters (from reflection):

void CompactDatabase(
    string SrcName, string DstName,
    object DstLocale = Type.Missing,
    object Options = Type.Missing,
    object SrcLocale = Type.Missing);

Make sure you run it under the same platform as the AccessDatabaseEngine (or Office) you installed (x86/x64).

score 2 · Answer 5 · answered Aug 18 '10 at 22:06

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

There's one issue with that code that I see off the bat, and that's that you're checking for the existence of the LDB to see if the MDB is open, and the LDB can persist even when the MDB is not open. To really see if the MDB is open, you should try to delete the LDB and if it fails, the MDB is open. — David-W-Fenton, Aug 19 '10 at 17:46
BTW, I just wrote a script for this on Monday, to run as a scheduled task on a server. Instead of Message Boxes, I logged everything. But my code is not as nice as yours. — David-W-Fenton, Aug 19 '10 at 17:46
I am fairly sure I cannot take credit for most of it, but I have had it for a good while, so I cannot assign credit for the relevant parts either. — Fionnuala, Aug 19 '10 at 20:09
strErr isn't assigned anything other than "True" so it should really be a boolean if it being used for bitwise comparrisons. — Tom 'Blue' Piddock, Apr 22 '13 at 07:13
@Blue you mean it should not be called strErr? You cannot type variables in VBScript. — Fionnuala, Apr 22 '13 at 07:33
Ah I see! So VBSCript is incredibly weak typed then :) Possibly instead of using `"True"` it then would it be better to use it as `true` , I believe the boolean assignment should work even if you can't type the object strongly. `boolErr = true` — Tom 'Blue' Piddock, Apr 22 '13 at 07:52
thanks for sharing the code. it works for me in terms of compact the database. but i had modified it according to my requirements. thanks again! — user2851376, Jul 26 '17 at 03:36

score 1 · Answer 6 · answered Jun 26 '15 at 12:16

1

Here is the official MS link, any further comments would be redundant. DBEngine.CompactDatabase Method

answered Jun 26 '15 at 12:16

htm11h

1,655
8
45
96

score 0 · Answer 7 · answered Feb 11 '13 at 06:20

Add ref to: Microsoft ActiveX Data Objects 2.x Library Microsoft Jet and Replication Objects 2.x Library

sDB = "c:\DB\myDb.mdb"
sDBtmp = "c:\DB\tempMyDb.mdb"
sPASSWORD = "password"

Dim oApp As Access.Application
Set oApp = New Access.Application
Call oApp.DBEngine.CompactDatabase(sDB, sDBtmp, dbLangGeneral, , ";pwd=" & sPASSWORD)

'wait for the app to finish
        DoEvents
'remove the uncompressed original
        Kill sDB
'rename the compressed file to the original to restore for other functions
        Name sDBtmp As sDB

Access "Compact and Repair" programmatically

7 Answers7

Linked

Related