ADO - Errors Collection

In ADO (ActiveX Data Objects), the Errors Collection is a built-in collection that contains detailed error information generated by the most recent ADO operation involving a Connection object.

It allows you to inspect multiple errors returned by the data provider (for example, SQL Server or Oracle) — not just the last one.

Purpose

When an ADO operation (like opening a connection, executing a command, or updating data) fails or generates warnings,
the ADO Errors collection captures one or more error objects that describe what happened.

Each item in the collection is an Error object that provides specific details about the problem.

Belongs To

The Errors collection is a property of the Connection object:

Connection.Errors

Common Use

You use the Errors collection inside an error-handling routine to find out:

What went wrong
Where it happened
What number or description the provider returned

Structure

Each Error object in the collection contains the following important properties:

Property	Description
`Number`	Numeric error code returned by the provider
`Description`	Text description of the error
`Source`	Source of the error (e.g., "Microsoft OLE DB Provider for SQL Server")
`SQLState`	Standard SQL state code (used by ODBC providers)
`NativeError`	Database-specific error code
`HelpFile` / `HelpContext`	Path and context ID for related help topics

Example: Handling Multiple ADO Errors

On Error GoTo ADOErrorHandler

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection

conn.Open "Provider=SQLOLEDB;Data Source=.;Initial Catalog=TestDB;User ID=sa;Password=wrongpass"

Exit Sub

ADOErrorHandler:
Dim errObj As ADODB.Error

' Loop through all errors in the collection
For Each errObj In conn.Errors
    Debug.Print "Error #" & errObj.Number
    Debug.Print "Description: " & errObj.Description
    Debug.Print "Source: " & errObj.Source
    Debug.Print "SQLState: " & errObj.SQLState
    Debug.Print "NativeError: " & errObj.NativeError
    Debug.Print "-----------------------------"
Next

conn.Errors.Clear   ' Clear after handling
Resume Next

Output Example:

Error #18456
Description: Login failed for user 'sa'.
Source: Microsoft OLE DB Provider for SQL Server
SQLState: 28000
NativeError: 18456
-----------------------------

Key Methods

Method	Description
`Clear`	Removes all Error objects from the collection (use after handling errors).

Example:

conn.Errors.Clear

When the `Errors` Collection is Populated

Action	Collection Updated?
Connection fails to open	✅ Yes
Command execution fails	✅ Yes
Recordset operation fails	✅ Yes
Disconnected Recordset errors	❌ No (use normal VB error handling)

After any ADO operation, check Connection.Errors.Count to see if any errors occurred.

Important Notes

The VB Err object captures only the first ADO error.
But ADO providers can generate multiple errors, so use the Errors collection to get all of them.
Always use Connection.Errors.Clear after handling, to prevent confusion from old errors.
The Errors collection is automatically cleared each time a new ADO operation occurs on that connection.

Summary

Feature	Description
Collection Owner	`Connection` object
Contains	One or more `Error` objects
Used For	Getting detailed information about ADO errors
Cleared Automatically?	Yes (after each ADO operation)
Manual Clear?	`Connection.Errors.Clear`

Example: Display All Errors

Dim errObj As ADODB.Error
For Each errObj In conn.Errors
    MsgBox "Error " & errObj.Number & ": " & errObj.Description
Next