Multiple Access databases as data source

I've read already a lot of threads, dealing with several databases on
different servers as data source. They all recommend to use linked servers.
But what, if we have to deal with Access databases?Actually, most advice I've seen is NOT to use linked servers. You can
connect directly via ODBC or OLEDB to Access.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"Niklas" <Niklas@.discussions.microsoft.com> wrote in message
news:7424F35B-C52C-4104-9C6D-91AC0F439898@.microsoft.com...
> I've read already a lot of threads, dealing with several databases on
> different servers as data source. They all recommend to use linked
> servers.
> But what, if we have to deal with Access databases?|||The recommendation for linked servers is if you are trying to join two the
results from two different servers. If you want to do that you need to have
a stored procedure and use temp tables and do the join there (and use linked
servers, note you should use openquery and not the four part naming for
performance reasons). If you are not doing a join then definitely do not use
linked servers. Just set up multiple data sources (shared is best in my
opinion).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Niklas" <Niklas@.discussions.microsoft.com> wrote in message
news:7424F35B-C52C-4104-9C6D-91AC0F439898@.microsoft.com...
> I've read already a lot of threads, dealing with several databases on
> different servers as data source. They all recommend to use linked
> servers.
> But what, if we have to deal with Access databases?|||Thanks Bruce,
I have to join the results of several servers, unfortunately I've to deal
with Access ...
"Bruce L-C [MVP]" wrote:
> The recommendation for linked servers is if you are trying to join two the
> results from two different servers. If you want to do that you need to have
> a stored procedure and use temp tables and do the join there (and use linked
> servers, note you should use openquery and not the four part naming for
> performance reasons). If you are not doing a join then definitely do not use
> linked servers. Just set up multiple data sources (shared is best in my
> opinion).
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "Niklas" <Niklas@.discussions.microsoft.com> wrote in message
> news:7424F35B-C52C-4104-9C6D-91AC0F439898@.microsoft.com...
> > I've read already a lot of threads, dealing with several databases on
> > different servers as data source. They all recommend to use linked
> > servers.
> > But what, if we have to deal with Access databases?
>
>|||Here is what you can do very easily with Access. In one of your Access
databases just add a link to the table in the other database. When you do a
join Access handles the join. You are better off to use Access for this
(especially if the data is in Access databases) than to create a linked
server in SQL Server.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Niklas" <Niklas@.discussions.microsoft.com> wrote in message
news:115ED942-3D21-46B4-AE80-CA93132E4845@.microsoft.com...
> Thanks Bruce,
> I have to join the results of several servers, unfortunately I've to deal
> with Access ...
> "Bruce L-C [MVP]" wrote:
>> The recommendation for linked servers is if you are trying to join two
>> the
>> results from two different servers. If you want to do that you need to
>> have
>> a stored procedure and use temp tables and do the join there (and use
>> linked
>> servers, note you should use openquery and not the four part naming for
>> performance reasons). If you are not doing a join then definitely do not
>> use
>> linked servers. Just set up multiple data sources (shared is best in my
>> opinion).
>> --
>> Bruce Loehle-Conger
>> MVP SQL Server Reporting Services
>> "Niklas" <Niklas@.discussions.microsoft.com> wrote in message
>> news:7424F35B-C52C-4104-9C6D-91AC0F439898@.microsoft.com...
>> > I've read already a lot of threads, dealing with several databases on
>> > different servers as data source. They all recommend to use linked
>> > servers.
>> > But what, if we have to deal with Access databases?
>>