ADO - ADO Event Handling

ADO (ActiveX Data Objects) provides an event-driven programming model that allows applications to respond automatically when certain actions occur during database operations. Instead of continuously checking whether an operation has completed or an error has occurred, developers can write event procedures that are triggered whenever specific database-related events take place. This approach makes applications more interactive, responsive, and easier to manage.

Event handling is especially useful in applications that work with large databases, remote servers, or operations that take noticeable time to complete. Developers can monitor database connections, query execution, record retrieval, and updates without interrupting the normal flow of the application.

Understanding ADO Events

An event is a notification generated by an ADO object whenever a particular action begins or ends. These notifications allow the application to execute custom code in response to database activities.

For example:

  • A connection is about to open.

  • A SQL query has finished executing.

  • Records have been fetched from the database.

  • A record is about to be modified.

  • An error occurs during a database operation.

Instead of manually checking each operation, ADO automatically calls the appropriate event procedure.

Types of ADO Events

ADO events are mainly associated with two important objects:

Connection Events

These events monitor activities related to establishing and managing a database connection.

Examples include:

  • Before a connection opens

  • After a connection opens

  • Before a connection closes

  • After a connection closes

  • Before a transaction begins

  • After a transaction commits

  • After a transaction rolls back

  • When a command executes

  • When an error occurs

Connection events are useful for logging database activity, validating operations, or preventing unwanted actions.

Recordset Events

These events monitor activities related to data stored inside a Recordset.

Examples include:

  • Before moving to another record

  • After moving to another record

  • Before inserting a record

  • After inserting a record

  • Before updating a record

  • After updating a record

  • Before deleting a record

  • After deleting a record

  • Before changing field values

  • After changing field values

These events help enforce business rules and validate data before changes are saved.

Event Categories

ADO events generally occur in two stages.

Before Events

These events occur before an operation actually happens.

Examples include:

  • WillConnect

  • WillExecute

  • WillMove

  • WillChangeField

  • WillChangeRecord

  • WillChangeRecordset

During these events, the programmer can:

  • Validate user input.

  • Cancel an operation.

  • Modify parameters.

  • Display warning messages.

  • Prevent unauthorized actions.

After Events

These events occur after the operation has successfully completed.

Examples include:

  • ConnectComplete

  • ExecuteComplete

  • MoveComplete

  • FetchComplete

  • RecordChangeComplete

  • FieldChangeComplete

These events are useful for:

  • Displaying confirmation messages.

  • Updating the user interface.

  • Logging successful operations.

  • Refreshing displayed data.

Common Connection Events

WillConnect

This event occurs immediately before the database connection is established.

It allows developers to:

  • Verify connection parameters.

  • Check server availability.

  • Cancel the connection if necessary.

Example use cases:

  • Checking whether the server name is correct.

  • Displaying a "Connecting to database" message.

  • Preventing unauthorized users from connecting.

ConnectComplete

This event occurs after the connection has been successfully established.

Typical tasks include:

  • Displaying a success notification.

  • Enabling application controls.

  • Loading initial data.

  • Recording connection time.

If the connection fails, error information becomes available for troubleshooting.

WillExecute

This event occurs before an SQL statement or stored procedure is executed.

Developers may:

  • Review the SQL statement.

  • Modify query parameters.

  • Validate input.

  • Cancel dangerous operations.

For example, before executing a DELETE statement, the application may ask the user for confirmation.

ExecuteComplete

This event occurs immediately after a SQL command finishes executing.

This event can be used to:

  • Display completion messages.

  • Update reports.

  • Refresh data grids.

  • Calculate execution time.

  • Record successful database operations.

For example, after inserting a new employee record, the application can automatically refresh the employee list.

Common Recordset Events

WillMove

Occurs before moving from one record to another.

Possible uses include:

  • Checking for unsaved changes.

  • Asking users whether they want to save modifications.

  • Preventing accidental navigation.

MoveComplete

Occurs after successfully moving to another record.

Typical applications include:

  • Updating navigation buttons.

  • Displaying current record information.

  • Refreshing calculated fields.

WillChangeField

Occurs before changing a field value.

Developers often use this event to validate data.

For example:

  • Prevent negative salary values.

  • Reject invalid email addresses.

  • Ensure required fields are not empty.

If validation fails, the update can be cancelled.

FieldChangeComplete

Occurs after a field has been successfully updated.

Possible actions include:

  • Updating totals.

  • Refreshing dependent controls.

  • Logging changes.

  • Displaying confirmation messages.

WillChangeRecord

Occurs before an entire record is modified.

Typical validation includes:

  • Checking duplicate records.

  • Verifying mandatory fields.

  • Confirming user permissions.

RecordChangeComplete

Occurs after a record has been successfully inserted, updated, or deleted.

Applications commonly use this event to:

  • Refresh displayed records.

  • Update statistics.

  • Notify users.

  • Synchronize data with other components.

FetchComplete Event

The FetchComplete event occurs after ADO has finished retrieving all requested records from the database.

This event is especially useful when working with:

  • Large datasets

  • Remote databases

  • Asynchronous record retrieval

Typical uses include:

  • Hiding loading indicators.

  • Displaying the total number of records retrieved.

  • Enabling search and filtering features.

  • Updating progress information.

For example, if a customer database contains thousands of records, the application can display a loading message while data is being fetched. Once retrieval is complete, the FetchComplete event removes the loading message and displays the records.

Error Event

The Error event is triggered whenever an error occurs during a database operation.

Common causes include:

  • Invalid SQL syntax.

  • Lost database connection.

  • Permission denied.

  • Constraint violations.

  • Incorrect table or column names.

  • Data type mismatches.

Developers can use this event to:

  • Display user-friendly error messages.

  • Log technical error details.

  • Retry failed operations.

  • Notify administrators.

  • Roll back transactions if necessary.

Effective error handling improves application reliability and helps users understand what went wrong without exposing complex technical details.

Advantages of ADO Event Handling

ADO event handling offers several benefits:

  • Reduces the need for constant status checking through event-driven notifications.

  • Improves application responsiveness by reacting immediately to database activities.

  • Supports data validation before changes are committed.

  • Enables centralized error handling and logging.

  • Enhances security by allowing operations to be intercepted and cancelled when necessary.

  • Simplifies maintenance by separating database events from core application logic.

  • Facilitates monitoring of long-running operations and user interactions.

  • Provides better control over transactions, record updates, and connection management.

Practical Applications

ADO event handling is widely used in business applications where monitoring and controlling database operations is essential. Banking systems use events to validate transactions before they are committed and to log successful operations for auditing purposes. Inventory management applications use recordset events to ensure stock levels remain accurate whenever products are added, updated, or removed. Hospital management systems rely on events to verify patient information before records are modified and to notify staff when updates are completed. Customer relationship management (CRM) systems use events to refresh customer data after changes and to handle errors gracefully during communication with remote databases. By responding automatically to important database activities, ADO event handling helps create reliable, efficient, and user-friendly database applications.