Trap a return code value from SQL server

S

Scott

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 @param varchar(11)
AS
IF (SELECT state FROM authors WHERE au_id = @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!
 
D

Douglas J. Steele

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)
 
S

Scott

Thanks, Doug!
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

(no private e-mails, please)


Scott said:
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 @param varchar(11)
AS
IF (SELECT state FROM authors WHERE au_id = @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!
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Using Access ADP to get return value from a SQL stored procedure 1
Using SQL Query Code in Form Module to Return Record Count 2
Compiled ADP getting return value from SQL Stored Procedure 1
Return Values from SQL Server to MS Access 1
Looking for Comments on Code 5
Get return code from SQL Server Sproc? 1
getting return value from stored proc 4
VBA Excel - Select a value from one cell in Excel to update a SQL table 1

Top