Linked Server connecting from SQL Server to a remote MS Access is not working

Hi Guys,

I have a server that runs SQL Server 2005 and I created a linked server to access a MS Access DB. These databases were located in the same server and they were working fine. Now, for space restrictions in the Server (Server1) I need to move the MS Access DB to another server(Server 2). I moved the Access DB to server 2 but now I don't know how to create the reference in the linked server. I put the \\servername\folder\accessdb.mdb but if I run a query from the sql server it shows me an error.

OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "FLEXPROACCESS" returned message "The Microsoft Jet database engine cannot open the file '\\Server2\folder\accessdb.mdb'.  It is already opened exclusively by another user, or you need permission to view its data.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "FLEXPROACCESS".

How can I solved this?
Thanks

Carolina
CarolinatAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
badbearontourConnect With a Mentor Commented:
The MDAC is a data component used by windows to connect to a range of databases and files..

The current version is 2.8 and is available for free download from Microsoft, heres the link

http://www.microsoft.com/downloads/details.aspx?DisplayLang=en&FamilyID=6c050fe3-c795-4b7d-b037-185d0506396c

2.8 is normally installed with 2003 server so if both of your servers have the 2003 opsys you probably will not need to install the latest MDAC.

Have you tried TRACEYMARY' solution? If so what was the result?



0
 
badbearontourCommented:
Have you checked that server1 can access the share on server2?

0
 
CarolinatAuthor Commented:
Yes, Server 1 can access the share on server2. But I don't know if this needed an special permissions, what do you think?
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
LeifonsCommented:
Make sure that the user credentials used on the Linked Server object are correct.
If it doesn't work try using a
1. a Global user account (known on both machines) OR
2. setup a new local windows user account on server2, give the account proper permissions (read/write, etc.) on the MS Access DB-file, and finally, use the account on the Linked Server object on server1 to access the file.
0
 
TRACEYMARYCommented:
check that you have msdtc set up on the 2nd server......control panel....component services

If your running the queries on server1 that access server 2
then on server1 your have to set up the link to server 2

select * from [SERVER2.MYDATABASE.MYOWNER.MYTABLE]

Make sure you have same login on both servers aswell.
0
 
badbearontourCommented:
I have been looking on the MS website fort his and in their forums this problem is mentioned quite a lot... but with no solutions as of yet

The Access database works fine if on the same server, but move it to a new server and try to access it accross servers then your in trouble..

I'm wondering if the same version of MDAC is installed on both servers?
0
 
CarolinatAuthor Commented:
Sorry for my ignorance but what is MDAC?
0
All Courses

From novice to tech pro — start learning today.