Unable to get RAISERROR message in adp.

[Alexs]

I used upsize withard to convert mdb to adp. The SQL Server database is
created with triggers. The triggers have RAISERROR functions. RAISERROR
functions have message text.
Triggers are fired and I cannot update or delete records in tables. It's
good. But where is message text???
What to do next?

MSDE2000sp3, Access2003, Win2003 (all updates installed)

 

[Sylvain Lafontaine]

A2002 is not able to display these messages (see
http://support.microsoft.com/?id=275057 ).

For A2003, I don't know. However, with ADO, these messages are transmitted
from the server via closed recordsets and are usually stored in the Errors
collection of the connection objet. At first sight, this should be fine, as
it should be pretty easy to open this collection and take a look at it but -
as always with ADP - there is a bone: since A2003, it seems that it's now
impossible to take a look at the original ADO Errors collection.

The only solution that I see to this problem would be that you make the
update yourself by using your own ADO connection object. Not a pretty
solution, I admit.

 

[Alexs]

Decision is in RecordChangeComplete event of ADODB.Recordset:
I created form:

Option Compare Database
Option Explicit

Dim WithEvents rs As ADODB.Recordset

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Select Case DataErr
Case 2757
Response = acDataErrContinue
End Select
End Sub

Private Sub Form_Open(Cancel As Integer)
Set rs = Me.Recordset
End Sub

Private Sub rs_RecordChangeComplete(ByVal adReason As ADODB.EventReasonEnum,
ByVal cRecords As Long, ByVal pError As ADODB.Error, adStatus As
ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset)
If Not pError Is Nothing Then MsgBox pError.Description
End Sub

Now I can get any messages from SQL Server for rs of this form.

For action query (recordset is not used):

Private Sub btn0_Click()
On Error Resume Next
CurrentProject.Connection.Execute "sp_with_raiserror_function"
If Err.Number <> 0 Then MsgBox Err.Description
End Sub
where "sp_with_raiserror_function" is sp with RAISERROR.

In this case Error collection is collection of errors of
CurrentProject.Connection object.

 

[Malcolm Cook]

RAISERROR msgs will ony display to screen if severity level is greater than
10

see my earlier post on this:
http://groups.google.com/group/micr..._frm/thread/c5850ce5af238cd9/b0b3a1c29994efd9

I am using access 2003 and used to use access 2002/XP where AFAIK it was
true then as well.

I think MS's KB article on the subject that Sylvain quoted is barking up the
wrong tree since it uses severity level 0.

I use RAISERROR all the time in my triggers and they get displayed by the
client.

HTH,

Malcolm Cook