ADO Connection checks

How do you implement an event handler in VB to check to see if a given ADO connections is still valid ?
Who is Participating?
samopalConnect With a Mentor Commented:
Use events model of Connection object

Dim WithEvents connEvent as Connection
Dim conn as New Connection
set connEvent = conn        ' Enable event support.
set connEvent = Nothing    ' Disable event support.
Private Sub connEvent_ConnectComplete(ByVal err as ADODB.Error, & _
adStatus as ADODB.EventStatus, ByVal pConnection as ADODB.Connection)
' Check status here...
End Sub

I am assuming you want to know the 'state' of the ADO connection object.  If so you could use the Connection object's State property which will return the following:

adStateClosed 0 Indicates that the object is closed.
adStateOpen 1 Indicates that the object is open.
adStateConnecting 2 Indicates that the object is connecting.
adStateExecuting 4 Indicates that the object is executing a command.
adStateFetching 8 Indicates that the rows of the object are being retrieved.
steve_gulloAuthor Commented:
I used the State property in a Timer loop but it didn't seem to change even after I stopped the SQL server I'm testing with. I also tried Error collections and that didn't seem to change either.
Why not call a query on that Connection and if the recordset throws an error..the connection is invalid, and put that query in the timer...

Just a thought
steve_gulloAuthor Commented:
Yes that would work, but there are modules in my code that perform Transactional updates against the SQL server. Would a timed query, resulting in a recordset, effect my active transactions?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.