September 2, 2003 at 3:12 pm
My application uses Microsoft Access VBA as the front end to SQL Server. It contains a significant number of stored procedures and a few UDFs. I frequently assign SQL or stored procedure to VB recordsets using the connection 'Execute' method. In this case I have developed a sp and fn that computer an ID number. My objective is to be able to assign the return value of the function or sp to a variable in VB.
The code below uses VB variables as parameters in the EXEC statement to return the results of a stored procedure into a recordset that can be used in VB:
Set PnRS = Cnn.Execute("EXEC spGetPprRecord @PN='" & strPN & "', @Cage='" & strCage & "'")
I would like to be able to perform something like the below snippet to assign the return value to the variable strPprNo.
Dim strPprNo As String
Cnn.Execute ("EXEC " & strPprNo & " = dbo.fx_GetNextPprNumber @Customer='" & Me.txtCust & "'")
September 2, 2003 at 3:32 pm
In vba can you assign a variable to be an output parameter similar to vb?
If so you could pass the value back in an output parameter.
Steven
September 2, 2003 at 10:41 pm
Try using the command object instead of the connection object, it is more Intuitive for operations stored procedures that return values.
Pay Respect to People on your way up. For you will meet the same People on your way down.
He who knows others is learned but the wise one is one who knows himself.
September 2, 2003 at 10:41 pm
Try using the command object instead of the connection object, it is more Intuitive for stored procedures that return values.
Pay Respect to People on your way up. For you will meet the same People on your way down.
He who knows others is learned but the wise one is one who knows himself.
September 3, 2003 at 8:07 am
Yes, I was aware of the possibility of using the return parameter from an ADODB command object but preferred to use the connection execute command because the rest of the application uses them; I didn't want to research, write, and troubleshoot the necessary code to use the command object for this single purpose; and I know, in my heart, that there is some way to retrieve the return value from a stored procedure using the connection object. For now, I've returned the value to an ADODB recordset and retrieved the value successfully. Also, I've been successful in retrieving the value into another stored procedure variable, but am still trying to determine a solution for the original question. My research using Books On-Line, and the 4 reference books has been unsuccessful with this particular issue. Books on SQL Server ignore the VB issue, books on VB ignore the SQL Server issue, and books on Access don't adequately cover it.
September 3, 2003 at 10:51 am
quote:
...and I know, in my heart, that there is some way to retrieve the return value from a stored procedure using the connection object...
Nope, not that I know of. That's what a Command object is for.
September 3, 2003 at 2:29 pm
While you can certainly use a recordset to return the value using the connection object I am pretty sure the overhead will be less to use a command object and just use the output parameter capability.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
September 4, 2003 at 8:30 am
Thanks for your advice. I'll abandon efforts to use the connection object and switch to the command object. You advice saved me from banging my head against the wall a few times.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply