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