Trap SQL Raiserror of severity less than 11

[Chris Galie]

Can anyone help me with a seemingly very ellusive solution to a
seemingly simple problem. I have a db in SQL Server 2000 SP3 and an
Access XP/2K ADP front end. I need to send a warning (Raiserror with
severity < 11) from a trigger back to Access if a condition is true.
I have seen in many positngs that this error is available in the ADO
errors set for the connection but no one seems to indicate how to
properly access the error set. Each time I try to access the error
set it is empty. I also understand that there is a problem using the
form events to do this. Since that is the case, can someone help me
to see when and how to get this error from SQL so I can use VBA to
raise this warning?

 

[Mary Chipman]

It's an InfoMessage that just shows status information if the severity
is less than 11, not a bona fide Error, which is why. If you want it
to show up as an error, try raising the severity level to between 11
and 16.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

 

[Chris Galie]

Mary,
I understand that I can get the error to show up at severity of 11
to 16. The problem is that I need to have sql return the error
message but allow access to finish the task. This process does nto
actually raise an error but a warning. Since severity of 0 to 10 are
warnings in sql, they do not create the runtime error in access. All
I need is to know how to get the error from the ado error set. I have
seen many other postings state that this is possible but I need to
know how.

Thanks,
Chris

 

[Steve Jorgensen]

There are 2 problems.

1. Theres's no way to get to the connection object the ADP uses for the form.
2. There's a bug in the wrapper ADO provider Access uses for ADPs that simply
doesn't record warning messages unless there is also an error message.

I've ended up working around this problem by actually writing the warning to a
table. A major kludge? Yes, but it's the only thing I know that works.

 

[Chris Galie]

Steve,
Thanks for the info. I was starting to think that this was going to
be the solution.

Thanks,
Chris