In the old times while all the CPUs were 32bit, we were happily using JET OLEDB Provider reaching Excel or MDB files for long time without any issues.
After we started using x64 CPUs and x64 Windows machines, we noticed that JET OLEDB Provider is not working. The reason for this was x64 Windows operating systems were not containing x64 bit JET OLEDB Provider but they had 32bit JET OLEDB Provider. We needed to recompile our applications as 32bit by changing the "Target CPU" as x86 in our Visual Studio Projects (remember that default "Target CPU" fro a Visual Studio Project is "Any CPU"), or using a 32bit application pool for a web application just to be able host our app in a 32bit w3wp.exe.
Now we have a new guy in the town. Let me introduce it : "Microsoft ACE OLEDB Provider". It's "ProgID" (in terms of COM/OLEDB) is "Microsoft.ACE.OLEDB.12". It does not come within the OS, you should install this manually by downloading it from here . The name of the download is "Microsoft Access Database Engine 2010 Redistributable" as this 64bit ACE OLEDB Provider is the result of our Office 2010. It has been around in the scene with Office 2007 but it was available as 32bit only.
With the Office 2010, we have 64bit ACE OLEDB Provider which is good news :)
Here are some cases with the details how to use this new OLEDB Provider :
CASE 1: Retrieving data from an Excel file in SQL Server
Please follow the steps below :
sp_configure 'show advanced options', 1
RECONFIGURE WITH OverRide
sp_configure 'Ad Hoc Distributed Queries', 1
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1
select * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0;Database=C:\temp\test.xls', [Sheet1$])
Yuppe I got the data from XLS :)
CASE 2: Retrieving data from an Excel file in a .NET app
OleDbConnection conn = new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\\temp\\test.xls;Extended Properties=\"Excel 12.0;HDR=YES;\"");
P.S. 1 : If you are going to deploy this app to a machine without ACE OLEDB Provider, don't forget that you should install "Microsoft Access Database Engine 2010 Redistributable" to the target machine.
P.S. 2 : If you have Office 2010 32bit is installed on the machine you cannot install "Microsoft Access Database Engine 2010 Redistributable" 64bit .