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
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