Accessing Excel files on a x64 machine

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 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

2) Install AccessDatabaseEngine_X64.exe on your SQL Server machine

Read more: A Support Engineer's Guide to the "Data Access"

Posted via email from jasper22's posterous