This is an old topic, but in case anyone else is still looking...
I was having trouble after an undock event. An open db connection saved in a global object would error, even after reconnecting to the network. This was due to the TCP connection being forcibly terminated by remote host. (Error -2147467259: TCP Provider: An existing connection was forcibly closed by the remote host.)
However, the error would only show up after the first transaction was attempted. Up to that point, neither Connection.State nor Connection.Version (per solutions above) would reveal any error.
So I wrote the small sub below to force the error - hope it's useful.
Performance testing on my setup (Access 2016, SQL Svr 2008R2) was approx 0.5ms per call.
Function adoIsConnected(adoCn As ADODB.Connection) As Boolean
'----------------------------------------------------------------
'#PURPOSE: Checks whether the supplied db connection is alive and
' hasn't had it's TCP connection forcibly closed by remote
' host, for example, as happens during an undock event
'#RETURNS: True if the supplied db is connected and error-free,
' False otherwise
'#AUTHOR: Belladonna
'----------------------------------------------------------------
Dim i As Long
Dim cmd As New ADODB.Command
'Set up SQL command to return 1
cmd.CommandText = "SELECT 1"
cmd.ActiveConnection = adoCn
'Run a simple query, to test the connection
On Error Resume Next
i = cmd.Execute.Fields(0)
On Error GoTo 0
'Tidy up
Set cmd = Nothing
'If i is 1, connection is open
If i = 1 Then
adoIsConnected = True
Else
adoIsConnected = False
End If
End Function
If (rs.State And rs.adStateOpen) = rs.adStateOpen Then
: although I still don't understand this conditional statement. What will it be evaluating to if rs is open?If (1 And ?) = ? Then
– Construe