Is it possible to "compact and repair" an Access database programmatically somehow (using ADOX, using OleDbConnection etc.)?
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.
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.
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)
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
.
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).
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With