ADO Events [Advanced Content]

Not all ADO Power Users realize that there are Events, specifically related to the Connection and Recordset Objects, for which they can write code for. If one is aware of these Events, it is not exactly intuitive as to how you would access them. Below, please find a listing of Events which are exposed by the ADO Recordset and Connection Objects. I will also demonstrate the mechanism by which you, the ADO programmer, can make use of these valuable tools. An in-depth discussion of these Events is well beyond the scope of this Tip. It is solely my intention to make you aware of their existence, and also to demonstrate the means by which you can utilize these critical Events.

1. Events of the ADO Recordset Object:
   1. FetchProgress()
   2. FetchComplete()
   3. WillChangeField()
   4. FieldChangeComplete()
   5. WillMove()
   6. MoveComplete()
   7. EndOfRecordset()
   8. WillChangeRecord()
   9. RecordChangeComplete()
   10. WillChangeRecordset()
   11. RecordsetChangeComplete
   
   
2. Events of the ADO Connection Object:
   1. WillConnect()
   2. ConnectComplete()
   3. Disconnect()
   4. WillExecute()
   5. ExecuteComplete()
   6. BeginTransComplete()
   7. CommitTransComplete()
   8. RollbackTransComplete()
   9. InfoMessage()
   
   
3. How to access ADO Event Procedures. The process involved in accessing ADO Events involves what is called an Event Sink. To sink to an ADO Event, you must:
   1. Set a Reference to the Microsoft ActiveX Data Objects X.X Library.
   2. Create a Class Module, either a stand-alone Class Module or a Form's Class Module.
   3. Add a WithEvents variable to the Module, preferably to the Declarations Section. WithEvents is a KeyWord that specifies that an Object Variable is to be used to respond to Events triggered by an ActiveX Object. Typical WithEvents Declarations follow:
      
      Expand|Select|Wrap|Line Numbers

      1. Private WithEvents cnn As ADODB.Connection
      2. Private WithEvents rst as ADODB.Recordset
   4. Once you add WithEvents to a Class Module, the VBA Editor adds the Event Sink Variable to the editor's drop down Object List. Once you select the Event Sink Variable from the Object drop down, the related Events to which you can respond, appear from the Procedures drop down List. To create one or more Event Procedures for that Event Sink, write code in the Procedure to react to that Event.
      
      Expand|Select|Wrap|Line Numbers

      1. Private Sub cnn_ConnectComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
      2. 'The following code segment exists in a Form's Class Module
      3. 'display a Connected Message and enable specific Controls.
      4. 'disable the Connect Button and enable an Execute Button
      5. Me![lblConnect].Caption = "Connected to SQL Server Godzilla"
      6. Me![cmdConnect].Enabled = False
      7. Me![cmdExecute].Enabled = True
      8. End Sub
   5. Somewhere in your code - for example in the Form's Load Event, instantiate the Event Sink Variable. This step is actually out-of-sequence with the prior step. It was logical to demo the code in the prior step:
      
      Expand|Select|Wrap|Line Numbers

      1. Set rst = New ADODB.Recordset
   6. Somewhere else in your code - for example in the Form's UnLoad Event, destroy the Event Sink by setting it to nothing.
      
      Expand|Select|Wrap|Line Numbers

      1. Set rst = Nothing