OLE DB provider "Microsoft.ACE.OLEDB.16.0" for linked server "(null)"

Question

Why can't I read Excel files using OLE DB in local SQL Server after installing the same version as the one on the company server (2022)? Although I have installed OLE DB, I keep getting the following error: "OLE DB provider 'Microsoft.ACE.OLEDB.16.0' for linked server '(null)' returned message 'Failure creating file'".

Execute

Error

I have tried uninstalling SQL Server, installing OLE DB, and also downloading and installing accessdatabaseengine_X64.exe. Why doesn't it work for me?

tambien eh intentado con EXEC sp_configure 'Show Advanced Options', 1 RECONFIGURE GO EXEC sp_configure 'Ad Hoc Distributed Queries', 1 RECONFIGURE GO EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'AllowInProcess', 1 GO EXEC sp_MSSet_oledb_prop N'Microsoft.ACE.OLEDB.16.0', N'DynamicParameters', 1 GO

Answer 1

Your error is as follows:

Msg 7303, Level 16, State 1, Line 41 Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.16.0" for linked server "(null)".

Please try the following steps:

• If the *.xslx file is open in Excel. Close it in Excel.
• Run SSMS as administrator.
• Give MSSQLSERVER full permission on this folder: C:\Windows\ServiceProfiles<SQL Server Service account name>\AppData\Local\Temp

On my machine account: NT Service\MSSQLSERVER

So, my temp files when I query MS Excel files:

• c:\Windows\ServiceProfiles\MSSQLSERVER\AppData\Local\Temp\JET643E.tmp
• c:\Windows\ServiceProfiles\MSSQLSERVER\AppData\Local\Temp\JET642E.tmp