Calling a Stored SQL query from Excel VBA

Nelboy

Board Regular
Joined
Mar 28, 2014
Messages
53
Office Version
  1. 365
Platform
  1. Windows
Good Afternoon, I am starting a new journey to learn SQL on top of my long usage of VBA

I have built the following

VBA Code:
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]

VBA Code:
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
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,825
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Mar 28, 2014
Messages
53
Office Version
  1. 365
Platform
  1. Windows
Thank you, I will give that a go in the morning and hopefully that will do the trick
 

Nelboy

Board Regular
Joined
Mar 28, 2014
Messages
53
Office Version
  1. 365
Platform
  1. Windows
Hi Xenou,

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

VBA Code:
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,127,827
Messages
5,627,124
Members
416,223
Latest member
RichardHell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top