DAO in Excel and multiple recordsets

[petehilljnr]

I have 2 tables in 2 seperate databases that I want to "relate" and extract data from. How/any suggestions on how I can do this in Excel only?

I can do it from multiple tables in one database fine but not sure how to use relate and use multiple tables from multiple databases.

At the moment, I'm using something like:

Sub Get_Data_Test(strDBPath as string)

Dim rst as dao.recordset
Dim dbs as dao.database

set dbs = opendatabase(strDBPath)
set rst = dbs.openrecordset("select t1.*, t2.* from t1, t2 where t1.id = t2.id")

rst.movelast
rst.movefirst

sheets("MySheet").Range("A1").Copyfromrecordset rst

rst.close
dbs.close

set rst = nothing
set dbs = nothing

End sub

But - if from the example above, table t1 and table t2 were in different databases; I know I can open up the correct databases fine but how do I create the "interrelatibility" (now there's a good word!) between the two recordsets?

Regards,
Pete

 

[Moniker]

Link one table in to the other's database. That way, you cut out the extraneous part and work only with one DB.