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?
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.
As you will see below, to run SQL queries in Access with VBA you can use either the DoCmd. RunSQL or CurrentDb. Execute methods.
You can do the subqueries-to-temp table stuff in a stored procedure, but you can't call a stored procedure from a view.
I believe all you need is this
Dim Rst As ADODB.Recordset
and this
Set Rst = cmd.Execute
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With