Tuesday, June 4, 2019

Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine

Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine


Error:

The error message Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine is thrown when I tried accessing the Excel file using OLEDB connection.



The issue I described in my scenario occurred basically due to the incompatibility of the Microsoft.Jet.OLEDB.4.0 driver in 64 bit OS.

Solution:

So if we are using Microsoft.Jet.OLEDB.4.0 driver in a 64 bit server, we have to force our application to build in in 32 bit mode(change the build property of the project to X86) and that causes other part of my code to break.

Fortunately, now Microsoft has released a 64 bit compatible 2010 Office System Driver which can be used as replacement for the traditional Microsoft.Jet.OLEDB.4.0 driver. It works both in 32 bit as well as 64 bit servers. I have used it for Excel file manipulation and it worked fine for me in both the environments.

You can download this driver from Microsoft Access Database Engine 2010 Redistributable - 64-bit compatible 2010 office system driver

If the file extension is xls and OS is 32 bit then only you can use "Microsoft.Jet.OLEDB.4.0". Microsoft has not released 64 bit version of this driver.

If file extension is xlsx or OS is 64 bit then you must have to use "Microsoft.ACE.OLEDB.12.0". The application compiled in 32/64 bit mode does not impact the selection of driver.

Always install the 64 bit driver of Microsoft.ACE.OLEDB.12.0 on OS 64 bit. If you have already installed Office 32 bit then you need to run driver from cmd with /passive argument. This hack works till Office 2013 only, Microsoft stopped this workaround from Office 2016 for Microsoft.ACE.OLEDB.16.0 drivers.


private void ProcessFile(string path)
{
    string connString = string.Empty;

    if (Path.GetExtension(path).ToLower().Trim() == ".xls" && Environment.Is64BitOperatingSystem == false)
        connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
    else
        connString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}



There is indeed no 64 bit version of Jet - and no plans (apparently) to produce one.



1 comment:

  1. Thank you for sharing such a valuable post. Explore this profile for more insightful content Mental Age Test. Mental age tests are an intriguing tool for self-discovery.

    ReplyDelete