Merging two och more recordsets

[PO]

Hi,

I want to join data from 2 different databases in one table and then put the
merged data in a spreadsheet.

I use 2 recordsets to get the data:

RS1 contains the following data: SELECT CustomerID, Name, Description FROM
Table X (in DB1)
RS2 contains the following data: SELECT CustomerID, Adress, Description FROM
Table Y (in DB2)

I now want to join RS1 with RS2 and put the result in RS3.

I suspect you could do something like this(?):

RS3.RecordSource=
SELECT
RS1!Name, RS2!Adress
FROM
RS1, RS2
WHERE
RS1.CustomerID=RS2.CustomerID

but I don´t know exacly how to do this.

Kind regards!

Peter Osswald

 

[Tom Ogilvy]

You need to do the join in the sql statement that creates the records set -
rather than creating two recordsets, create one recordset doing the join in
that sql statement.

 

[PO]

1. Is it possible to join two tables from two different databases (one
Oracle database and one MS Access database) in one singel select statement??

2. Is it imposible to accomplish what I discribed below, that is, joining
two recordsets into one?

Regards,
Peter O

 

[keepitcool]

PO,

can you create a linked table in either one of the main databases?
then you can use 1 sql engine to do the work for you.

i dont see it happening the way you describe.


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >

 

[Tom Ogilvy]

No, you couldn't do the single SQL since the recordsets belong to different
parents.

I don't know ADO well enough to say whether you could put each result in a
cursor and treat the cursor as two tables that could be joined, producing a
third recordset.

But, you could put each rs on a worksheet, then built a combined table/join
using the Vlookup function - if your looking for an immediate solution.