Ques. on interpreting sp error codes in Projects

[batteam]

I have an Access 2000 project that uses SQL 2000 sp's to read and write to my SQL tables. They work great. My problem is that I cannot get my VBA Access code in the (client side) project to receive the @@error global variable value that the sp sends when a runtime error occurs (in the sp). In the interest of brevity, the end of my sp code is:

xxxx......
Select @intErrorCode = @@ERROR
End

IF @intErrorCode = 0
COMMIT TRANSACTION

Else
ROLLBACK TRANSACTION

Return @intErrorCode

My problem occurs when I do the cmd.Execute in my project. If a sp generates a runtime error, the VBA code stops at the cmd.Execute, then a system generated error message box appears. The end of my VBA project code that calls the sp is:

xxxxxx....
cmdAddName.Parameters.Append cmdAddName.CreateParameter("RETURN_VALUE", adInteger, adParamReturnValue)

xxxx......
xxxx...... etc., etc.
cmdAddName.Execute

Beep
varErrorCode = cmdAddName.Parameters("RETURN_VALUE").Value
If varErrorCode = 99 Then
Beep
Beep
MsgBox "Error occurred.", 16, "System message"
Exit Sub
Else
.........

The program never runs to my code that receives the RETURN_VALUE so I can provide the user a more fiendly message. It always stops at the cmd.Execute. Anyone know why the sp runtime error causes the Access VBA code to stop at the cmd.Execute command?

Thanks for any help you can proivde.

 

[cmmrfrds]

Here is an article on error handling.

Do you have this type of error handling code in your function.

Public Function Yourfunction()
On Error GoTo ErrHandler

' your command object code

Exit Function
ErrHandler:
Dim er As ADODB.Error
Debug.Print " In Error Handler "; Err.description
For Each er In cn.Errors
Debug.Print "err num = "; Err.Number
Debug.Print "err desc = "; Err.description
Debug.Print "err source = "; Err.Source
Next
End Function

 

[batteam]

Thanks for your response. I have been on vacation for a few days and just getting back to this. I do have error handling in the command procedure similar to your example, but I put yours in anyway. I still get the system generated MS error msgbox when I force a runtime error in the execution of the sp. The code stops at the cmd.Execute in my VBA code in the Project. Maybe you can only trap and return a sp runtime error if you execute it in the sp and call it from the Project. Don't know. Books say do all of your data clean up and verification in the client application side before you execute your Insert/Update/Delete sp's so, next to a system crash, there is very little, if any, chance of the sp not running correctly. This makes sense and I do this in my code.

Feel free to comment on this again if you want.

Thanks again.

 

[cmmrfrds]

Whether the error comes back from the stored procedure probably has to do with the severity level. My guess is, if it is something like a disk IO error then the error won't be returned, but if it more informational and not a show stopper then it will be returned and you can interpret in the client code. I suggest reading up on severity levels.