How do I capture content of an ODBC error message from Oracle?

[Guest]

I am using Microsoft Access as a front end for an Oracle database. All of
the data validation is done in the Oracle Tables through the use of triggers.
The error messages are ones I coded for as user-defined exceptions with
concise user-friendly descriptions. They display in their entirety in the
forms I use to maintain the tables. For example, the following error message
may be displayed:

ODBC--insert on a linked table 'OBI_DONOR_EMAIL' failed.

[Oracle][ODBC][Ora]ORA-20700: Email address cannot be blank.

ORA-06512: at "OBIEBIS.OBI_DONOR_EMAIL_T2", line 23
ORA-04088: error during execution of trigger 'OBIEBIS.OBI_DONOR_EMAIL_T2'
(#20700)

I would like to, through the use of the form on error event and the mid()
function, display the error message as "Email address cannot be blank.".

Despite extensive searching, I have not been able to find the object holding
the message "[Oracle][ODBC][Ora]ORA-20700: Email address cannot be blank.
<LF><LF> ORA-06512: at "OBIEBIS.OBI_DONOR_EMAIL_T2", line 23 <LF> ORA-04088:
error during execution of trigger 'OBIEBIS.OBI_DONOR_EMAIL_T2' (#20700)". I
know it has to be there because the message box displays the error after
completion of the form on error event procedure.

Does anyone know where this error message resides in access and how I can
get at it during the form error event procedure?

sjl

 

[John Smith]

These errors are in the errors collection and there may be more than one of
them. For similar reasons I have just written the following function to
retrieve them:

Public Function GetDAOErrors() As String
Dim errX As Error, Message As String
On Error Resume Next
Message = ""
If Errors.Count > 0 Then
For Each errX In DBEngine.Errors
Message = Message & errX.Description & vbCrLf
Next errX
End If
GetDAOErrors = Message
End Function

I call this from the event error trap, with a little modification I think that
it will do what you want.

 

[Guest]

I already have tried querying the errors collection. Errors.count is always
0.

Does the form error event clear the error code prior to execution? It
almost seems like it does. If so, where does it store the error description
during the performance of any form error event routine, so that it can
display it in a message box afterward if the form error event routine
response is anything but acContinue?

sjl

 

[John Smith]

I must admit that I have not tried to access form level errors this way, I was
trapping errors from executing SQL in a form event. I would have expected them
to be available if the Form Error event, but I think that you only get the
DataErr Access error number. If you are not finding the Errors.count there, I
would also try Debug.Printing it from the form Before/After Insert/Update
events, it may be possible to see the errors during one of them.

Something like this in each of the events:
Private Sub Form_Error(DataErr As Integer, Response As Integer)
Debug.Print "Error Event -", Errors.Count
End Sub

Then look in the Debug Window for the results. This will also tell you whether
the Form Error event is firing.

The Errors collection also has a Refresh method. This should not be required
but may be worth trying if you still cannot find the errors!