Solved

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

Posted on 2006-11-17
7
544 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 500 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

728 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