Thursday, February 21, 2013

Programmatically Compact and Repair Access Example

string connectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;
                            Data Source=" + mainPath + @";
                            Jet OLEDB:Engine Type=5;
                            Jet OLEDB:Database Password=XXX;"
;
string compactedNewFilePathAndName = mainPath.Replace(".accdb", "_TEMP.accdb");
CompactAndRepair.CompactAccessDB(connectionString, compactedNewFilePathAndName, mainPath);


--------------------
--------------------

class CompactAndRepair
{
/// <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 compactedNewFilePathAndName, string mainPath)
{
   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.ACE.OLEDB.12.0;
                  Data Source=" + compactedNewFilePathAndName + @";
                  Jet OLEDB:Database Password=XXXX;
                  Jet OLEDB:Engine Type=5"
   };

    oParams = new object[] { connectionString,
            connectionString.Replace(mainPath, compactedNewFilePathAndName)
    };
   //invoke a CompactDatabase method of a JRO object
   //pass Parameters array
   try
   {
       objJRO.GetType().InvokeMember(
"CompactDatabase",
                              system.Reflection.BindingFlags.InvokeMethod,
                              null, objJRO, oParams);
   }
   catch (Exception ex)
   {
      //do something

      return;
   }

   //clean up (just in case)
   System.Runtime.InteropServices.Marshal.ReleaseComObject(objJRO);
   objJRO = null;

   //database is compacted now
   //to a new file compactedNewFilePathAndName
   //let's copy it over an old one and delete it
   try
   {
      System.IO.
File.Delete(mainPath);
      System.IO.File.Move(compactedNewFilePathAndName, mainPath);
   }    catch (Exception ex){}
}