SQL2016 to Access2016 Linked server issue

Question

SQL2016 to Access2016 Linked server issue

richardmgreen1

SSChampion

Points: 10351
More actions
April 22, 2021 at 8:18 am

#3874244
Hi all

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:-
1. 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)?
2. 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.

TIA

Richard

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 1

I've not set up a linked server to Access before, but my general checklist for things related to linked servers AND files on disk/network is:

1 - who is it connecting as?

2 - is it a LOCAL (to the server) database, a network share using UNC path or a network share using a drive letter?

My GUESS to question 1 is that it is connecting as the user making the request. So when you try and it works, it is connecting as you, when your coworker tries, it is connecting as them and they don't have access.

My GUESS to question 2 is that it is a local (to the server) database OR a network share using UNC path. My guess here is based on the fact that you can access it when running things as you. My ASSUMPTION that I am making is that you are running SSMS on your LOCAL machine and not on the server.

Now, doing a quick google on the error (7303), it sounds like the access database is in use by another user/process. I expect that is the root of your problems - Access doesn't like sharing the database file like that.

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

richardmgreen1 SSChampion Points: 10351 More actions · Answer 2

Thanks @Brian

I'll agree that the connection being made as the user running the request, but the odd bit is that the SqlServerAgent (which is also running the query) can't acces the lonked server (same error).

I've given the agent full control over the relevant folder (including the database) but I'm still having the same issue.

I've made sure the database is definitely closed (there's no lock file) but still no joy.

I'm stumped.

Mr. Brian Gale SSC-Insane Points: 24800 More actions · Answer 3

What about UNC path vs drive letter or is the file stored locally?

As another thought, are you using the 32-bit driver or the 64-bit driver?

This is the link I found for some things to try:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/5d53eb0c-bb02-4c85-bc65-b492ee83c7c0/linked-server-error-7303-for-access-2003-database

Alternately, what about if you use a different driver:

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/5d53eb0c-bb02-4c85-bc65-b492ee83c7c0/linked-server-error-7303-for-access-2003-database

The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.

richardmgreen1 SSChampion Points: 10351 More actions · Answer 4

The database s on a local server drive (drive S: from memory).

I believe I'm using the 64-bit driver, but I'll need to double-check. I'm guessing the driver is correct because I can access it (no pun intended). I think it's just a permissions issue.

I'll take a look at the links and see if there's anything I've missed.