How do I nest queries in Excel VBA

Question

I am not sure if it is possible, but I am trying to nest queries. First I create the following 2 queries:

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

Then I'd like to compare both queries and return the differences in the following way:

mkQry = "SELECT (" & MyQuery & ").*" _
                & "FROM (" & MyQuery & ") LEFT JOIN (" & MyQuery2 & ") ON " _
                & "(" & MyQuery & ".F1) = " & MyQuery2 & ".F1) AND " _
                & "(" & MyQuery & ".F2 =  " & MyQuery2 & ".F2) AND " _
                & "(" & MyQuery & ".F3 =  " & MyQuery2 & ".F3) AND " _
                & "(" & MyQuery & ".F4 =  " & MyQuery2 & ".F4) AND " _
                & "(" & MyQuery & ".F5 =  " & MyQuery2 & ".F5)" _
                & "WHERE (((" & MyQuery2 & ".F5) Is Null))"
                
        Set MyRecordset99 = MyConnection2.Execute(mkQry)
        Worksheets("TST").Range("A1").CopyFromRecordset MyRecordset99

However, mkQry contains a syntax error and I am not sure how to solve this.

My question is:
Is it possible to nest queries, more or less, in the way I am trying to do and if so, how can I update the syntax to make it work.

Try "SELECT T1.* FROM ... . Currently you put the whole MyQuery-String into the SELECT part - that has to throw an error — Ike
– Ike, Commented Feb 14, 2022 at 14:12

FlexYourData · Accepted Answer · 2022-02-14 14:24:30Z

3

You should alias each of the sub-queries, then use the aliases throughout.

mkQry = "SELECT x.* " _
                & "FROM (" & MyQuery & ") x LEFT JOIN (" & MyQuery2 & ") y ON " _
                & "(x.F1 = y.F1) AND " _
                ...
                & "(x.F5 = y.F5) " _
                & "WHERE (((y.F5) Is Null))"

edited Feb 14, 2022 at 14:24

answered Feb 14, 2022 at 14:15

FlexYourData

2,4941 gold badge14 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

MK01111000 Over a year ago

Thanks FlexYourData, the syntax error is gone. I ended up with a another problem. "MyQuery" and "MyQuery2" are taken from different databases. So how will I be able to run the query. The following is not working: Set MyRecordset99 = MyConnection2.Execute(mkQry)

FlexYourData Over a year ago

If you are using Excel, may I suggest using PowerQuery instead of VBA to pull these datasets? You can easily create connections to multiple databases and join them in the Power Query Editor. All of this without needing to write any VBA.

MK01111000 Over a year ago

PowerQuery seems like being able to handle this. However, the code from this thread is just a small part of the whole code. a lot more is going on, so I prefer a VBA solution for now. Thank you for your help, I will look for a solution

Sgdva Over a year ago

@MK01111000 Just a quick note, I found PowerQuery being considerably faster than VBA SQL in some scenarios (like pulling unique records from multiple files). You may consider transform your SQL logic to a PowerQuery one.

FlexYourData Over a year ago

Additionally, you could consider a hybrid approach where you perform VBA logic, then from VBA, refresh the Power Queries to return dataset(s) to the Excel file, then continue with VBA if that is appropriate. Aside from this, PowerQuery has a great deal of functionality beyond just "getting data" and I find it is a better replacement in most data-wrangling VBA scenarios.

Farzin · Accepted Answer · 2022-02-14 14:29:56Z

0

You can execute nested queries with this syntax:

Select * from T1 Where Field01 In (Select Field02 from T2)

If you want compare result of two queries you can do this:

select T1.FieldList, T2.FieldList from T1 FULL OUTER JOIN T2 On T1.Field01 = T2.Field01

then (T1.Field01 is Null) and (T2.Field01 Not is null) ==> All records that exist in T2 but not exist in T1

and (T1.Field01 Not is Null) and (T2.Field01 is null) ==> All records that exist in T1 but not exist in T2

and (T1.Field01 Not is Null) and (T2.Field01 Not is null) ==> All records that exist in in both

I hope it can help.

answered Feb 14, 2022 at 14:29

Farzin

115 bronze badges

Collectives™ on Stack Overflow

How do I nest queries in Excel VBA

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related