[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 549
  • Last Modified:

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
0
Carolinat
Asked:
Carolinat
1 Solution
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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
 
badbearontourCommented:
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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now