Executing an SQL Server stored procedure using VBA in Excel and passing a variable

Question

I am trying to execute an SQL Server stored procedure using VBA in Excel whilst passing a stored procedure.

My stored procedure code is as follows:

update [ASHCOURT_Weighsoft5].[dbo].[Invoice] set Is3rdPartyPosted = 0 where DocumentId = @document_no

My VBA Code is as follows:

Sub reverse_posted()
Dim con As ADODB.Connection
Dim cmd As ADODB.Command
Dim Rs As ADODB.Recordset
Dim WSP1 As Worksheet
Set con = New ADODB.Connection
Set cmd = New ADODB.Command
Set Rs = New ADODB.Recordset
Dim i As Long
i = InputBox("Invoice Number to be re-posted")
con.Open "Provider=SQLOLEDB;Data Source=ashcourt_app1;Initial Catalog=ASHCOURT_Weighsoft5;Integrated Security=SSPI;Trusted_Connection=Yes;"
cmd.ActiveConnection = con
cmd.CommandText = "ashcourt_balfour_reverse_posting" & i
Set Rs = cmd.Execute(, , adCmdStoredProc)
Rs.Close
Set Rs = Nothing
Set cmd = Nothing
con.Close
Set con = Nothing
End Sub

My VBA is very rusty so apologies. Essentially I am trying to pass the contents of the i variable to the parameter @document_no in the stored procedure.

Thanks in advance

At this stage, I get an error stating it cannot find the stored procedure. It is trying to append the value in variable 1 to the stored procedure name — Darren Wardill
– Darren Wardill, Commented Nov 17, 2021 at 9:42
thanks for the feedback. If you're referring to the line cmd.CommandText = "ashcourt_balfour_reverse_posting" & i, there is already a space between the quotes and the ampasand, and another before the i variable — Darren Wardill
– Darren Wardill, Commented Nov 17, 2021 at 9:47
"ashcourt_balfour_reverse_posting " & i add space inside quoted string or use parameters — CDP1802
– CDP1802, Commented Nov 17, 2021 at 9:50
thanks again. This has generated a syntax error on this line - Set Rs = cmd.Execute(, , adCmdStoredProc) — Darren Wardill
– Darren Wardill, Commented Nov 17, 2021 at 9:53
Update queries dont return record sets. add line cmd.CommandType = adCmdStoredProc and then just cmd.Execute — CDP1802
– CDP1802, Commented Nov 17, 2021 at 10:02

CDP1802 · Accepted Answer · 2021-11-17 10:34:19Z

1

Add a parameter to the command

Option Explicit

Sub reverse_posted()

    Const PROC = "ashcourt_balfour_reverse_posting"

    Dim con As ADODB.Connection, cmd As ADODB.Command, i As Long
    i = InputBox("Invoice Number to be re-posted")
     
    Set con = New ADODB.Connection
    con.Open "Provider=SQLOLEDB;Data Source=ashcourt_app1;" & _
             "Initial Catalog=ASHCOURT_Weighsoft5;" & _
             "Integrated Security=SSPI;Trusted_Connection=Yes;"
    
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = con
        .CommandType = adCmdStoredProc
        .CommandText = PROC
        .Parameters.Append .CreateParameter("P1", adInteger, adParamInput)
        .Execute , i
    End With

    con.Close
    Set con = Nothing
End Sub

answered Nov 17, 2021 at 10:34

CDP1802

17.3k2 gold badges10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Darren Wardill Over a year ago

amazing, thank you so much for that, works a treat.

Collectives™ on Stack Overflow

Executing an SQL Server stored procedure using VBA in Excel and passing a variable

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related