Error Handling Shields Your Code | Real World Microsoft Access Database Protection and Security

VBA code, as the glue that binds your Access application together, must manage not only what the developer expects to happen but also the unexpected. When your code is set up properly, Access will trap errors when they happen and transfer them to a special section of code called an error handler. Error handling helps protect your database because it allows you to keep the logic of your application hidden from your less-technical users. It also is important to include error handling in all your code but especially for Access 2000 and later. If you don't include it and you have cleared the Allow Special Keys check box in the startup options, the following problems will occur:

Your Access program will stop running where the error occurs.
Your users will be oblivious to the nature of the error.
Your users will be confused because they will not receive any response from the program.

Now that we have established that error handling is important, let's look at the standard error handling that is added to your VBA project by Access wizards that generate code.

Standard Error Handling Added by the Access Wizards

Whenever you run one of the Access code generation wizards, such as the Command Button wizard, the wizard adds a basic error handler section to the code it creates. For example, look at a sample procedure created by using the Command Button wizard. The code that this wizard creates opens a form in VBA code and comes complete with error handling code. All the code samples for error handling come from the demonstration form called frmErrorHandling (shown in Figure 4-7).

Figure 4-7: The frmErrorHandling form demonstrates the error handling for VBA code.

The first sample comes from the onClick event behind the cmdError1 button. This event intentionally tries to open a form that doesn't exist in the database.

 Private Sub cmdError1_Click() On Error GoTo Err_cmdError1_Click     Dim stDocName As String     Dim stLinkCriteria As String     ' Try to open a form that doesn't exist.     stDocName = "frmTasmanianTiger"     DoCmd.OpenForm stDocName, , , stLinkCriteria Exit_cmdError1_Click:     Exit Sub Err_cmdError1_Click:     MsgBox Err.Description     Resume Exit_cmdError1_Click End Sub

This code has the following clearly identifiable sections:

An onError statement is positioned at the top of the subroutine. When an error occurs, the code will be processed according to the onError statement's instructions. Generally , these instructions will involve jumping to the Error Handler section near the bottom of the procedure.
The Main Body of the procedure is where all the working code is placed.
The Exit section is where code requirements common to processing both the Main Body and the Error Handling section are tidied up, including closing open objects, variables , and files.
The Error Handler section reports problems in processing to the users. This section is located at the bottom of the procedure. When this section is completed, the code generally jumps back to the Exit Section, and the procedure is closed.

So what happens when an error occurs in the code in the onClick event of button 1? On the line of code where the form is opened, an error occurs because the form does not exist in the database. The code then jumps to the Error Handler section at the bottom of the code, and a message box displays a descriptive error to the user (see Figure 4-8). The code then jumps back to the Exit Section and a graceful (if not successful) exit from the subroutine occurs. All unremarkable stuff for a seasoned developer but the important thing is that life carries on for the user and your code is safe from the prying eyes of casual users.

Figure 4-8: When an error occurs, the error handler displays a message box.

What Happens When You do Not Have Error Handling

If you do not have error handling in your code and the user runs into a problem, you're going to expose your code to the user ( illustrated in Figure 4-9). As you can see, the user can choose the a Debug or End button. If the user clicks the End button, your code is not exposed. If the user clicks the Debug button, however, the code is exposed on the line of VBA code that failed. Seeing the code will probably traumatize your beginner users and will tempt those users who like to explore.

Figure 4-9: This message box is a result of no error handling.

Note

Not including error handling in the early stages of development and testing is acceptable because the debug option, as shown in Figure 4-9, allows you to see the problem code immediately. Forgetting to add your error checking when the system goes live is certainly not recommended if you are trying to protect your code.

To demonstrate what happens when there is no error checking, button 2 on the frmErrorHandling form shows what happens when you try to open a form that doesn't exist in the database (see Figure 4-9). In this code, the penultimate line is a msgbox statement that will never execute because the database will break on the problem line.

 Private Sub cmdError2_Click() ' This button demonstrates what happens when you do not add error handling code. ' In particular, try this button with the Allow Special Keys option ' selected and then cleared in the Access startup properties. ' Open a form that does not exist to cause an error intentionally. DoCmd.OpenForm "frmTasmanianTiger" ' The procedure should never get to the following line: MsgBox "This demonstrates that the software has continued executing", vbInformation End Sub

Clearly, we would like code to respond in a more consistent manner. If you can avoid these messy-looking debug prompts from appearing in your system, that would be a good outcome. So let's have a look at how you can ensure that the Debug button does not feature in your user interface.

Removing the Debug Button

In Access 97, you can stop the user from going into debug mode (also called break mode) by going to the Startup dialog and clearing the Allow Viewing Code After Error check box (shown in Figure 4-10). This action makes the Debug button unavailable.

Figure 4-10: The Allow Viewing Code After Error check box in Access 97.

To try this procedure, change the startup options in the demonstration database, close the database, and open it again. Choose the frmErrorHandling form again and click button 2. Only the End button is available, as shown in Figure 4-11.

Figure 4-11: The disabled Debug button in Access 97.

Unfortunately, in Access 2000 or later, the Allow Viewing Code After Error check box is no longer included in the startup options. To stop the Debug button from appearing when errors occur, you must clear the Use Access Special Keys check box in the Startup dialog. This action leads to some unfortunate circumstances if you have not included an Error Handling section in your procedure.

To demonstrate these unfortunate circumstances, choose Tools ˜ Startup and clear the Allow Access Special Keys check box. Now restart the demonstration database, choose frmErrorHandling , and click button 2. The result: nothing happens. No prompt, no messages. Remarkably, Access doesn't display an error message and the code stops processing on the line that fails. The code under button 2 illustrates pause because a message box that should execute after the erroneous line never appears.

Therefore, the conclusion here is that if you have Access 2000 or later or are considering upgrading your Access 97 database to a later version, you will need to add error handling to all your applications, especially to any application for which you intend to remove the Allow Access Special Keys option. Otherwise, your users will be even more in the dark than usual!

Note	In Chapter 11, I discuss VBA project passwords and the MDE database format as good and great ways to hide your code.

Now that we have seen that error handling is pretty essential, what is the best way to organize your VBA code error handling? We'll address this next .

Writing Good Error Handling Code

Good error handling code results from having a good template and reusing it whenever you write a new procedure. Follow these basic principles:

Always place an On Error GoTo statement before the first line of code.
Place your error handling code at the bottom of the procedure.
Test for and provide specific code for errors that you can easily anticipate or that occur frequently after you deploy the software. I manage this with a Case statement.
Display all other errors with a generic error message by using the Err.Number and Err.Description methods . This way, users can report the error with both a number and a full description. You may even want to log this error in a table.
Once you have completed the error messages, return the code to a common exit point so that the code can close all instantiated objects.

If you are like me, an example of code is always the best way to understand a software topic. To demonstrate the basic principles, look at the code for button 3's onClick event on the frmErrorHandling form that follows . As you can see, a GoTo statement is placed at the top of the procedure to ensure that all errors are trapped. At the bottom of the procedure, I have set up a Case statement to handle specific errors first. In this example, the divide-by-zero error number is 11. I like to define any specific error numbers by using a constant declared at the top of the procedure, which makes the code easier to read. The Case Else statement covers all other errors by displaying a message box that combines the number and its description. On completing the Error Handling section of the procedure, the code returns to the Exit section of the procedure.

 Private Sub cmdError3_Click() ' This subroutine demonstrates error handling. ' The first error is opening a form that doesn't exist ' and the second is a number divided by zero. Dim MyTotal As Double Const DIVIDEBYZERO = 11 On Error GoTo err_cmdError3 MyTotal = 10 MyTotal = 5 * 10 MyTotal = 10 / 0                      ' This line will cause an error. MyTotal = 10 / 5 MsgBox "MyTotal = " & MyTotal, vbInformation, "Mathematics Complete" exit_cmdError3:   Exit Sub err_cmdError3:   Select Case Err.Number     Case DIVIDEBYZERO       MsgBox "Problems with divide by zero -> " & Err.Description, vbInformation     Case vbObjectError + 1       ' To see a line immediately after the error line, press CTRL+BREAK,       ' then drag yellow arrow to Resume Next (below), then press F8.       Resume Next     Case Else       MsgBox "Error No. " & Err.Number & " -> " & Err.Description, vbCritical   End Select   Resume exit_cmdError3 End Sub

That is the basic layout for handling errors in your software. You will have noticed that this layout differed from the error handler code generated by the Access code generation wizards that I explained earlier. If you are writing code for an unimportant part of your database, either layout will suffice. What is important is that you have some error handling in all your VBA code so that Access traps the errors.

Returning Your Code to the Location of the Error

One of the biggest problems with the error handler approach is that it forces the focus of the errors in the debugger to the bottom of the procedure. Then, you generally have to guess where in your code the error occurred. To compensate for this problem, I use a special technique. When the error message appears, I press CTRL+BREAK, which makes the code appear in break mode, as shown in Figure 4-12. In Access 2000 or later, you will need to click the Debug button that appears in a dialog box before you can see the code in debug mode. You will need to have selected the Allow Special Keys startup option for this trick to work.

Figure 4-12: Viewing the code in break mode.

In debug mode, you will see a yellow arrow on the left side of the code window. This arrow highlights the line of code that is about to be processed. You can click and drag the yellow arrow to the line of code that says Resume Next (as illustrated in Figure 4-12). Now press the Step Through Code key (F8), and the program will return to the line of code directly below the line where the error occurred. Now you can debug the error or allow the code to continue processing by using the debug hot keys.

This discussion on error handling and debugging is a big topic, but it is one you don't need to learn all at once. I generally wait until I feel that something is getting repetitive before I look for the next little shortcut or technique. If you use the templates that I have provided and adopt the little debug trick to help you to locate your errors quickly, however, you will be well on the way to writing proficient error handling. The frmErrorHandling form has some other useful error-handling techniques: button 4 shows how to ignore errors in your code by using the Resume Next statement and button 5 demonstrates how a higher-level procedure can trap errors in lower-level subroutines and functions that have no Error Handler section.

That completes the discussion on good programming practices. To facilitate further research on these topics, I encourage you to follow up some of the Internet and Access help topics discussed in the next section.