The Errors Collection

The Errors Collection

The Errors collection lets you trap some but not all errors that occur in an ADO application. The members of this collection let you examine errors returned from an OLE data 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 of all previous errors. ADO errors, as opposed to data provider errors, enter the Visual Basic for Applications (VBA) Err object rather than the Errors collection. The Errors collection is most appropriate for handling connection-based errors returned from a remote database through the database's OLE data provider. Because the Errors collection belongs to the Connection object, you need an explicit Connection object to examine errors returned by a data provider.

Note  

The members of ErrorValueEnum represent a complete list of ADO errors. You can learn more about these errors from the Object Browser.

Error Object Properties

The Error objects in the Errors collection have six properties to 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 that you've already seen in VBA error traps. The Error object's Number and Description 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 error messages originating from the database server to which you submit your request concerning errors in your SQL statement syntax. Providers can also return HelpFile and HelpContext properties for error objects. Since these properties work together, I count them as one property. Furthermore, HelpFile and HelpContext are not always available. A zero-length string for an error's HelpFile property signals that the two properties are not available.

Generating and Trapping Error and Err Objects

The OpenLookOnlySQLErrors procedure shown next is an adaptation of the OpenLookOnly procedure in Chapter 1 that reveals the impact of the Connection object's Mode property. This update uses a connection to a computer running SQL Server instead of a local Jet database, and it includes advice in comments on how to create various errors. Implementing these errors and stepping through the procedure will help you understand how the ADO Errors collection and the VBA Err object complement one another. A read-only setting for the procedure (as it appears here) generates an error when you attempt to update a database. Interestingly, this error does not become part of the Errors collection. You can confirm this by stepping through the OpenLookOnlySQLErrors procedure. The Err object traps the error instead.

Note  

Database connection errors that generate Error objects propagate through to the VBA Err object. For example, the last member of the Errors collection also appears in the Err object. This confirms that one or more provider errors propagate to both the Errors collection and the VBA Err object. When the Err object does not provide sufficient context for you to fix an error, use the Errors collection for more detail on the cause of the error.

 SubOpenLookOnlySQLErrors() Dimcnn1AsConnection Dimrst1AsRecordset DimerrLoopAsError,intInErrorsAsInteger OnErrorGoToLookOnlyTrap     'ThissampleassumesyouhaveNorthwindCSontheCabSony1 'SQLServerinstanceorwhateverotherinstanceyouuse 'inplaceofit Setcnn1=NewADODB.Connection cnn1.Mode=adModeRead cnn1.Open"Provider=sqloledb;DataSource=CabSony1;"&_ "InitialCatalog=NorthwindCS;UserId=sa;Password=password;"     'SpellNorthwindCSincorrectlytoforceVBAerroronly 'cnn1.Open"Provider=sqloledb;DataSource=CabSony1;"&_ "InitialCatalog=NorthwindsCS;UserId=sa;Password=password;"     'SpellsqloledbincorrectlytoforceVBAandprovidererror 'cnn1.Open"Provider=sqloledbs;DataSource=CabSony1;"&_ "InitialCatalog=NorthwindCS;UserId=sa;Password=password;"     'ManuallystopSQLServertoforceVBAandprovidererror     'SpelluserIDincorrectlytogenerateVBAandprovidererrors 'cnn1.Open"Provider=sqloledb;DataSource=CabSony1;"&_ "InitialCatalog=NorthwindCS;UserId=sas;Password=password;"     Setrst1=NewADODB.Recordset rst1.ActiveConnection=cnn1 'Spellrst1incorrectlytoforcea424VBAerror 'Spellcnn1ascnntoforcea3001VBAerror 'rst.ActiveConnection=cnn1 'rst1.ActiveConnection=cnn     'Spelltablenameas"Customer"toforceVBAandprovider 'errorthatequals2147217900 rst1.Open"Customers" 'rst1.Open"Customer"     'adModeReadsettingforcnn1.ModecausesaVBAerror 'of3251here;removecnn1.Modelinetoenableupdates rst1.Fields("CustomerID")="xxxxx" rst1.Update Debug.Printrst1.Fields("CustomerID") rst1.Close     LookOnlyTrap: 'Loopthroughandprintprovidererrors ForEacherrLoopIncnn1.Errors Debug.PrinterrLoop.Number,errLoop.Description NexterrLoop     'PrintVBAErrobject Debug.Printerr.Number,err.Description     EndSub 

Review the comments throughout the program to see how to generate a selection of errors that creates ADO errors exclusively for the Err object or provides errors for both the Err object and the Errors collection. Commenting the first Open method and removing the comment marker from the second use of the Open method generates an attempt to link to an invalid database. (The Initial Catalog parameter spells the database name incorrectly.) This flaw causes a provider error that populates the Errors collection and writes the same error number and message to the VBA Err object.