1

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?

MK01111000
  • 770
  • 2
  • 9
  • 16
  • 1
    "The connection cannot.." where is the connection defined ? Are T1 and T2 Access tables in different accdb files ? – CDP1802 Feb 14 '22 at 16:51
  • Correct me if I'm wrong, but queries are always executed inside the database system, and can join only data where it has access to. As a workaround (untested), you could link T1 into the database of T2 and then execute the query against that database (or vice versa). – FunThomas Feb 14 '22 at 18:24
  • 1
    AFAIK, Excel cannot pull from more than one connection into one SQL statement. Possibly write each dataset to worksheets and then build recordset from ranges for writing combined dataset to another range. I have built recordset from a single Excel range, never tried a JOIN of multiple ranges. I will have to experiment. – June7 Feb 15 '22 at 02:11
  • Thank you for your replies. At least now I know that not more than one connection can be pulled into one SQL statement. I will have to look for a alternative solution – MK01111000 Feb 15 '22 at 06:41
  • I tested building SQL with INNER JOIN referencing two sheets and it works to open recordset. – June7 Feb 15 '22 at 09:01
  • @June7 Query more than 1 excel file - see https://stackoverflow.com/questions/38555156/using-vba-excel-for-an-sql-query-across-multiple-databases – CDP1802 Feb 15 '22 at 11:04

1 Answers1

1

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.

June7
  • 19,874
  • 8
  • 24
  • 34