Pass Errors and Messages back to the Client

[Ranvier]

Can anyone tell me if it is possible to pass messages and erors from SQL Server 2000 triggers and Stored Procedures back to the Microsoft Access ADP client. Just wondering if there is a way of trapping them and possibly communicting them back to the user and/or writing to a log file.

Thanks you the help.

 

[ESquared]

Just use the RAISERROR statement in SQL Server. This will cause an error message to be displayed in Access.

If you are running a statement or SP explicitly (and not implicitly through form or control record source binding, for example) you can do regular VB error trapping, e.g., "On Error Goto MySub_Error." Then when an error occurs, check and see if the ADODB Connection.Errors.Count > 0. If so, ignore the VB error (it's just a copy of the first in this collection). You can then enumerate over these errors and do whatever you like with them: display them to the user in a friendly way, or write them to an error log. You can structure your error messages in a way that provides detailed information that the user doesn't see. For example,

RAISERROR ('Error Updating User|The data update operation you requested failed and will be logged.|1|SP:UpdateUser', 16, 1)

For Each oErr in oADOConnection.Errors
   sError = Split(oErr.Description, "|")
   MsgBox sError(2), vbExclamation or vbOkOnly, sError(1)
   If sError(3) = 1 Then WriteLog sError(4), oErr.Description
Next

That should get you started.

In terms of error logging, I would personally make the stored procedure or trigger do the logging, though, as this would be more reliable. Be careful with the trigger as most errors will roll back the implicit transaction so logging errors to a table becomes tricky.