Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.16.0" for linked server "(null)"

Question

When trying to use openrowset with SQL I receive this error:

"Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.16.0" for linked server "(null)"."

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

select *
from openrowset('Microsoft.ACE.OLEDB.16.0',
            'Excel 16.0;Database=‪C:\Users\Unixus\Desktop\teste.xlsx',
            'SELECT * FROM [teste$]') AS testeData

UPDATE teste
SET test_model = testeData.test_model
,test_pass = testeData.test_pass
FROM teste
JOIN OPENROWSET ('Microsoft.ACE.OLEDB.16.0',
            'Excel 16.0;Database=‪C:\Users\Unixus\Desktop\teste.xlsx',
            'SELECT * FROM [teste$]') AS testeData ON teste.test_ID = teste.test_ID

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

Answer 1

If you are using a *.xlsx file Excel you can try it with 'Excel 12.0 Xml' instead of 'Excel 16' and if you use a *.xls file it should work with 'Excel 8.0'.

Maybe it is also necessary to set the following OLEDB Provider Options:

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

Last but not least: if the query will be executed from a non administrative user you have to give that account read write access to the TEMP folder of the SQL-Server's service account because every time you run an openrowset query, a connection string is temporarily stored there.