Click here to Skip to main content
13,198,850 members (41,943 online)
Click here to Skip to main content
Add your own
alternative version


92 bookmarked
Posted 20 Jul 2004

Compact and Repair Access Database using C# and late binding

, 20 Jul 2004
Rate this:
Please Sign up or sign in to vote.
The code compacts and repairs an MS Access database from C# code using a JRO.JetEngine COM-object. Binding occurs at runtime, no references, no interops. Pure System.Reflection.


This code compacts and repairs an MS Access database from a C# .NET application, no matter if 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).


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.


  • 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:

/// <summary>
/// MBD compact method (c) 2004 Alexander Youmashev
/// !make sure there's no open connections
///    to your db before calling this method!
/// </summary>
/// <param name="connectionString">connection string to your db</param>
/// <param name="mdwfilename">FULL name
///     of an MDB file you want to compress.</param>
public static void CompactAccessDB(string connectionString, string mdwfilename)
    object[] oParams;

    //create an inctance of a Jet Replication Object
    object objJRO = 

    //filling Parameters array
    //cnahge "Jet OLEDB:Engine Type=5" to an appropriate value
    // or leave it as is if you db is JET4X format (access 2000,2002)
    //(yes, jetengine5 is for JET4X, no misprint here)
    oParams = new object[] {
        "Provider=Microsoft.Jet.OLEDB.4.0;Data" + 
        " Source=C:\\tempdb.mdb;Jet OLEDB:Engine Type=5"};

    //invoke a CompactDatabase method of a JRO object
    //pass Parameters array

    //database is compacted now
    //to a new file C:\\tempdb.mdw
    //let's copy it over an old one and delete it

    System.IO.File.Move("C:\\tempdb.mdb", mdwfilename);

    //clean up (just in case)

Points of Interest

Interesting, that Jet Engine 5 is used for JET4X databases. Be careful. See the table:

Jet OLEDB:Engine TypeJet x.x Format MDB Files


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


About the Author

Alex Yumas
Founder Jitbit
United Kingdom United Kingdom
I'm CEO/Founder and lead developer at Jitbit Software

You may also be interested in...

Comments and Discussions

QuestionException has been thrown by the target of an invocation. Pin
Member 26460376-Mar-08 22:11
memberMember 26460376-Mar-08 22:11 
GeneralVery helpful. Thx. Pin
Muaddubby27-Nov-07 17:57
memberMuaddubby27-Nov-07 17:57 
Generalthanks [modified] Pin
M.S. Babaei16-Nov-07 23:24
memberM.S. Babaei16-Nov-07 23:24 
GeneralWarning Pin
Tom McAnnally15-Apr-07 14:04
memberTom McAnnally15-Apr-07 14:04 
Questionhow can i deal with microsoft office by using c#? Pin
gardinea20-Aug-06 22:52
membergardinea20-Aug-06 22:52 
QuestionHow to set the connection.OpenSchema parameter ? Pin
liuzhuangli6-Jul-06 8:06
memberliuzhuangli6-Jul-06 8:06 
Generalhelp - exception Pin
sgrass-11125-Jun-06 10:03
membersgrass-11125-Jun-06 10:03 
GeneralWhy not just this... Pin
Cabbi16-Jun-06 2:37
memberCabbi16-Jun-06 2:37 

I was wondering why not giving the source and destination the same database connection to avoid the harassing 'Delete' issue. After a few tries discovered that it was not possible with both OLE DB and ADO. So I googled a bit and found a different but more compact way usin ODBC:

<br />
   [System.Runtime.InteropServices.DllImport("ODBCCP32.dll")]<br />
   private static extern bool SQLConfigDataSource(IntPtr parent, int request, string driver, string attributes);<br />
   private const int ODBC_ADD_DSN = 1;<br />
<br />
   public static bool Compact(string mdbFileName)<br />
   {<br />
      string cmd = string.Format("COMPACT_DB={0} {0}\0\0", mdbFileName);<br />
      return SQLConfigDataSource((IntPtr)0, ODBC_ADD_DSN, "Microsoft Access Driver (*.MDB)", cmd);<br />
   }<br />
<br />
   public static bool Compact(string mdbFileName, string password)<br />
   {<br />
      string cmd = string.Format("COMPACT_DB={0} {0}\0PWD={1}\0\0", mdbFileName, password);<br />
      return SQLConfigDataSource((IntPtr)0, ODBC_ADD_DSN, "Microsoft Access Driver (*.MDB)", cmd);<br />
   }<br />

It works with my current OS configuration but from the MSDN Library I also found this old article:
BUG: Database Compaction with SQLConfigDataSource Fails when Access Database is Password Protected
GeneralRe: Why not just this... Pin
Lars [Large] Werner20-Jan-09 2:08
memberLars [Large] Werner20-Jan-09 2:08 
Questionthe password has gone, why? Pin
joeblack19-Dec-05 4:20
memberjoeblack19-Dec-05 4:20 
AnswerRe: the password has gone, why? Pin
bolzmaster21-Dec-05 1:48
memberbolzmaster21-Dec-05 1:48 
AnswerRe: the password has gone, why? Pin
bolzmaster18-Jan-06 23:38
memberbolzmaster18-Jan-06 23:38 
QuestionIs this code absolutely safe ? Pin
Anonymous22-Sep-05 8:55
sussAnonymous22-Sep-05 8:55 
QuestionCan I compact a local Access DB on remote PC? Pin
Anonymous9-Sep-05 21:54
sussAnonymous9-Sep-05 21:54 
QuestionI need this code in C++ Pin
M_Jafari26-Aug-05 3:01
memberM_Jafari26-Aug-05 3:01 
AnswerRe: I need this code in C++ Pin
hoang thoa18-May-06 23:03
memberhoang thoa18-May-06 23:03 
GeneralCom Object not registered Pin
Programmierhans21-Mar-05 3:17
memberProgrammierhans21-Mar-05 3:17 
GeneralException Pin
Ben1234567891-Mar-05 11:39
sussBen1234567891-Mar-05 11:39 
GeneralRe: Exception Pin
Programmierhans18-Mar-05 5:36
memberProgrammierhans18-Mar-05 5:36 
GeneralModified VB.NET Version Pin
johndauphine16-Feb-05 11:53
memberjohndauphine16-Feb-05 11:53 
GeneralRe: Modified VB.NET Version Pin
suds458-Jun-08 5:28
membersuds458-Jun-08 5:28 
GeneralThanks Pin
Anonymous31-Jan-05 8:37
sussAnonymous31-Jan-05 8:37 
GeneralBit Confussed Pin
Anonymous28-Jan-05 4:40
sussAnonymous28-Jan-05 4:40 
Generalsmall issue Pin
wesman24-Dec-04 9:49
memberwesman24-Dec-04 9:49 
GeneralRe: small issue Pin
wesman25-Dec-04 11:49
memberwesman25-Dec-04 11:49 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web02 | 2.8.171020.1 | Last Updated 21 Jul 2004
Article Copyright 2004 by Alex Yumas
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid