How do I connect to different versions of Excel from C#?

Question

I have a program that imports data from Excel into a dataset. To connect to Excel I use the following code...

return new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + 
fileName + "; Jet OLEDB:Engine Type=5;"+ "Extended Properties=\"Excel 8.0;\"");

I just got a new computer with Excel 2010 and now the connection attempt fails and throws an exception saying The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine

Is this because the new version of Excel needs a different connection string? Has anyone run into this?

EDIT: Actually I just read somewhere that using the System.Data.OleDbClient class does not require Excel to even be installed on the computer. So my problem probably has nothing to do with Excel but rather with the Microsoft.Jet tool. My computer is running Windows7 64 bit. Shouldn't that be installed already?

Answer 1

Check out the Microsoft Access Database Engine 2010 Redistributable.
Redistributables for other Excel versions also exist.

Answer 2

I find this site useful when having connection string questions.

http://www.connectionstrings.com/excel

Also take a look at a previous SO question:

http://stackoverflow.com/questions/1461053/diagnosing-an-oledb-exception-when-quering-excel-2010

Answer 3

You need to compile your program specifically for 32-bit so it will run in the WoW subsystem and invoke the 32-bit Jet provider. As mentioned above, there is no 64-bit version of the Jet provider and likely never will be.

In Visual Studio, you do this by setting your target CPU type in the project settings to x86 instead of Any or 64-bit.

If you don't have the source for your program, you can modify the .exe using the corflags.exe utility that comes with the .NET framework and the /32bit+ flag, but if it's a strongly signed assembly you'll need the .SNK to re-sign it once you've modified it.

Here is a reference on WoW64 if you need more detail: http://en.wikipedia.org/wiki/WoW64