We've got a linked server from SQL 2016 to an accdb database file (2016, I think).
I can access the linked without any issues (I'm a sysadmin).
The user who needs to access it can't (not a sysadmin).
This is what we've tried so far:-
On the Provider section under Linked Servers
Allow In Process = 1
Dynamic Parameters = 1
On the server
MSDAINITALIZE - given permission for the group the user is in to have access on all 3 items of the security tab
I've also made sure the user has the Microsoft ACE OLE DB 12 driver installed.
He can see the linked server, but cannot see the tables within it. This means he cannot run any stored procedures taht need to access the tables.
We've been at this for 2 hours+ with lots of googling but nothing has worked.
Has anyone got any ideas?
Forgot to add the error message which is:-
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server <mylinkedserver>