Excel VBA Query Across Databases

[Chris C]

I'm trying to populate a recordset with 2 tables from seperate databases. So
far, I get no error when I run the query, but it's also not populating the
field from the second database (which happens to be the one I'm actively in).

The query is below:

With comboPubs
' Assign the Connection object.
.ActiveConnection = cnPubs
' Extract the required records.
.Open "select strName from db1_name..TblAnalysisOptions AO join
db2_name..TblResultHeader RH on AO.guidAnalysisOptions=RH.guidAnalysisOptions"

' Copy the records into cell A1 on Sheet1.
Sheet1.Range("A1").CopyFromRecordset comboPubs

' Tidy up
.Close
End With

Is there anyway to do this? Both our on the same machine.

 

[Jim Thomlinson]

You can have as many connections open as you want but only one connection can
be active at a time. The open statement deals with the active connection. So
your query is not going to work as far as I know.

 

[Tim Williams]

You'll need to either create a linked table in one of your db's or
"manually" perform the join from 2 separate recordsets.

Tim

 

[Chris C]

Within the same sub/procedure/function, can I open one database, close it,
and open the other?

Alternative: Is it possible to have one function, connecting to DB1, be
called from a wrapper function that is connecting to DB2?

 

[Tim Williams]

Within the same sub/procedure/function, can I open one database, close it,
and open the other?

Yes. You could query the first set of data, cache the results in an
array (using getRows()), then run the second query on the other DB.

Alternative: Is it possible to have one function, connecting to DB1, be
called from a wrapper function that is connecting to DB2?

Don't see why not.

Tim