A user is trying to load data from an EXCEL file using a custom built dataloader to SQL server 2008 database table and this is the error he is getting:
Ad hoc access to OLE DB provider 'Microsoft.ACE.OLEDB.12.0' has been denied. You must access this provider through a linked server.
This is what I did:
ran this on the destination DB server:
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
and I checked to see if AccessDatabaseEngine_x64 is installed there and it is already there.
I am sure this happened before and I solved it by doing the above. I am not sure why it is not working now.
Can anyone spare any thoughts?