Only two ADO object class types support events: the Connection and the Recordset.
You can therefore declare object variables of these two types using the WithEvents keyword.
NOTE1 - Further References for the Execute Method:
See more information about the Execute method in "Using
Stored Procedures to Return Records to an Application" in Chapter
9, and the sections under "ADO Data Access Models"
in the next chapter
NOTE2 - Can't Use As New in a Declaration with WithEvents:
You can't use the As New keyword in a declaration that uses the WithEvents keyword.
This means that an object variable doesn't get instantiated when you declare it using WithEvents.
Therefore, when your code is ready to initialize an ADO Connection or Recordset object that has
been declared using WithEvents, you should use a statement of this form:
Set objName = New Class
The Connection Object's Will Events
The Connection object has two Will events:
-
The WillConnect event, which happens just before a connection
to a provider
-
The Execute event, which happens just before a pending
command executes on the current connection.
Both events have an adStatus parameter that enables you to cancel the pending action.
In addition, they have other information that tells you about the settings of the proposed
open connection or command.
Both event procedures are therefore ideal places to validate the pending actions
and cancel them if necessary, as discussed in the following sections.
The WillConnect Event
You can put code in the Connection object's WillConnect event procedure to
monitor information about the pending Connection that's about to be opened to a provider.
You can also validate and, if necessary, cancel the pending connection by setting the adStatus
parameter to a value of adCancel.
The most important parameter of WillConnect is the adStatus parameter, which
you can set to adStatusCancel to stop the pending execution. You can also set adStatus to adStatusUnwantedEvent
to prevent the event from firing again.
Setting the adCancel parameter to adStatusCancel will have no effect if the
original value is adStatusCantDeny.
The other parameters (ConnectionString, UserID, Password, and Options) represent settings
of the current Connection object that's about to open. You can change them here to change
the behavior of the new connection that's about to be opened.
The WillExecute Event
The WillExecute event's name implies a close link to the Execute method
of a Connection object, and it's true that it will fire when a Command object's
Execute method runs.
WillExecute does not just happen when a Connection object's Execute method
runs, however. It also can happen whenever a Recordset object that depends on the current
Connection object is opened, regardless of whether that Recordset object was initialized by a Connection
object's Execute method.
The parameters for WillExecute enable you to examine and change the settings
for the action that will be executed on the provider, and even to cancel the execution altogether.
The most important parameter of WillExecute is the adStatus parameter, which
you can set to adStatusCancel to stop the pending execution. You can also set adStatusCancel to
adStatusUnwantedEvent to prevent the event from firing again.
Setting the adStatus parameter to adStatusCancel will have no effect if the
original value is adStatusCantDeny.
The other parameters (Source, CursorType, LockType, and Options) represent
settings of the current request that's about to execute. You can change them here to
change the behavior of the request and the behavior of any Recordset that may be created.
The Connection Object's Transaction Completion
Events
The more important of these events include the following:
-
BeginTransComplete
-
CommitTransComplete
-
RollbackTransComplete
These events are described in greater detail in the following chapter, "Creating
Data Services: Part II" in the section titled "Managing
Database Transactions."
The Connection Object's ConnectComplete Event
The ConnectComplete event has the following parameters:
-
pError An Error object containing either Nothing
or a description of a connection error (the value of the adStatus parameter will be
adStatusErrorsOccurred in this case).
-
adStatus Can be adStatusOK or adStatusErrorsOccurred,
or adStatusCancel. It's adStatusCancel if the preceding WillConnect event procedure
cancelled the connection. You can also set it to adStatusUnwantedEvent if you don't
want to see this event fire again during the current session.
-
pConnection Not used in VB.
The Connection Object's ExecuteComplete Event
The ExecuteComplete event has the following parameters:
-
pError an Error object containing either Nothing
or a description of a connection error (the value of the adStatus parameter will be
adStatusErrorsOccurred in this case).
-
adStatus Can be adStatusOK or adStatusErrorsOccurred.
You can also set it to adStatusUnwantedEvent if you don't want to see this event
fire again during the current session.
-
pCommand If the action just executed was based on
a Command object, this parameter points to the Command object. Otherwise, its value
is Nothing.
-
pRecordset If the action just executed returned a Recordset,
this parameter points to the Recordset. Otherwise, its value is Nothing.
-
pConnection Not used in VB.
The Disconnect Event
The Disconnect event happens after the Connection is closed with the Close
method or by going out of scope. It takes as its first parameter adStatus, indicating whether
there were errors upon disconnection (adStatusOK or adStatusErrorsOccurred).
Its second parameter points to the current Connection object and is not needed
or used in VB programming. You can put code in the Disconnect event procedure
to perform post-connection cleanup.
