run stored procedure and return values from VBA
35,186
I believe all you need is this
Dim Rst As ADODB.Recordset
and this
Set Rst = cmd.Execute
Author by
Alex Gordon
Check out my YouTube channel with videos on Azure development.
Updated on July 27, 2022Comments
-
Alex Gordon almost 2 years
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?