I need to update an Access 2003 database from SQL Server 2012 with the Microsoft.ACE.OLEDB.12.0 provider. As an administrator, everything works great. I can do Select, Insert, Update and Delete statements on the Access database without any problem but when i use a non-admin user, i can only do Select queries. The Insert, Update and Delete queries doesn't work and i get the following error message:
Error message when i use a SQL Server login:
Cannot execute the query "SELECT * FROM inv_lign" against OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "AccessFileTest". The provider could not support an interface required for the UPDATE/DELETE/INSERT statements.The provider indicates that conflicts occurred with other properties or requirements.
Here's the query:
DELETE FROM OPENQUERY(AccessFileTest, 'SELECT * FROM inv_lign')
I have also tried the 4 parts query without success:
Here's how the Microsoft.ACE.OLEDB.12.0 provider is configured:
- Dynamic parameter = true
- Allow inprocess = true
- Added the key DisallowAdHocAccess = 0 in the registry
Here's how the Linked Server AccessFileTest is configured:
- In the Security tab, i have selected "Be made using this security context"
- The remote login is "admin" without any password
So how can i do Insert/Update/Delete statements on an Access 2003 database using the Microsoft.ACE.OLEDB.12.0 provider?
What i have tried so far:
I've been able to make it work ONLY if i use an AD user that is not sysadmin of SQL Server but who is Administrator of my server. If the user is not admin of the server, i get the error message
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "(null)".. I then tried to just add this user to have permission on the TEMP folder and on the Access file without success. So i don't know what specials rights i have to give to get this working with an AD User.
If i use a SQL Server login, i'm stock on the error message as stated before.