1

I created a linked server from my SQL Server 2008 R2 64 bit Windows Server 2008 machine to an Access 2000 Database that resides on another server.

The Linked server was created with no trouble using SSMS on the SQL Server machine, and you can browse the tables no problem. The trouble is trying to access the linked server from my local windows machine using SSMS authenticated with my windows account.

I have sysadmin privileges, and per several suggestions seen on the web, I have added 'Full control' shared permissions to the local service and network service temp directories.

In desperation, I also copied the MDB file to the actual SQL server machine, set up a linked server and found that it works fine when querying from my local machine's SSMS.

So, in summary, SQL Server needs to link to an Access DB on another Server and I must be able to query said Access DB from my local SSMS or some other SSMS instance or application (like SSRS).

Linked Server Settings:

  • Provider = Microsoft.ACE.OLEDB.12.0
  • Product Name = Access
  • Data Source = \\ServerName\path\to\Datafile.mdb

Error Message (When Running Test Connection):

Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Server".

OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Server" returned message "The Microsoft Access database engine cannot open or write to the file '\path\to\Data.mdb'. It is already opened exclusively by another user, or you need permission to view and write its data.". (Microsoft SQL Server, Error: 7303)

I don't know if there is any other information you need, but I will be happy to provide it to any who are willing to help. If you need clarification, then please ask as well.

Thanks!

  • You likely have the Access database open in Microsoft Access with some designer Window open. Ensure the database is not opened in exclusive mode in Microsoft Access. – Max Vernon Dec 29 '15 at 22:32
  • If you SELECT SUSER_NAME() AS me when connected to the server from SSMS on your own machine does the first part show your computer name, the server's computer name, the name of your Active Directory domain, or something else? Is it different than the result you get when you do the same thing from SSMS on the server itself? – Gord Thompson Dec 29 '15 at 23:41
  • @GordThompson I get my domain from both. – SpockJenkins Dec 30 '15 at 13:10
  • @MaxVernon It may be locked as this DB is used throughout the day. But I still do not understand how I can hit the tables from the SSMS on the Server but not my local machine using the same credentials. – SpockJenkins Dec 30 '15 at 13:23
  • Being "in-use" and having an exclusive design-level lock are two different things. – Max Vernon Dec 30 '15 at 13:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.