Access функции обработки ошибок

Соседние файлы в предмете Программирование на Delphi

• #
• #
• #
• #

  28.06.201440.36 Mб95Джулипн Б. “Фундаментальные алгоритмы и структуры данных.pdf

• #
• #
• #

  28.06.20145.41 Mб146Тексейра С. “Delphi 5. Руководство разработчика” (1 том).pdf

The Basics

Before delving into actual programming functions and sub-routine, we must first establish what is an error handler.

An error handler is a bit of code which will do pre-defined actions whenever an error occurs. For instance, generate a message to the user or developer describing the nature of the error. For an error handler to be useful, it must provide a minimum of information in its message to the user.

Microsoft’s Error Handler

If ever you allow Microsoft to generate code block, say by converting a macro to VBA, and include their ‘error handler’ you will get something like:

Private Sub Command3_Click()
On Error GoTo Command3_Click_Err

    'The code will be here

Command3_Click_Exit:
    Exit Sub

Command3_Click_Err:
    MsgBox Error$
    Resume Command3_Click_Exit
End Sub

Sadly, this error handler is dismal! It will result in a message box displaying a description of the error, but nothing more. Below is an example of what you can expect from Microsoft’s error handler.

In most cases, this simply isn’t enough information, certainly not for a developer, to properly isolate an issue to remedy it.

Proper Error Handler

So what would be the minimum data to provide in an error handler? Well, that’s a very good question and the answer can vary depending on the environment you are deploying your solution, but, for me, a bare minimum would include things like:

• Error Number
• Error Description
• Error Source

With this in mind, below is an example of a typical error handler I use for code I post on my website. It is a great starting point if you are new to VBA coding/error handling.

On Error GoTo Error_Handler

    'Your code will go here
    
Error_Handler_Exit:
    On Error Resume Next
    Exit {PROCEDURE_TYPE}
    
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: {PROCEDURE_NAME}/{MODULE_NAME}" & vbCrLf & _
           "Error Description: " & Err.Description, vbCritical, _
           "An Error has Occurred!"
    Resume Error_Handler_Exit

A Concrete Example

Sub HelloWorld()
On Error GoTo Error_Handler

    MsgBox "Hello Word!"

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub
    
Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: YourModuleName/HelloWorld" & vbCrLf & _
           "Error Description: " & Err.Description, vbCritical, _
           "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Taking Things Even Further

The basic error handler meets a basic need and is a step forward, but for more complex procedure it still can leave you deep in troubleshooting to determine exactly where in a procedure the error is occurring. This is where adding a Line Number in the Error Handler can greatly help!

Luckily, there is the little-known Erl gem! If we add Line Numbers to our procedures, Erl we in fact return the last executed line number (which should normally be the source of our error).

So with this in mind, we can update the Basic Error Handler to

On Error GoTo Error_Handler

    'Your code will go here
 
Error_Handler_Exit:
    On Error Resume Next
    Exit {PROCEDURE_TYPE}
 
Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: {PROCEDURE_NAME}/{MODULE_NAME}" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit

Notice I use a Switch function so the Error Handler will work with, or without, line numbers being present. The result being that now, when line numbers are added to your procedure you will get something like

Now, we starting to get information that will enable us to quickly identify, isolate and remedy an error! We instantly know the exact source of the error, the error number and description, as well as the line number within the procedure that raised the error. Doesn’t get much better than that.

Centralized Error Function

As any developer knows, it takes time to perfect code. Your Error Handler is no different and will probably go through several iterations until you get it just the way you like it. Now, if you implement the code shown above, then any changes will require you editing each procedure, 1 by 1, to update any changes you wish to implement to your error handler. This, of course, could be very time consuming.

This is why, IMHO, it makes much more sense to create and implement an Error Handling function and using it within your procedure. The end result is simplified procedural code (so we reduce the amount of code in each procedure/module) and set things up so that you can change all error handling in one location with ease.

I first found out about this approach, no this wasn’t my idea by any means, from the great Allen Browne when I read his article: Microsoft Access tips: Error Handling in VBA. One extra advantage of Allen’s approach is he logs the errors to a table enabling you to review them when you wish to and not solely have to rely on a user’s recollection of a popup message.

Over the years, I have used and improved upon Allen’s code, but it is an excellent starting and can easily be used as is in production.

So I mentioned having ‘improved’ upon it and people always ask me how exactly? Everything depends on context, but normally I add to the logging function (not displayed to the user, no need) in things like:

• the computer name
• OS Information (version, build, bitness, …)
• Office/Access Information (version, build, bitness, runtime or not, language, …)
• …

and in certain cases I also add things like

• user’s selected language (for my multi-lingual databases)
• I switch the language of the error MsgBox displayed to the user
• …

In such a function, you can (and I have) gotten into taking screenshots so you can see the form/report/… and see the exact data, and/or send the information by e-mail, change the message for certain specific error to make them more understandable!,…. The possibilities here are endless, add anything that may help you in your job of troubleshooting issue!

Automating Insertion of Your Error Handler

If you are going to be doing some serious vba (MS Access, Word, Excel, …) work and not just a little tinkering, you should most probably seriously consider looking into buying a copy of MZ-Tools as enables you to insert your error handler into any procedure at the click of a button. You can even manage multiple versions of your error handler. Another beautiful feature of using MZ-Tools is the fact that it can automatically include things like the function name, module name, … in your error handler at runtime, eliminating another manual task.

Testing Your Error Handler

Sometimes, it would be nice to be able to trigger an error, for instance, to test your error handler. Nothing could be easier in VBA, we can simply use the Raise method to do so. That’s actually how I produced the images for this article. Here’s a simple example:

Public Function ErrorDemo()
10    On Error GoTo Error_Handler

20        Debug.Print "Line 1"
30        Debug.Print "Line 2"
40        Debug.Print "Line 3"
50        Err.Raise 9
60        Debug.Print "Line 4"
70        Debug.Print "Line 5"
          
Error_Handler_Exit:
80        On Error Resume Next
90        Exit Function
       
Error_Handler:
100       MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
                 "Error Number: " & Err.Number & vbCrLf & _
                 "Error Source: ErrorDemo/mod_Helpers_Errors" & vbCrLf & _
                 "Error Description: " & Err.Description & _
                 Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
                 , vbOKOnly + vbCritical, "An Error has Occured!"
110       Resume Error_Handler_Exit
End Function

which then generates

So on Line 50 we Raise error number 9 and as shown in the image above our error handler properly reports that back to us (and much more!).

So with a single line of code, you can easily test your code at any time you would like.

Where & When To Use Error Handling?!

Now this is one point that various developers have different viewpoints on.

We have to ask ourselves, what is the purpose of error handling? And there are a couple answers here:

• Avoid users being able to debug code when an error occurs so they can’t access the raw VBA code of our application
• Present comprehensible messages to the user when an error occurs
• Avoids Global Variables from being reset when an error occurs

I’ve always view it as you should have error handling in all your procedure (with very, very few exceptions). This makes your application bulletproof, even during development and ultimately there is no down side to implementing as such.

That said, as someone like Albert Kallal explains:

Thus, some argue that since we should be deploying database in a compiled state (mde, accde) that there is no need for error handling. (and no I’m not saying Albert is stating that, I’m not going to put words in his mouth, but his comments explain why some developer believe this)

As stated above, while true, it still doesn’t address these issues during development and testing and ultimately why I stand behind recommending implementing proper error handling throughout. I view it as there is no downside to adding error handling throughout. Furthermore, if you do implement some type of error logging system, you will even gain insight into the runnings of your solution making it even more valuable to add error handling throughout!

Alternative and Other Options

I thought I’d also mention a couple other tools well worth mentioning, mainly Great Access Tools – Access Crash Reporter.

I’ve also seen many people refer to UtterAccess’ Global Error Handler code which can be found at Global Error Handling, so that may also be worth your while to check out.

A Few Resources on the Subject

• Microsoft Access tips: Error Handling in VBA
• MZ-Tools – Productivity Tools for Visual Studio .NET (C#, VB.NET), Visual Basic and VBA
• On Error statement (VBA) | Microsoft Docs
• Error function (Visual Basic for Applications) | Microsoft Docs
• Raise method (Visual Basic for Applications) | Microsoft Docs
• Information.Erl Method (Microsoft.VisualBasic) | Microsoft Docs I couldn’t find any VBA documentation, only this .net that references VB Namespace?!

Автор Allen Browne, июнь 1997 г. Обновлено в сентябре 2009 г..

Оригинал http://allenbrowne.com/ser-23a.html

Перевел с английского Александр Артамонов, ноябрь 2011 г.

Обработка ошибок в VBA

В каждую функция или процедуру следует включать обработку ошибок. Без нее пользователь может оказаться глядящим на дефектный код в открытом редактороме VBA в полной версии Аксесс, а в рантайм версии приложение просто падает.. Более подробно обработка ошибок изложена в статье FMS  Error Handling and Debugging.

Простейший подход состоит в том, чтобы показать сообщение об ошибке и выйти из процедуры. Каждая процедура, таким образом, будет иметь такой формат (без номеров строк):

1 Sub|Function SomeName()
2     On Error GoTo Err_SomeName          ' Инициализировать обработку ошибок.
3     'здесь должен быть ваш код.
4 Exit_SomeName:                          ' Метка для возобновления после ошибки.
5     Exit Sub|Function                   ' Выход до обработки ошибок.
6 Err_SomeName:                           ' Метка для перехода при ошибке.
7     MsgBox Err.Number & Err.Description ' Место для обработчика ошибок.
8     Resume Exit_SomeName                ' возобновить код и выйти.
9 End Sub|Function

Для задач, где есть возможность нескольких ошибок, строки 7-8 нужно заменить более подробным вариантом:

      Select Case Err.Number
      Case 9999                        ' Номер ожидаемой ошибки.
          Resume Next                  ' Чтобы игнорировать вызвавшую ошибку строку
      Case 999
          Resume Exit_SomeName         ' Это используется для выхода из процедуры.
      Case Else                        ' Любая неожиданная ошибка.
          Call LogError(Err.Number, Err.Description, "SomeName()")
          Resume Exit_SomeName
      End Select

Ветка Case Else в этом примере вызывает пользовательскую функцию записи деталей ошибки в таблицу. Это позволит подробнее разобраться уже после того, как ошибка будет очищена. Таблицу можно назвать «tLogError» и состоять она будет из следующих полей:

Below is a procedure for writing to this table. It optionally allows recording the value of any variables/parameters at the time the error occurred. You can also opt to suppress the display of information about the error.

Ниже процедура для записи в эту таблицу. Она опционально позволяет записывать значения переменных/параметров во время возникновения ошибки. Можно также подавить вывод информации об ошибке на экран.

Function LogError(ByVal lngErrNumber As Long, ByVal strErrDescription As String, _
    strCallingProc As String, Optional vParameters, Optional bShowUser As Boolean = True) As Boolean
On Error GoTo Err_LogError
    ' Назначение: Обобщенный обработчик ошибок.
    ' Записывает ошибки в таблицу "tLogError".
    ' Аргументы: lngErrNumber - значение Err.Number
    ' strErrDescription - значение Err.Description
    ' strCallingProc - имя процедуры/функции, сгенерировавшей ошибку.
    ' vParameters - необязательный параметр-строка: список параметров для записи в таблицу.
    ' bShowUser - необязательный параметр boolean: если False, подавляет вывод сообщения на экран.
    ' Автор: Allen Browne, allen@allenbrowne.com Комментарии перевел Александр Артамонов
    Dim strMsg As String      ' Строковое значение для отображения в MsgBox
    Dim rst As DAO.Recordset  ' таблица tLogError
    Select Case lngErrNumber
    Case 0
        Debug.Print strCallingProc & " called error 0."
    Case 2501                ' Cancelled
        'Do nothing.
    Case 3314, 2101, 2115    ' Can't save.
        If bShowUser Then
            strMsg = "Record cannot be saved at this time." & vbCrLf & _
                "Complete the entry, or press <Esc> to undo."
            MsgBox strMsg, vbExclamation, strCallingProc
        End If
    Case Else
        If bShowUser Then
            strMsg = "Error " & lngErrNumber & ": " & strErrDescription
            MsgBox strMsg, vbExclamation, strCallingProc
        End If
        Set rst = CurrentDb.OpenRecordset("tLogError", , dbAppendOnly)
        rst.AddNew
            rst![ErrNumber] = lngErrNumber
            rst![ErrDescription] = Left$(strErrDescription, 255)
            rst![ErrDate] = Now()
            rst![CallingProc] = strCallingProc
            rst![UserName] = CurrentUser()
            rst![ShowUser] = bShowUser
            If Not IsMissing(vParameters) Then
                rst![Parameters] = Left(vParameters, 255)
            End If
        rst.Update
        rst.Close
        LogError = True
    End Select
Exit_LogError:
    Set rst = Nothing
    Exit Function
Err_LogError:
    strMsg = "В программе возникла непредвиденная ситуация." & vbCrLf & _
        "Пожалуйста запишите следующую информацию:" & vbCrLf & vbCrLf & _
        "Вызывающая процедура: " & strCallingProc & vbCrLf & _
        "Номер ошибки " & lngErrNumber & vbCrLf & strErrDescription & vbCrLf & vbCrLf & _
        "Запись в таблицу невозможна из-за Ошибки " & Err.Number & vbCrLf & Err.Description
    MsgBox strMsg, vbCritical, "LogError()"
    Resume Exit_LogError
End Function

Замечания по функции:

1. Для Аксесса 1 или 2, используйте Access Basic error handler.
2. Возвращаемое значение служит только с целью показать, успешно ли функция записала в лог ошибку.
3. Возможные расширения: Поскольку таблица tErrorLog у вас уже открыта, вы могли бы посчитать последние ошибки и подавить вывод на экран одного и того же сообщения несколько раз, или отменить повторные ошибки блокировки.