检查ADODB连接是否打开 [英] Check if ADODB connection is open

问题描述

我在某些excel程序中使用以下内容来建立与我们数据库的连接.

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 

在随后的代码中，我使用各种SQL字符串打开连接.
我想测试rs是否打开，所以我知道它需要关闭，但以下操作无效.我该如何更改以下条件才能正常工作?

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

推荐答案

ADO Recordset具有.State属性，您可以检查其值是adStateClosed还是adStateOpen

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

编辑； 不对1或0进行.State检查的原因是，即使99.99％的时间都可以使用.State，它仍然可能具有

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:

对于没有引用ActiveX数据对象的后期绑定，您几乎没有选择. 使用来自 ObjectStateEnum 的adStateOpen常量的值.

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

这篇关于检查ADODB连接是否打开的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！