?
Solved

Handling Oracle errors via ODBC in Access forms

Posted on 2004-11-05
25
Medium Priority
?
1,299 Views
Last Modified: 2011-10-03
I have written a microsoft front end to an Oracle database where data validation is handled by Oracle Triggers.  Thus the error "Person not on file" displays in message box as;

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

  [Oracle][ODBC][Ora]ORA-20700: Person not on file
  ORA-06512: at "LEVINE.OBI_DONOR_EMAIL_T1", line 57
  ORA-04088: error during execution of trigger 'LEVINE.OBI_DONOR_EMAIL_T1'
   (#20700)

Likewise, the error "Invalid email address format. Must
  be {mailbox}@{domain}.{ext}." displays in message box as:

[Oracle][ODBC][Ora]ORA-20700: Invalid email address format. Must
  be {mailbox}@{domain}.{ext}.
  ORA-06512: at "LEVINE.OBI_DONOR_EMAIL_T2", line 20
  ORA-04088: error during execution of trigger
  'LEVINE.OBI_DONOR_EMAIL_T2'
   (#20700)

Without having to code the Access application to intercept the errors before they go to the Oracle database, I would like to be able to display the application-defined Oracle error messages without the user-unfriendly stuff.

Specifically, I would like to be able to use the form's On Error event to capture the error and then extract the user-friendly message from the string displayed in the message box.  Is there a way to do this?

sjl
0
Comment
Question by:sjlevine34
  • 11
  • 8
  • 5
24 Comments
 
LVL 51

Expert Comment

by:Steve Bink
ID: 12508699
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.
0
 
LVL 1

Author Comment

by:sjlevine34
ID: 12509168
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

0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 12509466
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.
0
Free recovery tool for Microsoft Active Directory

Veeam Explorer for Microsoft Active Directory provides fast and reliable object-level recovery for Active Directory from a single-pass, agentless backup or storage snapshot — without the need to restore an entire virtual machine or use third-party tools.

 
LVL 1

Author Comment

by:sjlevine34
ID: 12509782
Is there anyway to access that vba layer?

sjl
0
 
LVL 39

Expert Comment

by:stevbe
ID: 12512883
"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
0
 
LVL 1

Author Comment

by:sjlevine34
ID: 12515555
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
0
 
LVL 39

Expert Comment

by:stevbe
ID: 12516847
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
0
 
LVL 1

Author Comment

by:sjlevine34
ID: 12517796
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
0
 
LVL 39

Expert Comment

by:stevbe
ID: 12522732
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
0
 
LVL 1

Author Comment

by:sjlevine34
ID: 12523802
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
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 12535746
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.
0
 
LVL 1

Author Comment

by:sjlevine34
ID: 12539085
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
0
 
LVL 39

Expert Comment

by:stevbe
ID: 12539520
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
0
 
LVL 51

Accepted Solution

by:
Steve Bink earned 2000 total points
ID: 12539537
I understand that Access gets the text of the error.  My remarks were about obtaining a more user-friendly version of it.  You would either have to code your Oracle db to return better text, or use Access to parse through the error message on that end.  Since all external apps are only going to trigger the "application-defined" error message in Access, that is likely your sole option for trapping on the Access side.

As far as WHICH object contains the error text on the Access side, that may not even be available to you.  The only advice I can give at this point is to examine every object associated with the form, error-handling, and your particular ODBC connectivity.  It will be a long night, since objects and collections go several levels deep and there are TONS of properties.

But let's go back to what I said before:  code your Oracle to return better text.  If you are using stored procedures (MS nomenclature...whatever your Oracle equivalent is), you can easily code an SP to do whatever you need it to do, including receive data from Access, attempt the INSERT/UPDATE, handle the error locally and return a numeric or text value to Access for error handling/reporting.  It may not sound fun, but you have to consider the possibility that ODBC is not going to cooperate.

0
 
LVL 1

Author Comment

by:sjlevine34
ID: 12540619
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
0
 
LVL 39

Expert Comment

by:stevbe
ID: 12545964
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
0
 
LVL 1

Author Comment

by:sjlevine34
ID: 12546757
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


0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 13187582
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.  
0
 
LVL 1

Author Comment

by:sjlevine34
ID: 13187864
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
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 13196153
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:

http://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!
0
 
LVL 1

Author Comment

by:sjlevine34
ID: 13203378
Let's go ahead and close the question.

sjl
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 14131247
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...
0
 
LVL 1

Author Comment

by:sjlevine34
ID: 14133851
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
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 14139720
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=http://www.experts-exchange.com/Databases/MS_Access/Q_21196536.html#12539537>http:#12539537</a>}

0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question