Reading from Excel: Microsoft.Jet.OLEDB.4.0 error on 64 bit systems

Question

I am reading the contents of an excel sheet in my application using:

 OleDbConnection con = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties=Excel 8.0");
 _myDataSet = new DataSet();
 OleDbDataAdapter myCommand = new OleDbDataAdapter(" SELECT * FROM [" + "My Sheet" + "$]", con);

myCommand.Fill(_myDataSet);
con.Close();

This is failing in 64 bit systems with the error:

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

How to make this work in 64 bit machines?

Answer 1

Microsoft released a driver distribution with a 64-bit driver that works for Access and Excel. You can download both the 64-bit and 32-bit versions from the MS downloads site. The download page also has a brief outline of what you would need to change in your connection string to reference the ACE driver.

In a nutshell, you install the 64-bit driver distribution and then change your connection string to something along the lines of:

string connStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties=""Excel 8.0;IMEX=1""";