Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

run stored procedure and return values from VBA

I would like to pass parameters into a stored procedure in SQL Server 2008 and I would like to store the results in a ADODB.Recordset

Currently I have this:

Public Sub UpdateWithStoredProcedure()
        Dim cmd As New ADODB.Command
        Dim conn As ADODB.Connection
        Dim prm As ADODB.Parameter
        Dim strConn As String
        Dim strSQL As String

        strConn = "Provider=SQLOLEDB.1;" & _
            "Data Source=(local); Initial Catalog=NorthWind;" & _
            "Integrated Security=SSPI"

        Set conn = New ADODB.Connection
        conn.Open strConn

        Set cmd = New ADODB.Command
        cmd.CommandText = "procOrderUpdate"
        cmd.CommandType = adCmdStoredProc
        cmd.ActiveConnection = conn

        Set prm = cmd.CreateParameter("OrderID", adInteger, adParamInput)
        cmd.Parameters.Append prm
        cmd.Parameters("OrderID").Value = 1

        Set prm = cmd.CreateParameter("OrderDate", adDate, adParamInput)
        cmd.Parameters.Append prm
        cmd.Parameters("OrderDate").Value = "1/1/2007"

        Set prm = cmd.CreateParameter("ShipVia", adInteger, adParamInput)
        cmd.Parameters.Append prm
        cmd.Parameters("ShipVia").Value = 2
        Set prm = cmd.CreateParameter("Freight", adCurrency, adParamInput)
        cmd.Parameters.Append prm
        cmd.Parameters("Freight").Value = "10.5"

        'Execute the Stored Procedure
        cmd.Execute

        'Close the connection
        conn.Close
    End Sub

The question is how do I store this in a recordset instead of just executing it?

like image 786
Alex Gordon Avatar asked Nov 23 '11 22:11

Alex Gordon


People also ask

Can we return value from stored procedure?

A stored procedure does not have a return value but can optionally take input, output, or input-output parameters. A stored procedure can return output through any output or input-output parameter.

Can you run SQL in VBA?

As you will see below, to run SQL queries in Access with VBA you can use either the DoCmd. RunSQL or CurrentDb. Execute methods.

Can a view run a stored procedure?

You can do the subqueries-to-temp table stuff in a stored procedure, but you can't call a stored procedure from a view.


1 Answers

I believe all you need is this

Dim Rst As ADODB.Recordset

and this

Set Rst = cmd.Execute
like image 94
Pynner Avatar answered Sep 30 '22 22:09

Pynner