Handling Oracle errors via ODBC in Access forms

I doubt you will be able to use the Form's OnError event, since it is not a form error.  It is an error returned from an external ODBC source.  You will have to trap the error at the connection, recordset, or command object you are using to interface with Oracle.  It will likely take a little experimentation to find out where your first trap opportunity is, but you can start searching by tracing through the code to see if the error exists BEFORE it is reported.  Failing that, you can use "On Error Goto ..." to trap all errors, and sort the error numbers as they come up.

Is there a way to get at the ODBC or connection object for the table involved and, from it, obtain the error message?  If so, I could use the On error event to go look for the object property and get the message from there.  Once I have the message, I can use text operations to extract what I want.

I really want to avoid having to put a VBA layer between the table and its form.

This will be very useful to us because we have several applications where Oracle is the database and Access is the front end.

sjl

Not without a VBA 'layer' as you call it.  You have to remember that Access is an event-driven platform...regardless if you manually code anything or not, there is ALWAYS a VBA layer.  It is just that most of the time you will not see it, and a properly coded database will have this layer be transparent to the users.

Is there anyway to access that vba layer?

sjl

"I doubt you will be able to use the Form's OnError event"

perhaps this will work because it captures data errors ... did you try it?

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    Msgbox DataErr
End Sub

Steve

I have tried that.  It only gives me the number of the form exception for ODBC error, which, unfortunately, because I am not at the computer with the application, I cannot remember.  However, I am using that statement to place a debug marker to stop the program, so that I can use the immediate window to explore the contents of objects available to me.

However, as you know, unless the response code nullifying the error is returned, the complete error, including the specific oracle errors (namely the ones I defined in my Oracle triggers, all under the user defined code of ora-2600, display in a message box.  Thus, in some object resides the complete error code, be it an ODBC object or a connection object of some sort.  I need help in identifying that object so that I can capture the complete error message, much as the message box in which Access displays the error does.

Do I need to add reference objects?  If so, which ones?

sjl

have you tried looking at the CurrentProject.Connection.Errors collection?

Private Sub Form_Error(DataErr As Integer, Response As Integer)

Dim lngErr As Long

    Select Case DataErr
        Case 456789  ' whatever the oracle err number returns
            For lngErr = 0 To CurrentProject.Connection.Errors.Count - 1
                 Msgbox "error #" & lngErr
            Next
            Response = acDataErrContinue
        Case Else
            Response = acDataErrDisplay
    End Select

End Sub

Steve

Stevbe

I really appreciate your response and, being at the machine with the application, I just explored it.

The oracle number returned by the application for ODBC errors is 3155.

Setting the breakpoint that I described above in the Form_Error sub, I used the immediate window to check I looked at CurrentProject.Connection.Errors.Count and it was 0.  

Nevertheless, to be sure that I was not dealing with the value of the property being changed when I paused on the breakpoint, I tried your code.   This resulted in no message box but an inability to leave the record on the form.   Thinking that this might be due to CurrentProject.Connection.Errors.Count being 0, I substituted the following code for yours:  

Private Sub Form_Error(DataErr As Integer, Response As Integer)
Dim lngErr As Long

   If DataErr = 3155 And CurrentProject.Connection.Errors.Count > 0 Then
            For lngErr = 0 To CurrentProject.Connection.Errors.Count - 1
                 MsgBox "error #" & lngErr
            Next
            Response = acDataErrContinue
        Else
            MsgBox DataErr & " " & CurrentProject.Connection.Errors.Count
            Response = acDataErrDisplay
    End If

End Sub

This yielded a message box displaying '3155 0', verifying that CurrentProject.Connection.Errors.Count is 0.

I guess we need to keep looking.  I really appreciate your help and that of routinet provided to this point.  Thanks to both of you.

sjl

hmmm ...  3155 = ODBC--insert on a linked table '|' failed

Is there anyway you can raise a custom error number from Oracle so you could identify which of your triggers threw the error?

Steve

The problem is that the oracle errors are in the description and do not come across as a specific data errors.  Thus, without being able to parse the description, I still could not get the oracle error number.

sjl

And I think that is at the core of the answer you are looking for.  Application-defined errors, when returned to Access, fall under a series of catch-all error codes.  Unless you have a method to catch the error on the Oracle server, and code it to return a more user-friendly error message, I do not believe you will be able to do what you're aiming at...Access only has what Oracle gives it.

Routinet

Access obviously receives the full Oracle error message because it displays it.  So it must be there in some object because, otherwise, how would it have the complete error, including the user-friendly description to display?

For example, this error message displays in the application:

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

 [Oracle][ODBC][Ora]ORA-20700: Invalid email address format.
       Must be {mailbox}@{domain}.{ext}.

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

As you can see, the message "[Oracle][ODBC][Ora]ORA-20700: Invalid email address format.   Must be {mailbox}@{domain}.{ext}." is a part of that error message.  (I used linefeeds in the text of the message in the Oracle trigger so that I could control its display and thus separate it from the rest of the message).

So access must have the message somewhere, if not several places.  The message is generated if you try to enter the information in table directly.  I have looked in currentproject, currentdb and dbengine objects but could not find it.

I am still going to leave the question open and award the 500 points to the person providing the correct answer.  I am sure the error message is in one of the objects at the time the form error event is triggered because, otherwise, how is the error message box still obtaining it for display after completion of the event procedure?

sjl

still trying to dance around to find whee those errors are hidden ...

I am thinking that a form's recordset by default in Access is a DAO recordset, now what captures a DAO recordset erro ... the DBEngine Object. Have you tried ...

 If DataErr = 3155 And CurrentProject.Connection.Errors.Count > 0 Then
            For lngErr = 0 To DBEngine(0)(0).Errors.Count - 1
                 MsgBox "error #" & lngErr
            Next
            Response = acDataErrContinue
        Else
            MsgBox DataErr & " " & DBEngine(0)(0).Errors.Count
            Response = acDataErrDisplay
    End If

Stevbe

I tried your code and got the following error message when I tried to compile it:

     Compile error: Method or data member not found

The statement on which this occurred was:

    For lngErr = 0 To DBEngine(0)(0).Errors.Count - 1

Errors was what was highlighted.

I got the same compilation error when I made the statement  "For lngErr = 0 To DBEngine(0).Errors.Count - 1".

It does compile when I eliminate the subscript from DBEngine.  Unfortunately, when I use the following code, I get the message "error #Operation is not supported for this type of object."

If DataErr = 3155 And DBEngine.Errors.Count > 0 Then
        For lngErr = 0 To DBEngine.Errors.Count - 1
                 MsgBox "error #" & DBEngine.Errors(lngErr).Description
            Next
            Response = acDataErrContinue
        Else
            MsgBox DataErr & " " & DBEngine.Errors.Count

            Response = acDataErrDisplay
      End If

This is not the message I am looking for.  But I think we are getting closer.

I appreciate your continued effort.

sjl

ok ... there is an error collection associated with the DAO library itself which is the parent of DBEngine ...


If DataErr = 3155 And DBEngine.Errors.Count > 0 Then
        For lngErr = 0 To DAO.Errors.Count - 1
                 MsgBox "error #" & DAO.Errors(lngErr).Description
            Next
            Response = acDataErrContinue
        Else
            MsgBox DataErr & " " & DAO.Errors.Count
            Response = acDataErrDisplay
      End If

Stevbe

I implemented your code as follows:

If DataErr = 3155 And DAO.Errors.Count > 0 Then
        For lngErr = 0 To DAO.Errors.Count - 1
                 MsgBox "error #" & DAO.Errors(lngErr).Description
            Next
            Response = acDataErrContinue
        Else
            MsgBox DataErr & " " & DAO.Errors.Count
            Response = acDataErrDisplay
      End If

As you can see, the only change I made was in the If clause, where I changed "DBEngine.Errors.Count > 0" to "DAO.Errors.Count >0".

The result I got in exercising it was a msgbox displaying "3155 0", meaning that the else leg of the logic was the path taken and that DAO.Errors.Count = 0.

Curiously, today, when I exercise the error event procedure, I get DBEngine.Errors.Count = 0 when I test for it in the immediate window.  I am now wondering if that dbengine.errors.count = 1 from yesterday was an artifact of my testing things, that did not go away because I did not close and restart the program.

Frustrating, isn't it.  However, I want to continue to pursue this because why write a lot of code, including perhaps a form before update routine that does the updating, if one can get the error code using objects Access has already provided.

Anyway, we have time to solve this.  And once we do, it will be useful to us because, with our main vendor-provided application in Oracle on an IBM unix box and Access readily available, it is very advantageous for us to be able to use Access as a front-end for oracle tables.

sjl

I came back to this question as part of my CV duties, and I would still recommend my comments as the solution.

I still believe you may not have the option of an exposed error text from Oracle.  Whatever text is being returned is obviously going to Access, but the real question is will Access let you play with it.  From our experimentation during this question, I'm going with "no", and reiterate my suggested solution of recoding Oracle to return trappable error codes.  This way you can test the result of a Command object from within Access, and translate it to user-friendly accordingly.

Of course, you also have the option to leave the Oracle messages as-is, and commence on your user training to get them to report the errors to IT correctly.  But I think we both know how that is going to work out.

Please let me know how you would like to continue.  

I have not had a chance to work on this for awhile.

I am already using specific user-defined error codes from Oracle.  What I have not tried is to use VBA to do the writes so I can really use the On error goto trap rather than the form on-error event.  When I get a chance, I will try this.

Appreciate your following up on this.

sjl

In that case, would you like to close this question?  I obviously feel my 'solution' is the closest we'll get, but since we've found nothing workable for you, you can have your points refunded by posting a request here:

https://www.experts-exchange.com/Community_Support

If you do find anything from your experimentation, please do post back here.  Any solution you generate for this issue will help anyone else with the same problem.

Good luck with the rest of your project!

Let's go ahead and close the question.

sjl

This is kind of a toss-up.  There was no workable solution found, but I can't say I know enough about Oracle to claim there is no solution.  Either the comment I indicated, or PAQ/Refund...

Funny you should bring this up, today?  For I have been working on this issue this morning, with successful results.

Basically, I gave up on the idea of using the On Error form event, and instead made the source of the form an Access table that is populated from the oracle table during the On Open event.  

I have coded the Before Update Event to capture an Oracle error if it occurs.  The coding is as follows:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strJetError As String, strCheckErr1 As String, strCheckErr2 As String
Dim strCheckErr3 As String, strErrRef As String
    On Error GoTo errhandler
    Set dbcurrent = CurrentDb
    Set rsOracle = dbcurrent.OpenRecordset("Oracle Table", dbOpenDynaset)
    rsOracle.FindFirst "tkey = '" & Me!ID & "'"
    If rsOracle.NoMatch Then
        rsOracle.AddNew
        rsOracle!tkey = Me!ID
        rsOracle!doc_title = Me![document title]
        rsOracle!dcmt = DocString(Me!hyperlink)
        rsOracle.Update
      Else
        rsOracle.Edit
        If rsOracle!doc_title <> Me![document title] Then
            rsOracle!doc_title = Me![document title]
            rsOracle.Update
        End If
        If IsNull(rsOracle!dcmt) Or rsOracle!dcmt <> DocString(Me!hyperlink) Then
            rsOracle.Edit
            rsOracle!dcmt = DocString(Me!hyperlink)
            rsOracle.Update
        End If
    End If
ExitSub:
    rsOracle.Close
    Exit Sub
   
errhandler:
    If Err.Number = 3146 Then
        strJetError = DBEngine.Errors(0).Description
        If InStr(strJetError, "check constraint") > 0 Then
            strCheckErr1 = Right(strJetError, Len(strJetError) - _
                InStr(strJetError, "check constraint") - 17)
            strCheckErr2 = Left(strCheckErr1, InStr(strCheckErr1, "violated") - 3)
            If InStr(strCheckErr2, ".") > 0 Then
                strErrRef = Right(strCheckErr2, Len(strCheckErr2) - _
                  InStr(strCheckErr2, "."))
                Set rsErrRef = dbcurrent.OpenRecordset("select ERROR_MESS from " & _
                     "[Error Reference Table] where CONSTRAINT_NAME = '" & _
                     strErrRef & "'", dbOpenDynaset)
                If rsErrRef.EOF Then
                    strCheckErr3 = strJetError
                  Else
                    rsErrRef.MoveFirst
                    strCheckErr3 = rsErrRef!ERROR_MESS
                End If
                rsErrRef.Close
              Else
                strCheckErr3 = strCheckErr2
            End If
            msgbox strCheckErr3
          Else
            msgbox strJetError
        End If
      Else
        msgbox Err.Number & " - " & Err.Description
    End If
    Cancel = vbCancel
    Resume ExitSub

End Sub

The oracle table I am using has a constraint on the DOCTITLE field that prohibits its value from being "levine".   Thus when I enter the value "levine" for this field, oracle provides the message "[Oracle][ODBC][Ora]ORA-02290: check constraint (LEVINE.DOCTITLE_CK) violated".  The coding of the above errhandler extracts the constraint name minus the schema name ("DOCTITLE_CK") and then obtains its associated error message from the Error Reference Table and displays it in a message box.  Because the associated error message for DOCTITLE_CK is "cannot name document title "levine", that is what is displayed in the message box.

While not the solution I was originally seeking, this solution will meet my purposes.  Thank you all for your help on this one.

sjl

I'm glad you found something that worked, at least!  I was pretty sure the OnError event was not going to cut it for you, and parsing through the error text as returned by Oracle will at least get you where you need to be, yes?  I'll change my recommendation to

Accept: routinet {<a href=https://www.experts-exchange.com/questions/21196536/Handling-Oracle-errors-via-ODBC-in-Access-forms.html#12539537>http:#12539537</a>}