In this article, you will find code that compacts and repairs an MS Access DB from a C# .NET application.
Introduction
This code compacts and repairs an MS Access database from a C# .NET application, no matter whether it's a simple ".mdb", or a ".mdw"-secured workgroup-shared DB. It performs exactly the same operation as "Tools - Database Utils - Compact and Repair Database..." menu item in the MS Access application. The code uses "late binding" (creating COM-objects in memory at runtime), and that's why you don't need any annoying interop COM references in your project. You don't even need MS Office installed. Just make sure you have a Jet Engine (Jet is included in MDAC package, which comes with any Windows installation starting from NT 4).
Background
Don't you hate COM-library references in .NET-projects? I believe that pure .NET-code has to be free of any interops, RCWs, and other referenced COM-stuff. Basically because there's a load of different versions of MS libraries (for example, MS Office Object Library 9, 10, 11 etc.). We never know what version of MS Office is installed on a client machine, that's why we should access a COM-object via ProgID
, and not CLSID
. For example: you want to be sure that when you call for an "Excel.Application
", you get Excel, no matter what version of MS Office is installed. And when you add a reference "MS Excel 10 Object library", you add a strong limitation to your software! So... use System.Reflection
and late binding.
Using the Code
Just call a method CompactAccessDB
. This method compacts and repairs your database.
Parameters:
connectionString
- connection string to your database mdwfilename
- is a full name (path+name) of an MDB-file you want to compact and repair
Due to Jet limitations, the method compacts your database to a new file, so we have to copy the new compacted file over an old one.
When you call this method, make sure that there's no open connections to your database. Stop your threads.
Now, to the code:
public static void CompactAccessDB(string connectionString, string mdwfilename)
{
object[] oParams;
object objJRO =
Activator.CreateInstance(Type.GetTypeFromProgID("JRO.JetEngine"));
oParams = new object[] {
connectionString,
"Provider=Microsoft.Jet.OLEDB.4.0;Data" +
" Source=C:\\tempdb.mdb;Jet OLEDB:Engine Type=5"};
objJRO.GetType().InvokeMember("CompactDatabase",
System.Reflection.BindingFlags.InvokeMethod,
null,
objJRO,
oParams);
System.IO.File.Delete(mdwfilename);
System.IO.File.Move("C:\\tempdb.mdb", mdwfilename);
System.Runtime.InteropServices.Marshal.ReleaseComObject(objJRO);
objJRO=null;
}
Points of Interest
Interesting, that Jet Engine 5 is used for JET4X databases. Be careful. See the table:
Jet OLEDB:Engine Type | Jet x.x Format MDB Files |
1 | JET10 |
2 | JET11 |
3 | JET2X |
4 | JET3X |
5 | JET4X |
License
This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt, please contact the author via the discussion board below.
A list of licenses authors might use can be found here.
History
- 20th April, 2021: Initial post