[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

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

Posted on 2006-11-17
7
Medium Priority
?
547 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
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…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

656 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