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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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?
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.