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.