How to return multiple recordsets in a single execution using ADODB?

Question

I need to iterate through multiple recodsets produced by a single query.

However my current connection does not seem to support doing this. So when I do .NextRecordset I get the message:

Current provider does not support returning multiple recordsets from a single execution

This is my connection string:

DB_CONNECTION = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=Forecasting;Integrated Security=SSPI;"
Call Conn.Open(DB_CONNECTION)

What must I do to be able to use .NextRecordset?

Answer 1

Set the CursorLocation to adUseServer (instead of 'client side' )

Set RS = New ADODB.Recordset
strSQL = "Select * from States; Select * from Countries;"

With RS
   .CursorLocation = adUseServer
   .ActiveConnection = DB_CONNECTION
   .CursorType = adOpenStatic
   .Open strSQL
End With

Do
   If Not RS.EOF Then
       'do something
   End If
   Set RS = RS.NextRecordset
   If RS Is Nothing Then
       Exit Do
   End If
Loop Until RS.State = adStateClosed