Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Access "Compact and Repair" programmatically

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

like image 546
MadSeb Avatar asked Aug 17 '10 14:08

MadSeb


People also ask

How do I run a compact and repair in Access?

Manually compact and repair a database that you have open Select File > Info > Compact & Repair Database. Access creates a copy of the compacted and repaired database in the same location.

Where can you Access the Compact & Repair command?

Compact and Repair from Within Microsoft Access After backing up the file, open each file in Microsoft Access. Go to Database Tools. Click Compact and Repair Database.


3 Answers

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)
like image 58
Scuzzlebutt Avatar answered Sep 25 '22 00:09

Scuzzlebutt


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.

like image 43
udayphadkepune Avatar answered Sep 27 '22 00:09

udayphadkepune


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

like image 41
Danny Varod Avatar answered Sep 26 '22 00:09

Danny Varod