Database Errors

In VBA, you can trap errors in your procedures by using the special On Error statements:

■ On Error Resume Next On Error GoTo 0

To determine the cause of the error, you need to check the value of the Number property of the VBA Err object. The Description property of the Err object contains the message for the encountered error number.

When using ADO to access data, in addition to the VBA Err object, you can get information about the errors from the ActiveX Data Objects (ADO) Error object. When an error occurs in an application that uses the ADO Object Model, an Error object is appended to the ADO Errors collection of the Connection object and you are advised about the error via a message box.

While the VBA Err object holds information only about the most recent error, the ADO Errors collection can contain several entries regarding the last ADO error. You can count the errors caused by an invalid operation by using the Count property of the Errors collection. By checking the contents of the Errors collection you can learn more information about the nature of the error. The Errors collection is available only from the Connection object. Errors that occur in ADO itself are reported to the VBA Err object. Errors that are provider-specific are appended to the Errors collection of the ADO Connection object. These errors are reported by the specific OLE DB provider when ADO objects are being used to access data.

The DBError procedure in Hands-On 10-14 attempts to open a nonexistent database to demonstrate the capability of the VBA Err object and the ADO Errors collection.

Creating and Manipulating Databases with ADO

(6 Hands-On 10-14: Using the VBA Err Object and ADO Errors Collection

1. In the Visual Basic Editor window, choose Insert | Module.

2. In the module's Code window, type the DBError procedure shown below.

3. Choose Run | Run Sub/UserForm to execute the procedure.

Sub DBError()

Dim conn As New ADODB.Connection Dim errADO As ADODB.Error

On Error GoTo CheckErrors conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" _

& "Data Source=C:\my.mdb" CheckErrors:

Debug.Print "VBA error number: " _ & Err.Number & vbCrLf _ & " (" & Err.Description & ")" Debug.Print "Listed below is information " _ & "regarding this error " & vbCrLf _ & "contained in the ADO Errors collection." For Each errADO In conn.Errors

Debug.Print vbTab & "Error Number: " & errADO.Number Debug.Print vbTab & "Error Description: " & errADO.Description Debug.Print vbTab & "Jet Error Number: " & errADO.SQLState Debug.Print vbTab & "Native Error Number: " & errADO.NativeError Debug.Print vbTab & "Source: " & errADO.Source Debug.Print vbTab & "Help Context: " & errADO.HelpContext Debug.Print vbTab & "Help File: " & errADO.HelpFile Next

MsgBox "Errors were written to the Immediate window." End Sub

Figure 10-3: You can get information about the encountered ADO errors by looping through the Errors collection.


VBA error number. -2147467259


(Could not find file C:\my.mdb')

Listed below is information regarding this efror

contained in the ADO Errors collection.

Error Number: -2147467259

Error Description: Could nol find file "C:\my.mdb'.

Jet Error Number: 3024

Native Error Number: -534576963

Source; Microsoft JET Daiabase Engine

Help Context: 5003024

Help File:|


Was this article helpful?

0 0

Post a comment