Compact Access 2007 Database on 64bit windows 2008 Server

Question

I am facing problem while using JRO in winform application to compact access database on windows server 2008 R2 (64bit) server. I followed below steps:

Configuration of Development PC:
OS : Windows XP Professional Ver: 2002 (SP3) 32Bit
MSOffice 2003 Installed: Yes
Visual Studio: 2010 Premium
.Net framework: 4.0.30319

Two winform App with below code are created:

Application 1: For compacting Access 2007 DB

Target Platform is set to x86 and Added reference of Microsoft Jet and Replication Objects 2.6 Library

Config file:

<?xml version="1.0"?>
<configuration>
<startup><supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0,Profile=Client"/></startup>
<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"/>
<add key="AppDB" value="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Test\Test2007.accdb;" />
</appSettings> 
</configuration>

Code:

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

        int ReturnCode = 0;
        JRO.JetEngine objJRO = null;
        try
        {
            MessageBox.Show("Start Compact");
            objJRO = new JRO.JetEngine();
            objJRO.CompactDatabase(SrcDBName, DestDBName);
            MessageBox.Show("End Compact");
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error in Compact");
            ReturnCode = -1;
            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");

            //MessageBox.Show(Disp_Msg, "Compact Utility", MessageBoxButtons.OK, MessageBoxIcon.Error);
            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;
        }

Application 2: For Testing Connection with Access 2007

private void Form1_Load(object sender, EventArgs e)
    {
        try
        {
            //Connection Test
            MessageBox.Show("Start DBConn");
            TestConn();
            MessageBox.Show("End DBConn");
        }
        catch (Exception ex)
        {
            MessageBox.Show("Error in Conn Opening");
            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);
        }
    }

    public void TestConn()
    {
        string strConnectionString = ConfigurationManager.AppSettings["AppDB"];
        DbConnection objConnection;
        DbProviderFactory objFactory = OleDbFactory.Instance;

        objConnection = objFactory.CreateConnection();
        objConnection.ConnectionString = strConnectionString;
        objConnection.Open();
        objConnection.Close();
    }

Now above 2 exe are deployed on Win2008 Server:

Server Details:

Windows Server 2008 R2 (SP1) - 64bit
NO MSOffice
NO Visual Studio
Installed: Microsoft Office Access Database Engine 2010

Case 1: when Access DB Engine 2010 (64bit) is installed:
Link: http://www.microsoft.com/download/en/details.aspx?id=13255

Connection to Access 2007 (Test2007.accdb) is CORRECTLY DONE
Compact of DB using JRO NOT WORKED
Error Message while executing Step 2:

Message:Class not registered
Stack Trace:at JRO.IJetEngine.CompactDatabase(String SourceConnection, String Destconnection) at CompactUtility.Program.Main(String[] args)

Case 2: when Access DB Engine 2007 is installed:
Link: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=23734

Connection to Access 2007 (Test2007.accdb) is NOT DONE
Compact of DB using JRO CORRECTLY WORKED
Error Message while executing Step 1:
Message: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

Any Suggestion/help will be highly appreciated to solve both working on windows server 2008 (64bit) server.

Also refereed below links but not helpful:

Microsoft.ACE.OLEDB.12.0 provider is not registered

Thanks, Shah

Zoba Zoba · Accepted Answer · 2014-01-20T08:32:51

I know, it's an old question but still, This link did help me with the second issue. If i remember correctly, the problem stems from a 64bit issue with the engine. Adding the Component into Visual Studio should fix this behaviour.

Compact Access 2007 Database on 64bit windows 2008 Server

1 Answers