Extracting SQL data through VBA

[vbasql_jack]

Hello

I have written multiple SQL queries such as:

begin
'select sales from data_table where year = 2016'
'select revenue from _reports where year = 2016'
..etc
end

When I run this query from top to bottom in SQL Server, I get a table result for each query, which is great.

However, the problem is when I am trying to run this full query through VBA, only the first table pulls through using:

range("A1").copyfromrecordset rs

How can I set up my VBA code to loop through the tables after the query has ran in VBA and pull the data from each table?

My workaround is to split out the SQL queries and run them individually. But I feel this slows down the process.

 

[vbasql_jack]

I found the solution

Using Multiple Recordsets - 4GuysFromRolla.com

Specifically, this part (copyright to the publisher!)



'Retrieve the multiple Recordsets
set oRS = Server.CreateObject("ADODB.Recordset")
oRs.Open SQL, sConnectString

'Work with the first Recordset (the Categories table)
Do While Not oRs.EOF
'......some processing here
oRs.MoveNext
Loop

'Move to the next Recordset
Set oRS = oRS.NextRecordset()

'Work with the second Recordset (the Region table)
Do While Not oRs.EOF
'......some processing here
oRs.MoveNext
Loop
Thank you everyone for taking a look at least! I just had to get this done. The code does move faster because of this.

Thank you!

****** id="cke_pastebin" style="position: absolute; top: 0px; width: 1px; height: 1px; overflow: hidden; left: -1000px;">
'Retrieve the multiple Recordsets
set oRS = Server.CreateObject("ADODB.Recordset")
oRs.Open SQL, sConnectString

'Work with the first Recordset (the Categories table)
Do While Not oRs.EOF
'......some processing here
oRs.MoveNext
Loop

'Move to the next Recordset
Set oRS = oRS.NextRecordset()

'Work with the second Recordset (the Region table)
Do While Not oRs.EOF
'......some processing here
oRs.MoveNext
Loop


</body>