The ‘Microsoft.ACE.OLEDB.12.0’ provider is not registered on the local machine. (System.Data)

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)

sql server 2008 import excel xlsx

 

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: …”

sql server 2008 import excel xlsx 32 bit access database engine 64 bit office

 

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.

 

Leave a Reply