VBA: Call SQL Server stored procedure with two arguments

Question

Like mentioned in the title, I just want to call a SQL Server stored procedure from VBA.

I can call my stored procedure like this:

  EXEC  dbo.spClientXLS @Nr =  ' 131783', @date = '21.09.2014'

Nr is a varChar(50) type value, and date is of type date

Now, if I want to call it from VBA, I get an error message. My code in VBA is:

...'SQL Server stored procedure which is to execute with parameters
Dim ADODBCmd As New ADODB.Command
With ADODBCmd
    .ActiveConnection = objconn
    .CommandTimeout = 500
    .CommandText = "dbo.spClient"
    .CommandType = adCmdStoredProc
End With
Set recordset = ADODBCmd.Execute(, date, Nr)

Date is of type Date, Nr is of type String.

I would be happy, if somebody can explain me, how I can handle it with two arguments.

Regards

bodjo · Accepted Answer · 2014-10-07 07:36:54Z

6

Try this.

Dim cmd As New ADODB.Command
Dim rs As ADODB.Recordset

With cmd
    .ActiveConnection = objcnn
    .CommandText = "spClient"
    .CommandType = adCmdStoredProc
    .Parameters.Refresh
    If .Parameters.Count = 0 Then
        .Parameters.Append cmd.CreateParameter("@Nr", adVarChar, adParamInput, 50)
        .Parameters.Append cmd.CreateParameter("@date", adDate, adParamInput)
    End If
    .Parameters.Item("@Nr").Value = "131783"
    .Parameters.Item("@date").Value = "09/21/2014"
    Set rs = .Execute()

End With

answered Oct 7, 2014 at 7:36

bodjo

3261 silver badge5 bronze badges

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

1 Comment

Kipcak08 Over a year ago

It's not working. I get the Error-Message that the typ is not compatibel.

Paresh J · Accepted Answer · 2014-10-07 07:40:13Z

1

You need to add command parameters in your code to accept parameter values. Check the below code:

    Set oConn = New ADODB.Connection
    Set rs = New ADODB.Recordset
    Set cmd = New ADODB.Command

    oConn.Open strConn  '' strConn will have your connection string

    stProcName = "thenameofmystoredprocedurehere" 'Define name of Stored Procedure to execute.

    cmd.CommandType = adCmdStoredProc 'Define the ADODB command
    cmd.ActiveConnection = oConn 'Set the command connection string
    cmd.CommandText = stProcName 'Define Stored Procedure to run

    Set prmUser = cmd.CreateParameter("@user", adVarChar, adParamInput, 7)
    prmUser.Value = strUser
    cmd.Parameters.Append prmUser

    Set prmApplication = cmd.CreateParameter("@application", adInteger, adParamInput)
    prmApplication.Value = 1
    cmd.Parameters.Append prmApplication

    Set rs = cmd.Execute

    Range("A1").CopyFromRecordset rs '' to copy data in recordset to excel.

'' or you can do like this

   Set rs = cmd.Execute
    Do Until rs.EOF
       '' Do Something
    rs.MoveNext
    Loop

answered Oct 7, 2014 at 7:40

Paresh J

2,4293 gold badges26 silver badges32 bronze badges

1 Comment

Edward Over a year ago

Thanks - got this working once I declared the parameters first: Dim prmUser As ADODB.Parameter

Collectives™ on Stack Overflow

VBA: Call SQL Server stored procedure with two arguments

2 Answers 2

1 Comment

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related