By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,934 Members | 1,527 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,934 IT Pros & Developers. It's quick & easy.

Linked Server Headache (Access)

P: n/a

There seems to be a lot of confusion around the groups about linking
to an Access mdb with the SQL Server Jet OLE DB provider and I havent
been able to find a straight forward solution. Basically, I have an
Access MDB (A2K) on one server and a SQL Server DB (2005 std ed.) on
another - Both on the same network. I'm trying to create a linked
server object in the SQL server to view data in the mdb. I've set it
up and it works - but only from the server machine itself. If you try
to connect the the linked server from any other computer on the
network you get the usual access denied / file in use error.

The fact that I can use the linked server from the server box itself
but not from another pc on the network makes me think that it may be a
permissions problem. But I am logging in with full Admin rights and
still no joy. Also there is no workgroup security on the mdb, so thats
not the problem. I've used the surface editor to remove any
restrictions that may cause problems, OLE DB connect, OPENROWSET etc.
but still no joy.

I've tried mapping the mdb's location on the server so I could use a
standard filepath, rather than a //Server-Name/... path. Again, works
from the server, but not from any client PCs, so no joy there either.
In frustration, I copied the mdb to the same server and viola - full
access to the linked server from anywhere. But this is no good, I need
the mdb file to stay where it is. An mdb full of linked tables wont
work... they don't show up in the linked server.

So that's it - out of ideas! Am I just going to have to accept that
linked server objects are limited just to mdb files on the same server
machine, or is there something I'm missing??? Firewalls, service
logins, server settings.... something one of you gurus out there know
about that might be the key to making my headache go away!!!

Any input gratefully recieved!!!

Aug 13 '07 #1
Share this Question
Share on Google+
1 Reply

P: n/a
OK, bloody typical - I waste the best part of a day and half on theis
problem, and when I post to look for help I find the solution myself 2
mins later anyway:

In case anyone else is being crippled with this, here's the answer:

Maybe it will help someone some day ;)

Aug 13 '07 #2

This discussion thread is closed

Replies have been disabled for this discussion.