The Errors collection
The Errors collection lets you trap some, but not all, errors that occur in an ADO application. It also returns errors from an OLE DB provider. A single error condition can return multiple errors, each of which causes a new Error object to be placed in the Errors collection. Some errors cause a program termination; others do not. A new failure automatically clears the Errors collection for the entry of errors associated with it. Some ADO errors enter the Err object rather than Errors collection, but you might want to use the latter collection as well. The Errors collection is most appropriate for handling connection-based errors returned from a remote database through its OLE DB provider.
The Error objects in the Errors collection have five properties that help you gather more information so that you can respond to them with program logic. The Number and Description properties parallel those for the Err object. These properties complement one another. The Number property returns a unique number that identifies an error, and the Description property returns a brief string that describes the error. The NativeError property offers a provider-specific error code. If you often work with a particular provider, this property might provide useful information about how to resolve an error. The Source property names the object or application that originated the error. The SQLState property can contain SQL statement syntax error messages originating from the database server to which you submit your request.
The OpenLookOnly Errors procedure below is an adaptation of an earlier procedure that reveals the impact of the Connection object's Mode property. A read-only setting for this property causes an error to be generated when you attempt to update a database. Interestingly, this error does not become part of the Errors collection. You can trap the error and respond to it using the Err object. The last member of the Errors collection also appears in the Err object. The error-trapping logic at the end of the procedure avoids printing two lines with an identical number and description.
Sub OpenLookOnlyErrors() Dim cnn1 As New Connection Dim rsCustomers As Recordset Dim errLoop As Error, intInErrors As Integer On Error GoTo LookOnlyTrap cnn1.Mode = adModeRead cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Program Files\Microsoft Office\Office\" & _ "Samples\Northwind.mdb;"'Spell Northwind incorrectly to generate trappable error. ' cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Program Files\Microsoft Office\Office\" & _ "Samples\Northwinds.mdb;"'No Errrors element for faulty provider spelling ' cnn1.Open "Provider=Microsoft.Jets.OLEDB.4.0;" & _ "Data Source=C:\Program Files\Microsoft Office\Office\" & _ "Samples\Northwind.mdb;" Set rsCustomers = New ADODB.Recordset rsCustomers.ActiveConnection = cnn1'Spell rsCustomers incorrectly to make a 424 non-tappable error. 'Spell cnn1 as cnn to make 3001 non-trappable error. ' rsCustomer.ActiveConnection = cnn1'Spell table name as "Customer" to make -2147217900 trappable error. rsCustomers.Open "Customers"'adModeRead setting for cnn1.Mode causes an error (3251) here. 'Comment out cnn1.Mode line to enable updates. rsCustomers.Fields("CustomerID") = "xxxxx" rsCustomers.Update Debug.Print rsCustomers.Fields("CustomerID") rsCustomers.CloseLookOnlyTrap: intInErrors = 0 For Each errLoop In cnn1.Errors Debug.Print errLoop.Number, errLoop.Description intInErrors = intInErrors + 1 Next errLoop If intInErrors = 0 Then Debug.Print Err.Number, Err.Description End IfEnd Sub |
The OpenLookOnly Errors procedure creates several different types of errors and attempts to write to a read-only connection. Figure 2-9 shows the VBE Code and Immediate windows for these errors. The messages with large, negative error codes are from the Errors collection. The remaining errors are ADO errors that report through the Err object. Only two of the errors have the large negative numbers characteristic of the Errors collection. The remaining errors are ADO errors that are available through the Err object. One error within the connection string (error number 3706) still did not report through the Errors collection. This, plus the fact that the last member of the Errors collection appears in the Err object, points to usefulness of error trapping with the Err object. This same design works for VBA errors.
Figure 2-9. The VBE Code and Immediate windows showing error codes from typical kinds of errors.
NOTE
You can insert an Option Explicit statement in the general declarations area of a module to eliminate the possibility of certain errors, such as references to objects that do not exist.
The LoopToUsingErrors procedure below offers some alternative approaches to error processing with the Err object. The procedure generates one error and includes a comment that details the modifications to the code that are necessary to create another error. It also responds specifically to two errors and includes a general error handling routine for all others:
In the case of a 3251 error, the
procedure changes the lock type so that the recordset is updatable. This error
occurs because the lock type is wrong. To fix the problem, the error-processing
code closes the old recordset, resets the LockType property, and reopens
the recordset object.
With a 424 error, the procedure does
not try to fix the error but alerts the user to the potential cause of the
problem. This error occurs when a method is invoked or a property is set
against a variable not declared as an object. For example, a typographical
error can cause this problem.
If the error does not have a 3251 or
424 error number, the routine prints out the number and description properties
for the Err object.
Sub LoopToUsingErrors() On Error GoTo DErrorsTrap Dim cnn1 As Connection Dim rsMyTable As Recordset Set cnn1 = New ADODB.Connection cnn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Program Files\Microsoft Office\Office\" & _ "Samples\Northwind.mdb;" Set rsMyTable = New ADODB.Recordset'Open recordset with defaults. OpenRSMyTable: rsMyTable.Open "MyTable", cnn1'Loop through recordset. Do Until rsMyTable.EOF'Make 424 error by using next instead of preceding line. ' Do Until rsMyTables.EOF If rsMyTable.Fields(0) = 4 Then'This line makes 3251 error because recordset is read-only. rsMyTable.Fields(0) = 88 Else Debug.Print rsMyTable.Fields(0), rsMyTable.Fields(1) End If rsMyTable.MoveNext Loop rsMyTable.Close ErrorsExit: Exit SubDErrorsTrap: If Err.Number = 3251 Then MsgBox "OLEDB Provider does not support operation. " & _ "Find another way to get the job done or get a new " & _ "OLEDB Provider. Error happened in LoopToDeleteErrors." Debug.Print rsMyTable.LockType rsMyTable.Close rsMyTable.LockType = adLockOptimistic Resume OpenRSMyTable ElseIf Err.Number = 424 Then MsgBox "The code tried to do something requiring an " & _ "object, such as set a property or invoke a method, " & _ "but the code did not have an object. Check spelling." Else MsgBox "Check Immediate window for error # and desc." Debug.Print Err.Number, Err.Description End If Resume ErrorsExitEnd Sub |