CODEWITHSUNDEEP
sqlexcelvba
Tarrant Hawkins
Tarrant Hawkins

Reputation: 13

Multiple Tables in stored procedure need to be produced in excel through vba

I have only ever dealt with returning individual tables so I don't know how to go about this. I don't have access to the stored procedure. I know it's all dependent on an initial id, which in turn can be connected to a list of accounts this person has. It returns 4 tables but when I try to return the data, I only get the initial table. Is there some iteration I don't know how to do? I know it's tough without the code to the sp, but here's what I have for the vba so far:

 Sub special_customer_data()
    
        Dim con As ADODB.Connection
        Dim cmd As ADODB.Command
        Dim rs As ADODB.Recordset
    
        
        Set con = New ADODB.Connection
        Set cmd = New ADODB.Command
        Set rs = New ADODB.Recordset
        
        con.Open "database connection"
        
        cmd.ActiveConnection = con
        
        cmd.CommandType = adCmdStoredProc
        cmd.CommandText = "sp"
        
        cmd.Parameters("@cust_id").Value = data_feed.Range("B1").Value
        
        Set rs = cmd.Execute
        write_results rs
        
        rs.Close
        con.Close
    
    End Sub

Sub write_results(ResultSet As ADODB.Recordset)

Sheets("data_feed").Range("A6:AF1000").ClearContents

Sheets("data_feed").Range("A6").CopyFromRecordset ResultSet
Sheets("data_feed").Range("A6").CurrentRegion.EntireColumn.AutoFit
    
End Sub

Upvotes: 1

Views: 440

Answers (1)

user1274820
user1274820

Reputation: 8144

As I said in my comments:

https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/nextrecordset-method-example-vb?view=sql-server-ver15

https://learn.microsoft.com/en-us/sql/ado/reference/ado-api/nextrecordset-method-ado?view=sql-server-ver15

Use the NextRecordset method to return the results of the next command in a compound command statement or of a stored procedure that returns multiple results. If you open a Recordset object based on a compound command statement (for example, "SELECT * FROM table1;SELECT * FROM table2") using the Execute method on a Command or the Open method on a Recordset, ADO executes only the first command and returns the results to recordset. To access the results of subsequent commands in the statement, call the NextRecordset method.

This means you'd want to call rs.NextRecordset and write_results rs until you have all the data.

Upvotes: 3

Related Questions