Running 64-bit Ace OLEDB driver with 32-bit Office

When 32-bit Microsoft Office is installed on 64-bit Windows, there is a problem connecting to OLEDB sources using Microsoft Jet provider from .Net applications (and probably others). A .Net application, unless otherwise instructed, runs as 64-bit on 64-bit OS's and expects a 64-bit OLEDB provider for Jet. But, since Office is 32-bit, there is no 64-bit provider, and it complains that the provider is "not registered on the local machine". Actually, there doesn't seem to exist a 64-bit Jet provider, and the recommendation is to use the replacement provider which is called ACE and is backwards compatible with Jet. You get it by installing the Microsoft Access Database Engine Redistributable (one version available here) - but: the 32-bit installation doesn't solve the problem, and the 64-bit installation refuses to install because you don't have 64-bit Office.

There are many possible solutions and workarounds on the net (of which the most frequent one is to degrade your application to running 32-bit only) but the real solution is not easy to find. You need to force the 64-bit Access engine installation to install by calling it with the "/passive" argument. Call it from the command prompt like so:

AccessDatabaseEngine_X64.exe /passive

Be careful, though, not to install the same version of the engine as your version of Office. To be more precise, if you install 64-bit Access 2010 engine when 32-bit Office 2010 is present on the system, your Office applications may start complaining. On my laptop, Microsoft Excel started showing a dialog that said "One of your object libraries (|) is missing or damaged" and then tried to install/repair some components, ending with an error saying that it doesn't have the rights to install fonts (?!). This was easily resolved by uninstalling the 64-bit Access engine and installing the 32-bit one, but afterwards my ACE driver was gone again. The winning combination was to upgrade to Office 2013 (still 32-bit) and then install the 64-bit Access 2010 engine. This also seems to work with the Office 2016 / Engine 2010 combination, but not with Office 2010 / Engine 2013... It seems that the newer engine versions are smarter and don't fall for the "/passive" trick, but I haven't tried that many combinations to be sure.

As the last step, you need to change your connection string to use ACE instead of Jet and you're done. What I usually do is have a utility component that detects the presence of drivers and uses ACE as a fallback to Jet. For Excel files, it looks something like this:

/// <summary>
/// True if ACE oleDb driver is supported. False if not. Null if not checked yet.
/// </summary>
private bool? AceOleDbSupported = null;

/// <summary>
/// Name of the Excel file to be imported
/// </summary>
public string FileName { get; set; }

/// <summary>
/// True if the excel file's first row counts as a header
/// </summary>
public bool HasHeaderRow { get; set; }

public string GetConnectionString()
{
    if (AceOleDbSupported == null)
    {
        OleDbEnumerator e = new OleDbEnumerator();
        AceOleDbSupported = e.GetElements().Rows.Cast<DataRow>().Any(dr => dr["SOURCES_NAME"] as string == "Microsoft.ACE.OLEDB.12.0");
    }
    if (AceOleDbSupported.Value)
    {
        return "Provider=Microsoft.ACE.OLEDB.12.0;"
            + "Data Source=" + FileName
            + @";Extended Properties=""Excel 8.0;IMEX=1;"
            + "HDR=" + (HasHeaderRow ? "YES" : "NO") + @";""";
    }
    else
    {
        return "Provider=Microsoft.Jet.OLEDB.4.0;"
            + "Data Source=" + FileName
            + @";Extended Properties=""Excel 8.0;"
            + "HDR=" + (HasHeaderRow ? "YES" : "NO") + @";""";
    }
}