I am looking for a way to link an old access database on a network drive to
a SQL server. Can someone post some examples of how they might do this? I
have read where I could import the tables from access and then setup the
linked tables in access but this seems backwards although I am sure that it
would work.

EXEC sp_addlinkedserver 'LinedServerName', 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0', 'Full path and access database name'

--
Simon Worth


"Dave S." <davidstedman (AT) colliergov (DOT) net> wrote

Thanks! This did indeed create the server, but when trying to access the
tables I get an authentication error.


"Simon Worth" <REMOVEFIRST_simon.worth (AT) gmail (DOT) com> wrote

Take a look at sp_addlinkedsrvlogin in BOL

--
Simon Worth


"Dave S." <davidstedman (AT) colliergov (DOT) net> wrote

Forgive my ignorance here, but I have no idea why authentication is even a
problem since I am only trying to look at the tables from enterprise manager
as the administrator, the network drive can be seen as this same user, and
there is no security at all on the access database I am linked to. I have
several linked SQL servers and have zero problems with authentication.


"Simon Worth" <REMOVEFIRST_simon.worth (AT) gmail (DOT) com> wrote

I'm not sure why you are encountering the error. I linked an access
database to my server just to try it, and I didn't have to set up any
special permissions for it. So I'm not sure.

--
Simon Worth


"Dave S." <davidstedman (AT) colliergov (DOT) net> wrote

Maybe because the access db is on a network drive? Who knows....


"Simon Worth" <REMOVEFIRST_simon.worth (AT) gmail (DOT) com> wrote

I tried it on an access db on the network and didn't get the error.
What are the settings for the linked server in the security tab?

--
Simon Worth


"Dave S." <davidstedman (AT) colliergov (DOT) net> wrote

Ok, if I use enterprise mgr on the SQL server itself I can see the tables,
but not from a remote computer which I am sure will effect my ability to
query it as well....

Right now its clear of everything although I have tried several combos. How
is yours set?


"Simon Worth" <REMOVEFIRST_simon.worth (AT) gmail (DOT) com> wrote

Mines set up with the defaults.
No "Local server login to remote server login mappings"
and "Be made using the login's current security context" is set for not
defined logins

if you try to query it from QA using syntax
select * from [Linked Server name]...[Table Name] do you get any results?

--
Simon Worth


"Dave S." <davidstedman (AT) colliergov (DOT) net> wrote