Subqueries - multiple connection strings

Hi, main query (database 1) would have an inner part with a subquery (database 2).
Is it possible to run this in 1 sequence and spit out one result set into new Listobject,
alternatively Recordset?
Do you have such example please ?

Not able to understand properly.. Could you please clarify a bit?

A simple concept would look like this:

Please Login or Register  to view this content.

So idea is to create new ListObject table where external source would present the SQL which has 2 connections .

Is the Sub Query to a different Server or a different Database?

Besides, (Though never tried it before) If such a query executes properly on SQL, if should work without any problems in Excel too.

They're 2 different servers. A Company's software can execute that where (visually) it first connects into once source (asks for credentials), makes a pull and then asks to log into next source to complete the retrieval.
As a user, I don't see the details of that procedure. I assume in terms of excel VBA, one Sub would establish a connection to subquery, retrieve results into a memory (Recordset) and that would be used as a parameter,
to join with tables in main query. But from lack of my VBA-SQL knowledge, I seem not to put it into practice. Or is there a simpler way ?

I just tried to JOIN a Recordset (subquery) with a table in main query, but I get Type Mismatch as Recordset.Source of main query expects SQL as a string.
That part was like:

Please Login or Register  to view this content.

You might have to Initialise 2 connections 1 after another, 1 will have the values stored in the recordset, then the connection string will initialise the 2nd connection and use the recordset values in the 2nd query.

I will try to generate some code and post it in a while

That Recordset now should behave like a second table

Well, I don't think that is possible. I mean you might not be able to "Join" a SQL Table with a RS.

Considering my limited knowledge with SQL, I might be wrong on the above statement though.