?
Solved

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

Posted on 2006-11-17
7
Medium Priority
?
545 Views
Last Modified: 2008-01-09
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
Comment
Question by:Carolinat
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 7

Expert Comment

by:badbearontour
ID: 17966822
Have you checked that server1 can access the share on server2?

0
 

Author Comment

by:Carolinat
ID: 17966894
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
 

Expert Comment

by:Leifons
ID: 17970558
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 7

Expert Comment

by:TRACEYMARY
ID: 17971285
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
 
LVL 7

Expert Comment

by:badbearontour
ID: 17971963
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
 

Author Comment

by:Carolinat
ID: 17979216
Sorry for my ignorance but what is MDAC?
0
 
LVL 7

Accepted Solution

by:
badbearontour earned 1500 total points
ID: 17986195
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

Learn how to optimize MySQL for your business need

With the increasing importance of apps & networks in both business & personal interconnections, perfor. has become one of the key metrics of successful communication. This ebook is a hands-on business-case-driven guide to understanding MySQL query parameter tuning & database perf

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A company’s centralized system that manages user data, security, and distributed resources is often a focus of criminal attention. Active Directory (AD) is no exception. In truth, it’s even more likely to be targeted due to the number of companies …
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question