Click here to Skip to main content
Click here to Skip to main content

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.

Introduction

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

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:

/// <summary>
/// MBD compact method (c) 2004 Alexander Youmashev
/// !!IMPORTANT!!
/// !make sure there's no open connections
///    to your db before calling this method!
/// !!IMPORTANT!!
/// </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 = 
      Activator.CreateInstance(Type.GetTypeFromProgID("JRO.JetEngine"));

    //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[] {
        connectionString,
        "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
    objJRO.GetType().InvokeMember("CompactDatabase",
        System.Reflection.BindingFlags.InvokeMethod,
        null,
        objJRO,
        oParams);

    //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.Delete(mdwfilename);
    System.IO.File.Move("C:\\tempdb.mdb", mdwfilename);

    //clean up (just in case)
    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

Share

About the Author

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

Comments and Discussions

 
QuestionIs this compaction code for access public domain or what license PinmemberMember 45854253-Sep-14 5:21 
Questioni had error using this code so i used Pinmemberyakirmanor5-Dec-13 2:45 
QuestionError [modified] PinmemberRogueX2010-Jul-13 8:17 
Getting an error. During my research it brings me to a page that has some info that might pertain to late binding. Below is the message I am referring to. I am using this code inside a script task inside of SSIS. Not sure if that matters or not...and if it does what to do.
 
"
Remarks
The common language runtime (CLR) transforms well-known HRESULTS to .NET exceptions, enabling COM objects to return meaningful error information to managed clients. The HRESULT to exception mapping also works in the other direction by returning specific HRESULTS to unmanaged clients.
When passing late-bound parameters to methods of Microsoft Office objects, a COMException exception may be thrown when the objects are COM objects. The late binder assumes that such method calls involve a ByRef parameter and that the property you pass has a Set accessor. If the property does not, the .NET Framework generates a MissingMethodException exception (HRESULT CORE_E_MISSINGMETHOD). To work around this behavior, use early-bound objects or pass a variable instead of a property of the object.
"
 
Same error when using outside of SSIS. Any ideas?

modified 10-Jul-13 15:01pm.

AnswerRe: Error PinmemberRogueX2010-Jul-13 16:07 
GeneralMy vote of 5 PinmemberStewBob18-Jul-12 6:17 
QuestionError when I run this Code PinmemberPooja N Khanapurkar20-Mar-12 5:41 
QuestionCompact and Repair Pinmemberlkn_o20-Mar-12 3:43 
GeneralMy vote of 5 Pinmemberkozura18-Feb-11 1:37 
GeneralCreate,Compact/Compress and Convert MDB database using ASP/VBScript Pingroupelizas12-Feb-10 0:21 
GeneralCreate,Compact/Compress and Convert MDB database using ASP/VBScript Pingroupelizas3-Feb-10 2:04 
GeneralReally useful PinmemberAlexander Stankov20-Sep-09 23:57 
QuestionCan it run on Windows 7? Pinmembertiendungdl13-Sep-09 19:29 
AnswerRe: Can it run on Windows 7? PinmemberJohn Reiter11-Oct-12 16:02 
QuestionInstance of type object ? PinmemberStuard25-Aug-09 16:12 
GeneralIt's Not Compacting or repairing MS access database properly.. Pinmembervermaraj8311-Aug-09 1:00 
GeneralEach iteration increases the size of my Access 97 MDB PinmemberMerrimackBobNH27-Jun-09 9:23 
GeneralြGreat PinmemberAung Sithu Kyaw2-Apr-08 17:02 
QuestionException has been thrown by the target of an invocation. PinmemberMember 26460376-Mar-08 23:11 
GeneralVery helpful. Thx. PinmemberMuaddubby27-Nov-07 18:57 
Generalthanks [modified] PinmemberM.S. Babaei17-Nov-07 0:24 
GeneralWarning PinmemberTom McAnnally15-Apr-07 15:04 
Questionhow can i deal with microsoft office by using c#? Pinmembergardinea20-Aug-06 23:52 
QuestionHow to set the connection.OpenSchema parameter ? Pinmemberliuzhuangli6-Jul-06 9:06 
Generalhelp - exception Pinmembersgrass-11125-Jun-06 11:03 
GeneralWhy not just this... PinmemberCabbi16-Jun-06 3:37 
GeneralRe: Why not just this... PinmemberLars [Large] Werner20-Jan-09 3:08 
Questionthe password has gone, why? Pinmemberjoeblack19-Dec-05 5:20 
AnswerRe: the password has gone, why? Pinmemberbolzmaster21-Dec-05 2:48 
AnswerRe: the password has gone, why? Pinmemberbolzmaster19-Jan-06 0:38 
QuestionIs this code absolutely safe ? PinsussAnonymous22-Sep-05 9:55 
QuestionCan I compact a local Access DB on remote PC? PinsussAnonymous9-Sep-05 22:54 
QuestionI need this code in C++ PinmemberM_Jafari26-Aug-05 4:01 
AnswerRe: I need this code in C++ Pinmemberhoang thoa19-May-06 0:03 
GeneralCom Object not registered PinmemberProgrammierhans21-Mar-05 4:17 
GeneralException PinsussBen1234567891-Mar-05 12:39 
GeneralRe: Exception PinmemberProgrammierhans18-Mar-05 6:36 
GeneralModified VB.NET Version Pinmemberjohndauphine16-Feb-05 12:53 
GeneralRe: Modified VB.NET Version Pinmembersuds458-Jun-08 6:28 
GeneralThanks PinsussAnonymous31-Jan-05 9:37 
GeneralBit Confussed PinsussAnonymous28-Jan-05 5:40 
Generalsmall issue Pinmemberwesman24-Dec-04 10:49 
GeneralRe: small issue Pinmemberwesman25-Dec-04 12:49 
GeneralInteropServices PinmemberMich2626-Sep-04 14:02 
GeneralThank you for the code exmple PinmemberMagnus Hauge19-Aug-04 10:54 
GeneralProgram Exception PinmemberMichael Greene4-Aug-04 12:13 
GeneralMessage Removed PinmemberAlexander Youmashev5-Aug-04 23:03 
GeneralRe: Program Exception PinmemberMike Gardiner16-Aug-05 5:48 
GeneralRe: Program Exception Pinmemberphamtoanthang19-Sep-07 18:57 
GeneralThanks PinmemberDavid ARNOLD30-Jul-04 1:13 
GeneralGreat PinsussDubant29-Jul-04 5:47 

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

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

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