ADO - ADO Connection Events and Event Handling

ADO Connection Events are notifications generated by an ADO Connection object when certain activities or changes occur during a database connection. They allow an application to respond automatically to events such as opening a connection, closing a connection, beginning a transaction, committing a transaction, or encountering an error.

Instead of continuously checking the connection status manually, an application can use event handlers. When a particular event occurs, ADO automatically calls the corresponding event procedure. This makes applications more responsive and provides a structured way to monitor database operations.

1. What is an ADO Connection Event?

An ADO Connection Event is an event raised by the ADO Connection object.

For example, when an application opens a database connection, the ConnectComplete event can notify the application that the connection attempt has finished. Similarly, the Disconnect event can notify the application that the connection has been closed.

A simplified flow is:

Application
     |
     | Open Connection
     v
ADO Connection
     |
     | Connection operation completed
     v
ConnectComplete Event
     |
     v
Event Handler Executes

This event-based approach is particularly useful when database operations may take some time or when an application needs to monitor connection activity.

2. Important ADO Connection Events

Some important events associated with the ADO Connection object include:

  • ConnectComplete

  • Disconnect

  • InfoMessage

  • WillConnect

  • WillExecute

  • ExecuteComplete

  • BeginTransComplete

  • CommitTransComplete

  • RollbackTransComplete

Each event occurs at a different stage of a database operation.

3. ConnectComplete Event

The ConnectComplete event occurs after an attempt to establish a database connection has completed.

The event can be used to determine whether the connection was established successfully or whether an error occurred.

For example:

Private Sub Connection1_ConnectComplete(ByVal pError As ADODB.Error, _
    adStatus As ADODB.EventStatusEnum, _
    ByVal pConnection As ADODB.Connection)

    If adStatus = adStatusOK Then
        MsgBox "Connection established successfully."
    Else
        MsgBox "Unable to establish database connection."
    End If

End Sub

The event provides information about the operation through parameters such as the error object and event status.

A common use is displaying an appropriate message to the user or recording connection failures in a log.

4. Disconnect Event

The Disconnect event occurs when an ADO connection is disconnected.

For example:

Private Sub Connection1_Disconnect( _
    ByVal adStatus As ADODB.EventStatusEnum, _
    ByVal pConnection As ADODB.Connection)

    MsgBox "Database connection has been closed."

End Sub

This can be useful for performing cleanup activities when the connection is closed.

For example, an application could:

  • Release related resources.

  • Update application status.

  • Write an entry to a log.

  • Notify other components that the database is no longer available.

5. InfoMessage Event

The InfoMessage event is used when the database provider sends informational messages or warnings.

It is different from a normal fatal database error. Some database systems can return messages that provide additional information about an operation without causing the operation to fail.

Example:

Private Sub Connection1_InfoMessage( _
    ByVal pError As ADODB.Error, _
    ByVal adStatus As ADODB.EventStatusEnum, _
    ByVal pConnection As ADODB.Connection)

    MsgBox pError.Description

End Sub

This can be useful when an application needs to capture provider-generated messages.

6. WillConnect Event

The WillConnect event occurs before ADO attempts to establish the connection.

This event provides an opportunity to examine or influence the connection process.

For example:

Private Sub Connection1_WillConnect( _
    ConnectionString As String, _
    UserID As String, _
    Password As String, _
    Options As Long, _
    ByVal adStatus As ADODB.EventStatusEnum, _
    ByVal pConnection As ADODB.Connection)

    MsgBox "Attempting to connect to the database."

End Sub

The event can be useful for monitoring connection attempts.

An application can also use event status information to determine whether an operation should continue.

7. WillExecute Event

The WillExecute event occurs immediately before ADO executes a command.

It can be used to monitor database commands before they are sent to the provider.

For example:

Private Sub Connection1_WillExecute( _
    Source As String, _
    CursorType As ADODB.CursorTypeEnum, _
    LockType As ADODB.LockTypeEnum, _
    Options As Long, _
    ByVal adStatus As ADODB.EventStatusEnum, _
    ByVal pCommand As ADODB.Command, _
    ByVal pRecordset As ADODB.Recordset, _
    ByVal pConnection As ADODB.Connection)

    Debug.Print "Executing: " & Source

End Sub

This can be particularly useful for debugging and monitoring database activity.

For example, a developer could use it to identify which SQL statement is being executed when an application produces unexpected results.

8. ExecuteComplete Event

The ExecuteComplete event occurs after an ADO command has finished executing.

This event can be used to determine whether execution was successful and to inspect any errors produced during execution.

Example:

Private Sub Connection1_ExecuteComplete( _
    ByVal RecordsAffected As Long, _
    ByVal pError As ADODB.Error, _
    ByVal adStatus As ADODB.EventStatusEnum, _
    ByVal pCommand As ADODB.Command, _
    ByVal pRecordset As ADODB.Recordset, _
    ByVal pConnection As ADODB.Connection)

    If adStatus = adStatusOK Then
        Debug.Print "Command executed successfully."
    Else
        Debug.Print "Command execution failed."
    End If

End Sub

This is useful when an application needs to perform some action after a command has completed.

9. Transaction-Related Events

ADO also provides events associated with transactions.

The important transaction events include:

BeginTransComplete
CommitTransComplete
RollbackTransComplete

BeginTransComplete occurs after a transaction begins.

CommitTransComplete occurs after a transaction is committed.

RollbackTransComplete occurs after a transaction is rolled back.

For example:

Private Sub Connection1_CommitTransComplete( _
    ByVal pError As ADODB.Error, _
    ByVal adStatus As ADODB.EventStatusEnum, _
    ByVal pConnection As ADODB.Connection)

    If adStatus = adStatusOK Then
        MsgBox "Transaction committed successfully."
    End If

End Sub

These events are useful when an application needs to monitor transaction completion.

10. Event Status

ADO events commonly use an EventStatusEnum value to indicate the status of an event.

A typical event handler checks whether the operation was successful:

If adStatus = adStatusOK Then
    'Operation completed successfully
Else
    'Operation encountered a problem
End If

The event status mechanism is important because an event handler should not assume that every database operation has succeeded.

11. Why Connection Events Are Useful

Connection events provide several advantages.

First, they allow applications to monitor database activity without repeatedly checking the connection manually.

Second, they make error handling more organized. Instead of placing error-checking code throughout an application, connection-related problems can be handled through appropriate event procedures.

Third, they are useful for debugging. Events such as WillExecute and ExecuteComplete can help developers understand when commands are being sent to the database and when they finish.

Fourth, connection events can help with application logging. An application can record connection attempts, command execution, disconnections, and transaction results.

12. Example of Event-Based Database Monitoring

Consider an application that retrieves customer information.

The sequence could be:

Application starts
       |
       v
WillConnect
       |
       v
Database connection attempted
       |
       v
ConnectComplete
       |
       v
SQL command executed
       |
       v
WillExecute
       |
       v
Database processes command
       |
       v
ExecuteComplete
       |
       v
Recordset returned
       |
       v
Connection closed
       |
       v
Disconnect

This sequence demonstrates how multiple events can be used to monitor the complete lifecycle of database operations.

13. Event Handling and Error Management

Connection events should not replace normal error handling. They complement it.

For example, an application can use conventional error handling for unexpected programming errors while using ADO events to monitor database-specific operations.

A good application should distinguish between:

Connection failure
Command failure
Provider information message
Transaction failure
Application programming error

Treating all of these situations as the same type of error can make debugging difficult.

14. Connection Events vs. Manual Checking

Without events, an application might repeatedly check whether an operation has completed:

Check connection
Check again
Check again
Check again
Process result

With event handling:

Start operation
       |
       v
ADO performs operation
       |
       v
Event occurs
       |
       v
Event handler processes result

The second approach provides a cleaner event-driven programming model.

15. Practical Applications

ADO Connection Events can be useful in applications such as:

  • Customer management systems

  • Inventory applications

  • Banking applications

  • Reporting systems

  • Administrative applications

  • Data-entry systems

  • Database monitoring utilities

  • Enterprise applications

For example, an inventory application could use ConnectComplete to confirm that the database is available, WillExecute to monitor important queries, ExecuteComplete to determine whether an update succeeded, and Disconnect to perform cleanup.

Conclusion

ADO Connection Events provide an event-driven mechanism for monitoring and responding to database connection activities. Events such as ConnectComplete, Disconnect, InfoMessage, WillConnect, WillExecute, and ExecuteComplete allow developers to respond at different stages of database operations. Transaction events such as BeginTransComplete, CommitTransComplete, and RollbackTransComplete provide additional control over transaction monitoring.

Understanding these events is important for building well-structured ADO applications because they improve monitoring, debugging, error handling, logging, and overall control of database operations.