Check whether SQL Server connection is still open
Posted on 2010-11-26
I am connecting to my SQL Server on my web hosting account from Excel VBA as shown below.
I need to keep the connection open because it takes about 10 secs to connect each time. (That, I hope is ok)
So I want to check whether the connection is still open when the user requests another query. I tried this ... to try and understand it
If CBool(objConn.State And adStateOpen) Then
MsgBox "Connection is still open"
But sometimes when it returns TRUE the connection code below still takes that 10 secs - which I interpret to mean the connection is closed!
Question: what is a good way to check whether the connection is actually closed (so I can prompt a reconnect)?