The 'Microsoft.ACE.OLEDB.15.0' provider is not registered on the local machine. (System.Data) When Trying to Import Excel 2013 file into SSMS 18

Question

I am getting the error in SSMS 18 when trying to import an excel file to a database:

The operation could not be completed.

ADDITIONAL INFORMATION:

The 'Microsoft.ACE.OLEDB.15.0' provider is not registered on the local machine. (System.Data)

When googling around for solutions, everyone seemed to have conflicting versions (64 vs. 32) but everything I am using is 64-bit.

System details:

• Windows 10 x64-bit
• Microsoft SQL Server Management Studio 18 (Standard Edition (64-bit))
• Microsoft Excel 2013 64-bit

Answer 1

To check what 64-bit ACE provider is installed on the machine, just execute the following command in SSMS:

T-SQL

EXEC master.sys.sp_MSset_oledb_prop;

You can see its screen shot below.

Overall, there are 3 existing versions of ACE: 12.0, 15.0, and 16.0 You can change a misleading Excel version: dropdown to one of them, i.e to whatever version is installed.

If you need to install any of the ACE providers:

• Microsoft Access Database Engine 2010 (Microsoft.ACE.OLEDB.12.0) https://www.microsoft.com/en-us/download/details.aspx?id=13255
• Microsoft Access 2013 Runtime (Microsoft.ACE.OLEDB.15.0) https://www.microsoft.com/en-us/download/details.aspx?id=39358
• Microsoft Access Database Engine 2016 (Microsoft.ACE.OLEDB.16.0) https://www.microsoft.com/en-us/download/details.aspx?id=54920

UPDATE

To update ACE properties please issue the following commands:

USE [master] 
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
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DisallowAdHocAccess', 0
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'AllowInProcess', 1 
GO 
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'DynamicParameters', 1 
GO
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'DisallowAdHocAccess', 0
GO

Answer 2

I had this same issue but I resolved it by installing 'Microsoft Access Database Engine 2010 Redistributable'

Make sure you download the 32-bit version if you're running 32-bit version of Microsoft SQL Server Management Studio or 64-bit version if you're running 64-bit version of Microsoft SQL Server Management Studio

You can check which version of Microsoft SQL Server Management Studio you're running by:

1. Run the application
2. Open up task manager and you'll see the version you're currently running

After all installation, Reboot your PC