connecting 2 tables in 2 files

[jimot]

Hello all..

I'm trying to connect 2 tables (using INNER JOIN statement) in 2 files using ADO.

For example, I want to join table DATA1 in A.mdb with table DATA2 in B.mdb.

I have made 2 ADO connections:
con1 for A.mdb
con2 for B.mdb

I have done this:
DIM rst as NEW.ADODB.RECORDSET

rst.Open "SELECT data1.custID, data2.orderID FROM data1 inner join data2 on data1.custID = data2.custID", {active connection}
how do I set the active connection for the recordset?


thank you very much

[Leather]

As far as I know there is know way to open a recordset accross 2 connections...

However you can link to access db's together..

Open one of the databases in Access,
Goto File, Get External Data, Link Tables...

Follow the wizard through and you will ber able to link this database to tables in the other one.

Once this is done you can query this database for both tables, and perform the joins that you need...

Hope this helps

[jimot]

thanks, but how do I make the ADODB connection? I mean, what should I put in the ADODB connection string that points to 2 files?
Thanks

[Leather]

You dont have to connect to both files, thats the whole point...

Link one file to another, as described... Then just connect to the linked file..

[jimot]

OK, but if I link a.mdb and b.mdb, which file should I put in the ADO connection string? Or does the linked files creat another file?
thanks very much for your help

[Leather]

Link to whichever one you did the linked table thing in... All the tables of both db's will apear in here..

[jimot]

all right! thank you leather