I believe the following should do it:
Dim conCurr As ADODB.Connection
Dim cmdCurr As ADODB.Command
Set conCurr = New ADODB.Connection
conCurr.Open <appropriate connect string>, , adAsyncConnect
Set cmdCurr = New ADODB.Command
cmdCurr.ActiveConnection = conCurr
cmdCurr.CommandText = "checkstate"
cmdCurr.CommandType = adCmdStoredProc
cmdCurr.Parameters(1) = ' pass your parameter here
cmdCurr.Execute
MsgBox "checkstate returned " & cmdCurr.Parameters(0)
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)
"Scott" <millardgroups[ at ]gmail.com> wrote in message
news:1159560473.373308.8610[ at ]b28g2000cwb.googlegroups.com...
[Quoted Text] > Can someone point me to how to trap a return code value from a SQL
> server? I have looked in Safari and in newsgroups... I must be missing
> it... I see how to send the return code value:
>
> [From SQL Server Books Online....]
> CREATE PROCEDURE checkstate [ at ]param varchar(11)
> AS
> IF (SELECT state FROM authors WHERE au_id = [ at ]param) = 'CA'
> RETURN 1
> ELSE
> RETURN 2
>
> The RETURN [Value] is very clear... but I'm not finding an example of
> how to read and handle the return code in VBA.
>
> Can someone point me in the right direction?
>
> Thanks!
>
|