creating a linked server

I am trying to use linked servers as a means of accessing files in an Access
database. I have unsuccessfully tried 2 methods:
1) using sp_addlinkedserver. I am told that I cannot create a linked server
for Access tables, unless the Access file exists on the same server as SQL.
This seems odd to me. Is this true? Can I create a linked server for an
.mdb file that exists on a different server? And can I do this using
sp_addlinkedserver?
2) using OPENROWSET. I get an "Authentication failed" error message with
the following:
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'c:\temp\z.mdb';'';'', Untitled)
Do I need to specify some kind of security? What am I missing?
My knowledge on security issues is very limited. Any help on getting me in
the right direction would be appreciated. I am using SQL 8.00.194
Thanks
DionI would recommend looking at this thread in the
microsoft.public.sqlserver.dts newsgroup with subject "Access and SQL". We
just reviewed it there.
--
Simon Worth
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> I am trying to use linked servers as a means of accessing files in an
Access
> database. I have unsuccessfully tried 2 methods:
> 1) using sp_addlinkedserver. I am told that I cannot create a linked
server
> for Access tables, unless the Access file exists on the same server as
SQL.
> This seems odd to me. Is this true? Can I create a linked server for an
> .mdb file that exists on a different server? And can I do this using
> sp_addlinkedserver?
> 2) using OPENROWSET. I get an "Authentication failed" error message with
> the following:
> SELECT *
> FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> 'c:\temp\z.mdb';'';'', Untitled)
> Do I need to specify some kind of security? What am I missing?
> My knowledge on security issues is very limited. Any help on getting me
in
> the right direction would be appreciated. I am using SQL 8.00.194
> Thanks
> Dion|||So the Access file needs to be either on the local drive, or on the same
server as sql, right? Thanks much for the thread.
Dion
"Simon Worth" wrote:
> I would recommend looking at this thread in the
> microsoft.public.sqlserver.dts newsgroup with subject "Access and SQL". We
> just reviewed it there.
> --
> Simon Worth
>
> "Dion" <Dion@.discussions.microsoft.com> wrote in message
> news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> > I am trying to use linked servers as a means of accessing files in an
> Access
> > database. I have unsuccessfully tried 2 methods:
> > 1) using sp_addlinkedserver. I am told that I cannot create a linked
> server
> > for Access tables, unless the Access file exists on the same server as
> SQL.
> > This seems odd to me. Is this true? Can I create a linked server for an
> > .mdb file that exists on a different server? And can I do this using
> > sp_addlinkedserver?
> > 2) using OPENROWSET. I get an "Authentication failed" error message with
> > the following:
> > SELECT *
> > FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> > 'c:\temp\z.mdb';'';'', Untitled)
> > Do I need to specify some kind of security? What am I missing?
> >
> > My knowledge on security issues is very limited. Any help on getting me
> in
> > the right direction would be appreciated. I am using SQL 8.00.194
> >
> > Thanks
> > Dion
>
>|||The .mdb database file must reside on the server. data_source is evaluated
on the server, not the client, and the path must be valid on the server.
I didn't try it with the access db on the local drive. I assume that won't
work, as it's counter to what BOL says. But try it out, and see if it
works.
--
Simon Worth
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:BDB66770-AD50-4EB9-8681-9F9427871584@.microsoft.com...
> So the Access file needs to be either on the local drive, or on the same
> server as sql, right? Thanks much for the thread.
> Dion
>
> "Simon Worth" wrote:
> > I would recommend looking at this thread in the
> > microsoft.public.sqlserver.dts newsgroup with subject "Access and SQL".
We
> > just reviewed it there.
> > --
> > Simon Worth
> >
> >
> > "Dion" <Dion@.discussions.microsoft.com> wrote in message
> > news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> > > I am trying to use linked servers as a means of accessing files in an
> > Access
> > > database. I have unsuccessfully tried 2 methods:
> > > 1) using sp_addlinkedserver. I am told that I cannot create a linked
> > server
> > > for Access tables, unless the Access file exists on the same server as
> > SQL.
> > > This seems odd to me. Is this true? Can I create a linked server
for an
> > > .mdb file that exists on a different server? And can I do this using
> > > sp_addlinkedserver?
> > > 2) using OPENROWSET. I get an "Authentication failed" error message
with
> > > the following:
> > > SELECT *
> > > FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> > > 'c:\temp\z.mdb';'';'', Untitled)
> > > Do I need to specify some kind of security? What am I missing?
> > >
> > > My knowledge on security issues is very limited. Any help on getting
me
> > in
> > > the right direction would be appreciated. I am using SQL 8.00.194
> > >
> > > Thanks
> > > Dion
> >
> >
> >|||Sorry, Simon, but if I can ask one last question (my hardware knowledge is
pretty limited). When you say "database file must reside on the server,"
you mean the Access file needs to be on the SAME server as SQL, right? In
other words, my probelm is I am trying to access one server from another
server.
Thanks
Rick
"Simon Worth" wrote:
> The .mdb database file must reside on the server. data_source is evaluated
> on the server, not the client, and the path must be valid on the server.
> I didn't try it with the access db on the local drive. I assume that won't
> work, as it's counter to what BOL says. But try it out, and see if it
> works.
> --
> Simon Worth
>
> "Dion" <Dion@.discussions.microsoft.com> wrote in message
> news:BDB66770-AD50-4EB9-8681-9F9427871584@.microsoft.com...
> > So the Access file needs to be either on the local drive, or on the same
> > server as sql, right? Thanks much for the thread.
> >
> > Dion
> >
> >
> > "Simon Worth" wrote:
> >
> > > I would recommend looking at this thread in the
> > > microsoft.public.sqlserver.dts newsgroup with subject "Access and SQL".
> We
> > > just reviewed it there.
> > > --
> > > Simon Worth
> > >
> > >
> > > "Dion" <Dion@.discussions.microsoft.com> wrote in message
> > > news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> > > > I am trying to use linked servers as a means of accessing files in an
> > > Access
> > > > database. I have unsuccessfully tried 2 methods:
> > > > 1) using sp_addlinkedserver. I am told that I cannot create a linked
> > > server
> > > > for Access tables, unless the Access file exists on the same server as
> > > SQL.
> > > > This seems odd to me. Is this true? Can I create a linked server
> for an
> > > > .mdb file that exists on a different server? And can I do this using
> > > > sp_addlinkedserver?
> > > > 2) using OPENROWSET. I get an "Authentication failed" error message
> with
> > > > the following:
> > > > SELECT *
> > > > FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> > > > 'c:\temp\z.mdb';'';'', Untitled)
> > > > Do I need to specify some kind of security? What am I missing?
> > > >
> > > > My knowledge on security issues is very limited. Any help on getting
> me
> > > in
> > > > the right direction would be appreciated. I am using SQL 8.00.194
> > > >
> > > > Thanks
> > > > Dion
> > >
> > >
> > >
>
>|||So here's the thing.
You can add an Access DB .mdb file to SQL Server as a linked server. You
can do so if the file is on a network share somewhere. However (and this
may not always be the case - I have limited knowledge about access linked
servers) you will only be able to query the linked access db server from the
server itself if it's on a network share. If you want to connect to the
access linked server from another sql server - the access db must reside on
a local drive of the server you added the link to.
example
I have a desktop computer and I'm running SQL Server 2000 dev edition.
I have an access db on a network drive (\\prodserv1\accessdbs\MyDB.mdb)
I can add \\prodserv1\accessdbs\MyDB.mdb to my desktop computer running sql
server, and can query it from Query analyzer.
But, if I log into my desktop computer running SQL Server from another
desktop computer using Query Analyzer, I cannot query the linked access
server anymore (select * from LinkedServerName...TableName). I get an error
message.
Now, if I copy \\prodserv1\accessdbs\MyDB.mdb to C:\Temp\MyDB.mdb, and
change the data source in the linked server from
\\prodserv1\accessdbs\MyDB.mdb to C:\Temp\MyDB.mdb - I can then query the
linked server that I defined on my desktop computer from another desktop
computer.
--
Simon Worth
"Dion" <Dion@.discussions.microsoft.com> wrote in message
news:3769D201-5F4C-465E-A79A-0D88918F5EF7@.microsoft.com...
> Sorry, Simon, but if I can ask one last question (my hardware knowledge is
> pretty limited). When you say "database file must reside on the server,"
> you mean the Access file needs to be on the SAME server as SQL, right? In
> other words, my probelm is I am trying to access one server from another
> server.
> Thanks
> Rick
> "Simon Worth" wrote:
> > The .mdb database file must reside on the server. data_source is
evaluated
> > on the server, not the client, and the path must be valid on the server.
> >
> > I didn't try it with the access db on the local drive. I assume that
won't
> > work, as it's counter to what BOL says. But try it out, and see if it
> > works.
> >
> > --
> > Simon Worth
> >
> >
> > "Dion" <Dion@.discussions.microsoft.com> wrote in message
> > news:BDB66770-AD50-4EB9-8681-9F9427871584@.microsoft.com...
> > > So the Access file needs to be either on the local drive, or on the
same
> > > server as sql, right? Thanks much for the thread.
> > >
> > > Dion
> > >
> > >
> > > "Simon Worth" wrote:
> > >
> > > > I would recommend looking at this thread in the
> > > > microsoft.public.sqlserver.dts newsgroup with subject "Access and
SQL".
> > We
> > > > just reviewed it there.
> > > > --
> > > > Simon Worth
> > > >
> > > >
> > > > "Dion" <Dion@.discussions.microsoft.com> wrote in message
> > > > news:007EABF0-E19E-4553-9BC7-7ED71A17448A@.microsoft.com...
> > > > > I am trying to use linked servers as a means of accessing files in
an
> > > > Access
> > > > > database. I have unsuccessfully tried 2 methods:
> > > > > 1) using sp_addlinkedserver. I am told that I cannot create a
linked
> > > > server
> > > > > for Access tables, unless the Access file exists on the same
server as
> > > > SQL.
> > > > > This seems odd to me. Is this true? Can I create a linked
server
> > for an
> > > > > .mdb file that exists on a different server? And can I do this
using
> > > > > sp_addlinkedserver?
> > > > > 2) using OPENROWSET. I get an "Authentication failed" error
message
> > with
> > > > > the following:
> > > > > SELECT *
> > > > > FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
> > > > > 'c:\temp\z.mdb';'';'', Untitled)
> > > > > Do I need to specify some kind of security? What am I missing?
> > > > >
> > > > > My knowledge on security issues is very limited. Any help on
getting
> > me
> > > > in
> > > > > the right direction would be appreciated. I am using SQL 8.00.194
> > > > >
> > > > > Thanks
> > > > > Dion
> > > >
> > > >
> > > >
> >
> >
> >