Access™ 2007 VBA Programmer's Reference

7.8. Using ADO Events

The ADO Connection and Recordset objects support several events for a variety of operations. These events won't interrupt your code, and can be more accurately pictured as notifications, which are actually a call to an event procedure that you define in your code, much like a text box's AfterUpdate event.

ADO object events aren't always so important for synchronous operations because your code waits for the operation to complete before proceeding. They can be important for asynchronous operations, however, because there's no way of telling when the operation will complete.

For example, say you execute the following code to open an asynchronous connection against a SQL Server database that resides on the other side of the country:

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

' Open an asynchronous connection
cn.CursorLocation = adUseServer
cn.Open CurrentProject.Connection, , , adAsyncConnect
rs.Open "vwSomeView", cn

Just for fun, say the network runs at 9600 baud. With such a slow network speed, getting connected takes considerable time. Naturally enough, an error will occur when you try to open the recordset, because the connection will not have opened by the time you execute the recordset's Open method.

To account for this possibility, you can use the Connection object's ConnectComplete event. But before you do that, you must declare the Connection object using the WithEvents keyword.

Private WithEvents cn As ADODB.Connection

Remember, however, ...