How do I create a recordset of nested queries from different databases-CodePudding

The following 2 queries are taken from tables on different databases

MyQuery = "Select * from " & "T1"
MyQuery2 = "Select * from " & "T2"

I'd like to nest these in the following query

Dim rrst As New ADODB.Recordset     
mkQry = "SELECT x.*" _
        & "FROM (" & MyQuery & ") x LEFT JOIN (" & MyQuery2 & ") y ON " _
        & "(x.F1 = y.F2) AND " _
        & "(x.F1 = y.F2) AND " _
        & "(x.F1 = y.F2) AND " _
        & "(x.F1 = y.F2) AND " _
        & "(x.F1 = y.F2)" _
        & "WHERE (((y.F2) Is Null))"

rrst.Open mkQry
Worksheets("TST").Range("A1").CopyFromRecordset rrst

However, I am getting an error:
The connection cannot be used to perform this operation On the following line: rrst.Open mkQry

I guess it has to do with MyQuery and MyQuery2, both being from a different database. Is there a way to make this work?

CodePudding user response：

Learned something new - Excel CAN pull data from multiple Access files. Have to set a connection, which can be the workbook or one of the Access files, then other data sources must be nested. Example:

connection to workbook using ADODB objects with early binding so would need reference to Microsoft ActiveX Data Objects x.x Library.

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cn.Open "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & ThisWorkbook.FullName & ";HDR=Yes';"
rs.Open "SELECT H.*, P.* FROM (SELECT * FROM Holidays IN 'C:\Users\Owner\June\Umpires.accdb') AS H " & _
        "INNER JOIN (SELECT * FROM Projects IN 'C:\Users\Owner\June\LabData.accdb') AS P " & _
        "ON H.HolID = P.ProjRecID", cn, adOpenStatic, adLockReadOnly

connection to one Access file

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source='C:\Users\Owner\June\LL\Umpires.accdb'"
rs.Open "SELECT Holidays.*, Pjt.* FROM Holidays INNER JOIN (SELECT * FROM Projects IN 'C:\Users\Owner\June\DOT\Lab\Data\LabData.accdb') AS Pjt ON Holidays.HolID = " & _
         "Pjt.ProjRecID ", cn, adOpenStatic, adLockReadOnly

DAO with early binding so reference Microsoft DAO 3.6 Object Library

Dim ws As DAO.Workspace
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set ws = DBEngine.Workspaces(0)
Set db = ws.OpenDatabase("C:\Users\Owner\June\LL\Umpires.accdb")
Set rs = db.OpenRecordset("SELECT Holidays.*, Pjt.* FROM Holidays " & _
        "INNER JOIN (SELECT * FROM Projects IN 'C:\Users\Owner\June\DOT\Lab\Data\LabData.accdb') AS Pjt " & _
        "ON Holidays.HolID = Pjt.ProjRecID ")