Get data from several Access and SQL databases

[FesterSXS]

Hi,

I have also posted this question in the ASP forum (forum333)

I would like to create a recordset built from data from a number of SQL Server 2000 databases and also several Access databases. All databases are running on the same server.

To put my problem into context, I am trying to show news articles on our International website. I would like to query the database of each of our National websites for any news articles that they have. I would then like to order all of these news articles by date and show them on one page (ASP). Some of these sites use Access DBs and some use SQL Server.

The News tables in the various Access databases all have the same structure and likewise, the News tables in the various SQL Server databases all have the same structure.

Anyone have any ideas?

Thanks

Tony
---------------------------------------

http://www.SpiraxSarco.com/

 

[sblocher]

One easy option: Setup the ACCESS files as linked servers , then use a set of queries to get the data. For example, if you have a linked server called MDB1 you could use either of these queries to get the data from a table called XYZ:

select * from openquery(MDB1,'select * from XYZ')
select * from MDB1...XYZ

If your in SQL 2005, you can also do
EXECUTE ('select * from XYZ') at MDB1

If you want to use variable names for the server and tables, then you need to do something like:
SET @mdblinkeservername = 'MDB1'
SET @SQL = 'select * from ' + @mdblinkeservername' + '...XYZ'
exec (@SQL)

You can change these to INSERTS to put the results in one table so you can query it for the final set of data.

Setting up linked servers (or using OPENDATASOURCE or whatever to get to your Access data) is a subject in and of itself.

 

[Crowley16]

if you wanted, you could do the same thing in access, setup linked tables or pass through queries in access to all the different databases...

just thought I'd mention it...

--------------------
Procrastinate Now!