When you try to import Excel 2007 or later “.xlsx” files into an SQL Server 2008 database you may get the following error mesage :
The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. (System.Data)
To solve this problem install the appropriate version of the Microsoft Access Database Engine.
On 32 bit Windows
On the 32 bit version of Windows Server 2003 download and install the 32 bit version of Microsoft Access Database Engine 2010 Redistributable from
http://www.microsoft.com/en-us/download/details.aspx?id=13255
On 64 bit Windows
On the 64 bit versions of Windows 7 and Windows Server 2008 download and install the 64 bit version of
Microsoft Access Database Engine 2010 Redistributable from
http://www.microsoft.com/en-us/download/details.aspx?id=13255
and the
2007 Office System Driver: Data Connectivity Components from
http://www.microsoft.com/download/en/details.aspx?id=23734
You may also have to execute the following lines in the SQL Server Management Studio
- Open a new query window
- Select the master database
- Execute the following lines
sp_configure ‘show advanced options’, 1;
RECONFIGURE;
sp_configure ‘Ad Hoc Distributed Queries’, 1;
RECONFIGURE;
GO
Why do we need to install the old 32 bit driver on the 64 bit operating system?
SQL Server Management Studio is a 32 bit application, most likely it can only use the 32 bit driver on the 64 bit operating system.
If you try to install the 32 bit version of the newer Microsoft Access Database Engine 2010 Redistributable and you already have the 64 bit version of Microsoft Office installed on your 64 bit computer you will get the following error message:
“You cannot install the 32-bit version of Microsoft Access Database Engine 2010 because you currently have 64-bit Office products installed. If you want to install 32-bit Microsoft Access Database Engine 2010, you will first need to remove the 64-bit installation of Office products. After uninstalling the following product(s), rerun setup in order to install 32-bit version of Microsoft Access Database Engine 2010: …”
The old 32 bit 2007 Office System Driver can be installed even if the 64 bit version of Microsoft Office 2013 is installed on the machine.