We've got an issue with a linked server to an Access database.
The general gist is we need the Access database for a file submission that we have to make.
The linked server itself is fine but, when I try and write data to it via code, I get the following error:-
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MATERNITYDATASETV2". [SQLSTATE 42000] (Error 7303) OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MATERNITYDATASETV2" returned message "Unspecified error". [SQLSTATE 01000] (Error 7412).
The job itself is rnning under the SQLAgent account (and I've just given that account full access to the folder that contains the Access database.
I've also made sure the Access Database Engine (2010) is also installed but that doesn't seem to have made a difference.
I'm logged in as a sysadmin and I can access the linked server without any issues.
Another user who also needs to use the linked server cannot see the tables (not a sysadmin). I'm guessing the issues are related.
Can someone please hep with the following:-
- How can I allow the SQLAgent account access to write to the Access database ready for the file submission (the rest of the process is automated)?
- What permissions would I need to give my user in order to see the tables within the linked server (my user is getting the same error(s) as the SQLAgent)?
Any help on this would be greatly appreciated.