I'm having the rather common problem in SQL Server trying to import data using OPENROWSET:
Msg 7302, Level 16, State 1, Line 24
Cannot create an instance of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".
I'm running SQL Server 2014 Standard, Office 365 - 2016 64bit. I've also installed the AccessDatabaseEngine_X64. Both versions 12 and 16 of the OLEDB provider are showing in the providers list in SQL Management Studio.
I'm trying to run the following:
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'Ad Hoc Distributed Queries', 1; RECONFIGURE;
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'AllowInProcess', 1
EXEC master.dbo.sp_MSset_oledb_prop N'Microsoft.ACE.OLEDB.12.0', N'DynamicParameters', 1
SELECT * FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'C:\Import2263.mdb'; 'admin';'',IMPORT);
I've added Everyone with full control over all the temp folders I can find and the source mdb file. I'm running the script as sa. I've tried running the MSSQLSERVER account as local system and as the local administrator. No luck.
The only thing I haven't tried is a clean install. The server has had 32bit office running which I uninstalled before installing 64bit. Just rather a nuclear option.
I can't see what I'm missing - any help much appreciated.