vba - 检查 ADODB 连接是否打开

Question

我在一些 Excel 程序中使用以下内容来建立与数据库的连接。

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 是否打开，所以我知道它需要关闭，但以下不起作用。如何更改以下条件才能工作？

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

以下方法有效，但我不想以这种方式使用错误捕获:

On Error Resume Next
    rs.Close

Answer 1

ADO Recordset具有.State属性，您可以检查其值是adStateClosed还是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

编辑；不检查 .State 是否为 1 或 0 的原因是，即使它在 99.99% 的时间内有效，仍然可能有 other flags set这将导致 If 语句无法通过 adStateOpen 检查。

编辑2:

对于没有引用 ActiveX 数据对象的后期绑定(bind)，您几乎没有选择。使用 ObjectStateEnum 中的 adStateOpen 常量值

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

或者您可以自己定义常量以使代码更具可读性(将它们全部定义为一个很好的示例。)

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