Calling a Stored SQL query from Excel VBA

[Nelboy]

Good Afternoon, I am starting a new journey to learn SQL on top of my long usage of VBA

I have built the following

Sub extract_cash()

    Dim conn As ADODB.connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String, sql As String
    
    Set wkbMacro = ActiveWorkbook
    Set wskData = wkbMacro.Worksheets("Cash Transaction Table")
    
    wkbMacro.Worksheets("sheet1").Cells.ClearContents

    sConnString = .......
    
    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.connection

    ' Open the connection and execute.
    conn.Open sConnString
    
    'sql query
    sql = "SELECT client_acc, currency, sum(cash_amount) "
    sql = sql & "FROM test_cash_trans "
    sql = sql & "group by client_acc, currency;"

    Set rs = New ADODB.Recordset

    rs.Open sql, conn

    'export to excel
    'wkbMacro.Worksheets("sheet1").Cells(2, 1).CopyFromRecordset rs

End Sub

This exports the information I need but... I have a created stored procedure in sql which do the same thing called "extract_cash_balance"

could anyone tell me how to change the above to call the stored procedure rather than create the query in VBA


I tried the code below which resulted in a miserable fail [on the rs.open line]

Sub extract_cash()

    Dim conn As ADODB.connection
    Dim rs As ADODB.Recordset
    Dim sConnString As String, sql As String
    
    Set wkbMacro = ActiveWorkbook
    Set wskData = wkbMacro.Worksheets("Cash Transaction Table")
    
    wkbMacro.Worksheets("sheet1").Cells.ClearContents

    sConnString = ......
    
    ' Create the Connection and Recordset objects.
    Set conn = New ADODB.connection

    ' Open the connection and execute.
    conn.Open sConnString
    
    rs.Open ("exec extract_cash_balance", conn)
    wkbMacro.Worksheets("sheet1").Cells(2, 1).CopyFromRecordset rs

End Sub

many thanks in advance

 

[xenou]

I think in general with stored procedures you will want to create an ado Command object. Then you can basically tell it that you have a stored procedure and ask it to execute it.

An example is here (you wouldn't need to worry about parameters just yet, but this one also shows that part as well):
https://stackoverflow.com/questions/24267080/calling-stored-procedure-using-vba

Note: 'm not sure why they set command timeout to zero in the answer at that url though ... I think the default is 30 (seconds) and much of the time you don't need to set it at all. Maybe zero is the same as no timeout (or in other words, infinity, which I think wouldn't be so good generally!) So generally you can just leave that line out.

 

[Nelboy]

Thank you, I will give that a go in the morning and hopefully that will do the trick

 

[Nelboy]

Hi Xenou,

Thanks for your help. I have finally got it to work with

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim strConnString As String

strConnString = "Driver={ODBC Driver 17 for SQL Server};Server=........
    
'Create the Connection and Recordset objects.
Set conn = New ADODB.Connection

'Open the connection and execute.
conn.Open strConnString

'set cmd connection to active connection
Set cmd.ActiveConnection = conn

'Set CommandText equal to the stored procedure name.
cmd.CommandText = "extract_cash_balance"
cmd.CommandType = adCmdStoredProc

'Execute
Set rs = cmd.Execute

'export to excel
activeworkbook.Worksheets("Sheet1").Cells(2, 1).CopyFromRecordset rs

conn.Close
Set conn = Nothing
Set rs = Nothing