Check if ADODB connection is open
Asked Answered
C

3

21

I use the following from within some excel procedures to establish a connection to our database.

Private Const strConn As String = _
    "PROVIDER=SQLOLEDB.1 ..."     

Sub OpenConnection()

Set cn = CreateObject("ADODB.Connection")
cn.Open strConn
cn.CommandTimeout = 0
Set rs = CreateObject("ADODB.Recordset")
Set rs.ActiveConnection = cn

End Sub 

In subsequent code I open the connection using various SQL strings.
I'd like to test if rs is open so I know that it needs to be closed but the following does not work. How can I change the condition in the following to work?

If (rs.Open = True) Then
    rs.Close
End If

The following works but I'd rather not use error trapping in this way:

On Error Resume Next
    rs.Close
Construe answered 12/7, 2013 at 9:20 Comment(0)
C
35

ADO Recordset has .State property, you can check if its value is adStateClosed or adStateOpen

If Not (rs Is Nothing) Then
  If (rs.State And adStateOpen) = adStateOpen Then rs.Close
  Set rs = Nothing
End If

MSDN about State property

Edit; The reason not to check .State against 1 or 0 is because even if it works 99.99% of the time, it is still possible to have other flags set which will cause the If statement fail the adStateOpen check.

Edit2:

For Late binding without the ActiveX Data Objects referenced, you have few options. Use the value of adStateOpen constant from ObjectStateEnum

If Not (rs Is Nothing) Then
  If (rs.State And 1) = 1 Then rs.Close
  Set rs = Nothing
End If

Or you can define the constant yourself to make your code more readable (defining them all for a good example.)

Const adStateClosed As Long = 0 'Indicates that the object is closed.
Const adStateOpen As Long = 1 'Indicates that the object is open.
Const adStateConnecting As Long = 2 'Indicates that the object is connecting.
Const adStateExecuting As Long = 4 'Indicates that the object is executing a command.
Const adStateFetching As Long = 8 'Indicates that the rows of the object are being retrieved.    

[...]

If Not (rs Is Nothing) Then

    ' ex. If (0001 And 0001) = 0001 (only open flag) -> true
    ' ex. If (1001 And 0001) = 0001 (open and retrieve) -> true
    '    This second example means it is open, but its value is not 1
    '    and If rs.State = 1 -> false, even though it is open
    If (rs.State And adStateOpen) = adStateOpen Then 
        rs.Close
    End If

    Set rs = Nothing
End If
Coppage answered 12/7, 2013 at 9:33 Comment(10)
ok - just guessing but is there a typo in the answer? ...let me try to edit...should it read 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 ?) = ? ThenConstrue
It is bitwise And operation. The bit which is adStateOpen will only remain set if that bit in .State is set and all other bits are zeroed. Result of comparing the And operation value to adStateOpen will only result True if that bit flag was set in .State.Coppage
Heh, your confusion was probably in place. I did not mean to use rs.adStateOpen but adStateOpen.Coppage
I'm using late binding so adStateOpen bugs "Variable not defined" ...that is why I edited and added in rs....although I think rs was the wrong edit - have re-edited.Construe
....hmmm still not working: can you edit your code so it functions without a reference; I use late binding.Construe
Oh, Sorry! The Flags link I provided shows the list of constants where adStateOpen = 1. So yes, let me edit the answer.Coppage
surely If (rs.State And 1) = 1 Then is the same as If rs.State = 1 Then ?!Construe
No, like I said it would work in most of the cases but there is a possibility you end up having it both open AND executing a command or fetching rows, in which cases the .State value would be 1+4=5(binary 0101) or 1+8=9(binary 1001). You can use simple If rs.State = 1 if you want, but why not do it right way which will work in all circumstances, no future hard-to-find bug situations.Coppage
I understand your answer now - thanks. As an aside - is there equal complexity when testing an object like fConn where earlier in the code I've executed Set fConn = CreateObject("ADODB.Connection") and then at the end I want to tidy up this object?Construe
Yes, ADODB.Connection.State also uses ObjectStateEnum.Coppage
E
6

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
Estival answered 12/2, 2020 at 12:54 Comment(0)
F
1

This topic is old but if other people like me search a solution, this is a solution that I have found:

Public Function DBStats() As Boolean
    On Error GoTo errorHandler
        If Not IsNull(myBase.Version) Then 
            DBStats = True
        End If
        Exit Function
    errorHandler:
        DBStats = False  
End Function

So "myBase" is a Database Object, I have made a class to access to database (class with insert, update etc...) and on the module the class is use declare in an object (obviously) and I can test the connection with "[the Object].DBStats":

Dim BaseAccess As New myClass
BaseAccess.DBOpen 'I open connection
Debug.Print BaseAccess.DBStats ' I test and that tell me true
BaseAccess.DBClose ' I close the connection
Debug.Print BaseAccess.DBStats ' I test and tell me false

Edit : In DBOpen I use "OpenDatabase" and in DBClose I use ".Close" and "set myBase = nothing" Edit 2: In the function, if you are not connect, .version give you an error so if aren't connect, the errorHandler give you false

Fimbriate answered 12/1, 2016 at 15:14 Comment(3)
what is this: .Version? ....could you possibly add the code inside myClass ....I recently posted this, just the other week, so I would be VERY interested to see how you have written myClass: codereview.stackexchange.com/questions/116253/…Construe
".Version" give you the version of the Database so if you not connect to a database (I use Access) you can't know the version so by this way you can know if you are connect to the databaseFimbriate
This set me on the right track, but doesn't work in the case where the remote host has terminated the connection, as the version is still available and non-null. In these case the solution I just posted can be used.Estival

© 2022 - 2025 — McMap. All rights reserved.