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 :

  • 1) Download our 64bit version (AccessDatabaseEngine_X64.exe) of our "Microsoft Access Database Engine 2010 Redistributable" from the URL below :

http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en&WT.mc_id=soc-n-[TR]-loc-[Services]-[farukc]

  • 2) Install AccessDatabaseEngine_X64.exe on your SQL Server machine

 

  • 3) Open a new "Query Window" in SQL Server Management Studio (SSMS) after connecting to your SQL Server Engine and issue the T-SQL commands below :

 

USE [master]

GO

 

sp_configure 'show advanced options', 1

GO

RECONFIGURE WITH OverRide

GO

sp_configure 'Ad Hoc Distributed Queries', 1

GO

RECONFIGURE WITH OverRide

GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'AllowInProcess' , 1

GO

EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0' , N'DynamicParameters' , 1

GO

  • 4) Now try executing the query below :

 

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

  • 1) Download our 64bit version (AccessDatabaseEngine_X64.exe) of our "Microsoft Access Database Engine 2010 Redistributable" from the URL below :

http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en

  • 2) Install AccessDatabaseEngine_X64.exe on your x64 machine  (Likely that it will be your web server which will be hosting your ASP.NET app in a 64bit app pool or the server that you will run your .NET Windows Service as 64bit etc.)

 

  • 3) Use the System.Data.OleDBConnection with the connection string below :

 

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 .