I have a long query with the in Excel VBA
Rs.Open stSQL, conn, adOpenKeyset, adLockReadOnly
The stSQL contains two parts
1. select [some fields] from TableA into #TMP1
2. select [some fields from #TMP1 INNER JOIN TableB on ....
The first part retrieved a temporary solution into a temp table and the second part select fields based on joining the temp table to another table.
If I paste the whole SQL and run on SQL Management Studio. It works fine. However, no recordset can be retrieved through the VBA. Can VBA SQL allows directing 1st part of the result to a temporary table in order to subsequent query to use. Is there any workaround way to make it work ?