How To Distinguish Odbc Errors From Other Types In Form_Error (1 Viewer)

[whdyck]

Long Story:

We have an Oracle back end, Access front end. Unfortunately, when, an ODBC error arrives in Form_Error() from Oracle, say a constraint error, it's very cryptic. We want user-friendly without having to duplicate the error-detection logic in the front-end.

I've implemented user-friendly ODBC error-handling thus (Coles notes):
1. Any Oracle server error gets logged to an error-log table in Oracle, at which time an Oracle package variable gets set to the ID of the Error Log record in which that error message is stored.
2. In Access, if Form_Error() sees an error, it looks at at the Oracle package variable to identify whether there's an ODBC error that needs to be translated to a user-friendly version.
3. If the package variable contains a value, Access then retrieves the error from the error-log table using that value as the ID of the error record.
4. Access then uses a lookup table to generate a user-friendly error message.
5. Form_Error() then sets Response = acDataErrContinue to prevent the ugly ODBC error message from appearing.
6. It then blows away the Oracle package variable to prevent a future, non-ODBC error, from picking up the already-displayed error message by mistake.

This works well, except that we want to implement this scheme into production for only a few forms initially until we're confident that it's bulletproof.

So here's the problem scenario:
1. Form A without this user-friendly handling scheme generates an ODBC error. Package variable gets set, pointing to the error log ID. User sees an ugly error.
2. Since Form A does not yet implement user-friendly handling, the package variable never gets blown away.
3. Form B, which implements this user-friendly handling scheme, sees a non-ODBC error in Form_Error(). Form_Error() sees the package variable left over from the error in Form A and incorrectly reports it. It then nukes the package variable.

So the problem is that Form_Error() on form B needs to know whether the incoming error is from ODBC before it decides to use the package variable to report the ODBC error. If the incoming error is not ODBC, the error in the Error Log table is not the one we want to report.

Bottom Line: All Form_Error needs to know is whether the incoming error is an ODBC error. Is it possible to identify that in Form_Error()?

Note that there is another solution: in every error-handling portion of every sub or function, insert the call that blows away the package variable. That ensures that the package variable is always reset after every error, so that a later error cannot pick it up by mistake. But this a very extensive, brute-force change all across the app that I'd prefer to avoid. It would be much better if Form_Error could identify which incoming errors are ODBC, so it knows when the package variable corresponds to the current error.

Sorry for the long-winded explanation.

Thanks for any help you can give.

Wayne

Note: Cross-posted to utteraccess.com

 

[jdraw]

?? What identifies an ODBC error??
Here is a link to Oracle errors -- many seem to start "ORA-"...

Here is a link re MS Access ODBC errors that may help.

We need to see/know more bout your application and some code to offer more focused responses.

It seems --totally untested -- that you may be able to call a standard module and compare the Error identifier with some table values and return a boolean
from with form event code
eg. Is_Odbc_Error/Is_Not_odbc_Error (just a thought for consideration)

 

[whdyck]

We need to see/know more bout your application and some code to offer more focused responses.

I'm not convinced that's necessary.

Form_Error() does not need to know anything about the back-end in order to do what I want it to do. All it needs to do is answer this question: "Is this error coming via ODBC or not?" Once I have that answer, I have all I need to know to make this work.

I've seen the link you suggested re MS Access ODBC errors, and I'm going to look into that again to see if I can glean anything useful.

Thanks.

Wayne

 

[jdraw]

Wayne,

I found this link via Google search.

See the post near the end of the dialog by
sjlevine34
Author Commented: 2005-06-02

Good luck.

 

[whdyck]

Your response (and link) reminded me of another angle of attack: Try to identify which set of DataErr values constitute an ODBC error. Not sure if that's what I need, but the following listing suggests a bunch:
http://www.fmsinc.com/microsoftaccess/errors/ErrorNumberAccess2010-2003.pdf

But are these mutually exclusive?
If an ODBC error generates a given error number, does that number never appear from a non-ODBC error?
and similarly
If a non-ODBC error generates a given error number, does that number never appear from an ODBC error?

 

[isladogs]

I've done 2 separate posts giving a complete list of Access error codes:
Create a list of all Access error codes
Access Error Codes

The only ones not included are the negative ADO errors such as -2147352567

HTH

 

[whdyck]

I've concluded that Form_Error() will know that the incoming error is from ODBC if DataErr is one of the following:
3146
3151
3154
3155
3156
3157

 

[whdyck]

I've done 2 separate posts giving a complete list of Access error codes

Thanks. Would you be able to post your database in mdb format? We're on Access 2003.

 

[isladogs]

MDB version attached. AFAIK it will still work properly but not checked

 

[isladogs]

Forgot to mention - the error list included is for A2010 so there may be some errors that don't apply to A2003.
If you use the other link, you can create a list for your current version

Also I think you missed a few ODBC errors - this is from the db I sent & filtering for ODBC

 

[The_Doc_Man]

For what it is worth, ORACLE-generated errors are tough to parse out on an OpenVMS server, too. And for the same reason, really. In many cases, the error that gets signaled isn't one of the O/S errors because what happened was not an O/S, device, or network failure. It was an APP error. In many cases that means the interface code has to detect the error even though something came back from ORACLE. The interface code has to then resignal it as some other kind of error for the calling program to trap it.

For OpenVMS, it wasn't called ODBC because that is a Windows concept. We called it SQLnet. It was originally part of a package called SmartStar built by ShareBase. That company went belly-up and the engineers reconvened to form SYBASE.

The "ORA-nnnn" errors are so familiar that I can still remember them. In my nightmares. And I can state with some certainty that it has been that way with ORACLE for not less than the last 15 years - but maybe more like just over 22 years. Because we started looking for a replacement for what we WERE using and found ORACLE just after a massive failure of a ShareBase server in 1993. The replacement and conversion was done by 1995 or 1996. I remember it painfully because I had to reverse-engineer the ShareBase database so we could export it to a binary file and convert it to something ORACLE could directly import. I don't recommend doing that too often.

 

[jleach]

Just to be sure as I saw no specific mention of it, have you tried the DAO.Errors collection?

For example, if you catch VBA 3146 (or any of the others), you can look at DAO.Errors(0).Description (or .Number, etc) to get information returned by the server. I've usually found this a pretty easy way to log error info and pick from a grab-bag of custom messages depending on the error number, or fallback to a generic error message for the user.

Assuming of course that you are connected via DAO anyway.

Two additional things:
1. I've not used against an Oracle BE, but this should be standard behavior across any ODBC connection via DAO (ADO has it's own similar collection).
2. Form_Error() is a bit of an anomaly in terms of error handling, so maybe that's the reason for your workarounds with logging the info and trying to read it back?

Anyway, thought I'd throw it out there.

 

[whdyck]

Just to be sure as I saw no specific mention of it, have you tried the DAO.Errors collection?

Assuming of course that you are connected via DAO anyway.

Two additional things:
1. I've not used against an Oracle BE, but this should be standard behavior across any ODBC connection via DAO (ADO has it's own similar collection).
2. Form_Error() is a bit of an anomaly in terms of error handling, so maybe that's the reason for your workarounds with logging the info and trying to read it back?

Your comments helped clarify some things for me:

1. Yes, Form_Error() is really the problem here. If I check DAO.Errors(0).Description when an ODBC error occurs in a form, all I see is "This action was cancelled by an associated object." Not very helpful. This problem is documented here:
https://support.office.com/en-us/ar...b-206175-706122ff-24a3-4e41-ad59-3a7729a2c3d7

2. If I run a db.Execute() or other inline SQL against links to Oracle tables, on the other hand, then, yes, your approach gives me exactly what I need.

The challenge, then is coming up with a unified approach that always works when VBA code encounters an ODBC error, whether in a bound form or not.

Stiphout has offered an interesting solution for forms that involves hacking the Windows API (http://accessmvp.com/TomVanStiphout/OdbcErrors.htm). I tried to use his approach, but I found that if I wanted to expand upon a short, cryptic Oracle error message, I was unable to display a user-friendly error message that consumed more screen space than the original, cryptic error message. This was a deal-breaker for me. If I knew more about the Windows API, maybe I could have gotten around it, but even so, it just seems wrong to be doing it this way, and I doubt that I would have gotten that type of solution past my code reviews.

For my project, the breakthrough came from some help here re how to set up Oracle to log every server error, from whatever source. Now that I've implemented that, whenever VBA code sees an ODBC error, it can select from the error log table and formulate a user-friendly error message.

I've used some of the comments here to help determine that when an ODBC error occurs, Access VBA should see an error number in the following list:
3146, 3151, 3154, 3155, 3156, 3157
This error number will appear as either DataErr in Form_Error() or Err.Number otherwise.

I think I'm on my way, and I thank all who contributed to this discussion.

Cheers!

 

[davidcie]

Stiphout has offered an interesting solution for forms that involves hacking the Windows API. I tried to use his approach, but I found that if I wanted to expand upon a short, cryptic Oracle error message, I was unable to display a user-friendly error message that consumed more screen space than the original, cryptic error message. This was a deal-breaker for me.

Let me humbly add that I think Tom's solution is the only real complete solution if you are unable or unwilling to log errors database side (using SQL Server here) and, once you get used to looking at Win32 API code, it's not as hacky as it looks at first blush.

Secondly and to deal with the screen space problem you raised, we're using... more API calls to hide the error message before it is displayed and produce our own dialog as part of a broader error handling and reporting framework. I think our solution might look a little less intimidating because we removed a lot of code related to ODBC message parsing; Tom used it to feed something useful back to the user but we decided a simple generic error message + detailed log emailed to devs would be sufficient.

Please find a (hopefully complete) code extract with all the relevant bits attached.

 

[gemma-the-husky]

fwiw, I like to refer to some errors as "automation errors". It's not exactly a programming error, so the error object shows an error.number of zero, but there still a form_error where something went wrong.

you can get the error message TEXT in the form_error sub by accesserror(dataerr). Dataerr is the first parameter in the sub header.

eg (as @isladogs already pointed out)
?accesserror(3146)
ODBC--call failed.

?accesserror(3299)
ODBC Specification Conformance Error (|). Report this error to the developer of your application.

I think this is similar maybe to sending an outlook message. If you try outlookmessage.send, and the send fails because of an outlook error you again get a similarly unhelpful message. Outlook failed to send the message, but you can't really identify exactly why, within access. eg, try using A2003 to send a message when you have Outlook13 or Outlook16. I am pretty sure that fails.

 

[davidcie]

Trouble is that these are generic error messages, in essence "Something went wrong". If you're trying to debug a more serious frontend<->backend communication error (eg. a validated constratint, foreign key relationship) you need the full ODBC error message that contains actual SQL Server's reply in order to understand what really went wrong.

If you do not have SQL Server or other backends, absolutely Form_Error is your best friend