How to capture trigger errors from SQL Server on a form

  • Thread starter Thread starter Jim
  • Start date Start date
J

Jim

Hi,

I've got an A2K mdb with a SQL Server 7 backend. Most RI is enforced using
relationships, which is fine, but I also use triggers for enforcing
"special" rules that can't be handled by check constraints. The scenario
that I'm having a problem with is when a user enters information into a form
that violates a business rule on the server, the error message that is shown
is very confusing to users, e.g.:

ODBC--update on a linked table 'tblAccount' failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]A company can only have a
single company account! (#50000)

The actual message from the trigger is buried in the text of the error, and
since users don't read, they never look at the actual message. I would like
to have some clean way of capturing this message before it's displayed,
stripping out all of the junk that just makes things more confusing, and
showing only the pertinent message. The Error event doesn't help, because
all it tells you is the error number, not the error message itself, and
there are no errors in the DBEngine.Errors collection when the Error event
fires. Short of going to unbound forms, is there any way to capture these
messages?

Thanks, Jim
 
The OnError() event provides a number as you indicated.
You use the Error function to return the description of
the error. You'd then parse out the parts of your message
contained in brackets or parens to reveal the useful
portion.

eg)

Private Sub Form_Error(DataErr As Integer, Response As
Integer)
Dim strDescription as String
strDescription = Error(DataErr)
End Sub
 
This does not work. All the number gives you is that it is an
Application-defined error, not the true text of the error itself. It's just
like the difference between Error(Err) and Error$ - the latter gives a more
specific contextual error.

Elwin said:
The OnError() event provides a number as you indicated.
You use the Error function to return the description of
the error. You'd then parse out the parts of your message
contained in brackets or parens to reveal the useful
portion.

eg)

Private Sub Form_Error(DataErr As Integer, Response As
Integer)
Dim strDescription as String
strDescription = Error(DataErr)
End Sub
-----Original Message-----
Hi,

I've got an A2K mdb with a SQL Server 7 backend. Most RI is enforced using
relationships, which is fine, but I also use triggers for enforcing
"special" rules that can't be handled by check constraints. The scenario
that I'm having a problem with is when a user enters information into a form
that violates a business rule on the server, the error message that is shown
is very confusing to users, e.g.:

ODBC--update on a linked table 'tblAccount' failed.

[Microsoft][ODBC SQL Server Driver][SQL Server]A company can only have a
single company account! (#50000)

The actual message from the trigger is buried in the text of the error, and
since users don't read, they never look at the actual message. I would like
to have some clean way of capturing this message before it's displayed,
stripping out all of the junk that just makes things more confusing, and
showing only the pertinent message. The Error event doesn't help, because
all it tells you is the error number, not the error message itself, and
there are no errors in the DBEngine.Errors collection when the Error event
fires. Short of going to unbound forms, is there any way to capture these
messages?

Thanks, Jim


.
Click to expand...
Click to expand...
 
Have you considered checking this kind of "normal" thing before doing the
update? The best approach is to not LET the user make the mistake -- which
you can, more or less easily, do by code behind the form if I understand
your description of the problem.

Larry Linson
Microsoft Access MVP
 
Back
Top