Handling MS Access |
 |
by Raymond P. Woerner |
Run-time errors are often the most difficult ones to understand and fix. They don't occur until you're running the system, the error messages can be cryptic, and you may not understand why they occurred. In this article, I'll discuss various approaches handling run-time errors in MS Access.
No Error Handling
This approach is only useful during the development of an application. You don't put any error handling of your own in the function and let MS Access catch the error. It's useful during development since the development environment will point you at the line of code which caused the error. It has no place in a finished application.
Default Error Handling
I refer to the error handling code generated by the command button wizard as the "default" error handling. It catches the error, displays the error number and text in a message box, and resumes execution at the end of the subroutine or function which caused the error. This model is a good starting point for your own error handling function.
Improved Error Handling
The most obvious improvement to the default model is the inclusion of more information. You're not limited to providing only the error number and error text. You can create your own error handling function to show the object type and object name, where the error occurred, you can ask the user to note the information, and you can pass additional information to the function to aid in debugging. An example of this approach is shown in Figure One.
Another improvement can be made in situations where you resume execution after an error has occurred. You may not want to immediately exit a function causing an error. Some errors can be safely ignored and you can resume execution with the next statement. Other errors may be part of the normal operation of your application and you may need to execute most of the remaining statements in the function before exiting. This determination is made on a case by case basis.
The Case of the Normal Error
If you request to delete a record, MS Access will ask for confirmation. Do you really want to delete this record or should the request be canceled? You decide to cancel the request and it generates an error. This is a "normal" error since it results from a normal operation of your application. Do you let your user see it? Of course not. You check for that error number and simply resume execution further down in the function.
Proactive Error Handling
There are at least two areas where proactive error handling is the best approach.
The first area is the creation of a new record. You should insure the record has a unique primary key before adding it to your table. This can be done by using a counter field as the primary key, generating a unique key for the record using some other method, or checking for the existence of the record before adding it.
The second area is referential integrity. The enforcement of referential integrity is one of the main reasons for using a relational database, but it can also generate plenty of seemingly cryptic run-time errors. In situations where the existence of a record in one table is required to link it to a second table, you'll get a referential integrity violation if the record is missing. You can check for the existence of the required record before the database engine detects the violation and create the record to avoid the referential integrity violation.
Other Issues
Another issue is the scope of error handling. Basically, the last error handling routine declared remains in force until it's canceled or superseded. This means if function A1 turns on error handling, calls function B2 which doesn't have error handling, then any errors in B2 will use the error handling in A1. In general, you want each function to use its own error handling..
Ray Woerner is a HAL-PC member.
E-mail me at webmaster@hal-pc.org with any comments you have and tell me what you want to see here.
Back to the User Journal Home Page