Check if ADODB connection is open

Question

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

User · Accepted Answer

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  - gt  true       ex  If  1001 And 0001    0001  open and retrieve  - gt  true          This second example means it is open  but its value is not 1          and If rs State   1 - gt  false  even though it is open     If  rs State And adStateOpen    adStateOpen Then          rs Close     End If      Set rs   Nothing End If

User · Answer

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

User · Answer

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

[vba] Check if ADODB connection is open

Examples related to vba

Examples related to oledb