SQL Server: Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)"

Question

I am trying to run the following query:

SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0',
'Excel 12.0;Database=C:\Somefile.xlsx',
'SELECT * FROM [Sheet$]')

But I get this error:

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".

I tried the following:

sp_configure 'show advanced options', 1;
RECONFIGURE;
sp_configure 'Ad Hoc Distributed Queries', 1;
RECONFIGURE;
GO

And:

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

When I go to 'Server Objects' -> 'Linked Servers' -> 'Providers', Microsoft.ACE.OLEDB.12.0 is listed.

Acess Database Engine x64 is installed, all Office products are 64-bit, and my SQL Server is also 64 bit.

If it matters, all users have access to the Temp folder in the Users folder.

(These are all suggestions I found in similar answers to this question)

Edit: Using SQL Server 2014.

Answer 1

Make sure you close the excel spreadsheet and run SSMS as admin.