Search code examples
sqlexcelvbams-access

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

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?

Solution

  • Learned something new - Excel CAN pull data from multiple Access files via one SQL statement. You were on the right path with nesting. Have to set a connection, which can be the workbook or one of the Access files, then other data sources must be nested with embedded filepath. Examples:

    1. 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
    1. 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
    1. 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 ")

    I did a quick test with PowerQuery add-in and it was able to pull from both Access files and save dataset to worksheet. This does allow for a 'live' link to both data sources as a merged dataset.