Compact Access 2007 Database using JRO / DAO

In one of my winform project, we are using Sql Server as well as Microsoft Access 97 as Database. And since size of MS Access DB file grows, at some point of time we Compact the Access Database file through coding by .Net. Today, I am sharing my experience on using different techniques to compact Access DB file using .Net.

We are using Microsoft Jet and Replication Objects 2.6 Library (JRO) to compact Access 2007 Database. But during last week we found two problems in using JRO:

1. JRO was NOT working on 64 bit Computer. I tested on Windows Server 2008 R2 and it fail to Load. (JRO might work on 64 bit if you have MS Office installed)
2. When JRO compacts DB (on XP 32bit), database file created by compaction is of Access 2002 - 2003 File format. So if your Original file is of 2007 Format then after compaction by JRO, DB File is converted to 2002 - 2003 File format.

For solving above 2 problems, I have then used DAO to compact database, which is more accurate and also worked on 64bit Server.

Here, I am giving steps to implement Compaction using JRO and DAO both.

Steps to compact Access DB using JRO:

App.config file:
<?xml version="1.0"?>
<configuration>
<appSettings>
<add key="SourceDB" value="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Test\Test2007.accdb;Jet OLEDB:Engine Type=5"/>
<add key="DestDB" value="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Test\Test2007BK.accdb;Jet OLEDB:Engine Type=5"/>
</appSettings>
</configuration>


1. Create a winform application and add a reference of Jet and Replication Objects 2.6 Library. See the diagram below:

2. Add form to solution and add below code in form Load event:

string SrcDBName = ConfigurationManager.AppSettings["SourceDB"];
string DestDBName = ConfigurationManager.AppSettings["DestDB"];

        int ReturnCode = 0;
        JRO.JetEngine objJRO = null;
        try
        {
            objJRO = new JRO.JetEngine();
            objJRO.CompactDatabase(SrcDBName, DestDBName);
        }
        catch (Exception ex)
        {
            StackTrace STrace = new StackTrace(ex, true);
            StackFrame StkFrame = STrace.GetFrame(STrace.FrameCount - 1);
            string Disp_Msg = "Message:\t" + ex.Message + Environment.NewLine;
            Disp_Msg += "Error Date:\t" + DateTime.Now.ToString("dddd, MMM d yyyy HH:mm:ss");

            File.AppendAllText(Path.GetDirectoryName(Application.ExecutablePath) + @"\CompactErr.txt", Disp_Msg + Environment.NewLine + "Stack Trace:\t" + ex.StackTrace + Environment.NewLine + Environment.NewLine);
        }
        finally
        {
            Marshal.ReleaseComObject(objJRO);
            objJRO = null;
        }

3. Most Important: change the Target Platform to x86 (From Project property window -> Click on Build tab and then change Platform Target to x86.

4. Run the application and will create new compacted Access DB file. But as I mention earlier its format will always be 2002-2003 format.

Steps using DAO:

1. Create a winform application and Add a reference of DAO - Microsoft Office 12.0 Access Database Engine Object Library, and the DLL is ACEDAO.DLL. See the diagram below:

2. Add form to solution and add below code in form Load event:

using AccInterop = Microsoft.Office.Interop.Access;

AccInterop.Dao.DBEngine objDBEngine = null;

string SrcDBName = ConfigurationManager.AppSettings["SourceDB"];
string DestDBName = ConfigurationManager.AppSettings["DestDB"];
objDBEngine = new AccInterop.Dao.DBEngine();
objDBEngine.CompactDatabase(SrcDBName, DestDBName);

3. You can keep target platform to "Any CPU" or x86. If you keep Any CPU then you need to install MS Access Database Engine 2010 - x64 version. And if you keep x86 then you need to install MS Access Database Engine 2010 - x86 version.

4. Run the application and will create new compacted Access DB file. Format of compacted file will be same as Original file format.

5. we need to install 2 components for DAO to work correctly.

5.1. Microsoft Access Database Engine 2010 (x86 or x64).
Link from MSDN to download: http://www.microsoft.com/download/en/details.aspx?id=13255

5.2. Microsoft Office 2010: Primary Interop Assemblies.
Link from MSDN to download: http://www.microsoft.com/download/en/details.aspx?id=3508

If you have any queries, you can write comments and I will be glad to reply you.

Happy Programming !!!