Access "Compact and Repair" programmatically

Question

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

Answer 1

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)

Answer 2

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.

Answer 3

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).