我在一些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
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
编辑：
不建议将 .State 与 1 或 0 进行比较，因为即使它在99.99％的情况下都有效，仍然有可能存在设置了其他标志的情况，这将导致 if 语句中的 adStateOpen 检查失败。
编辑2: 如果没有引用 ActiveX 数据对象，可以使用来自 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
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
这个话题已经老旧了，但如果像我一样的其他人在寻找解决方案，这是我找到的一个解决方案：
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
"myBase"是一个数据库对象，我已经创建了一个类来访问数据库（包括插入、更新等功能），在模块中使用该类声明一个对象（显然），并且我可以通过"[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
.Version？你能否添加到myClass内部的代码？我最近发布了这个问题，就在上周，所以我非常想看看你是如何编写myClass的：http://codereview.stackexchange.com/questions/116253/class-module-to-wrap-up-classic-ado-call-to-sql-server - whytheq
And操作。只有当.State中的那个位被设置并且所有其他位都被清零时，才会保留adStateOpen位。将And操作值与adStateOpen进行比较的结果只有在.State中设置了该位标志时才会为True。 - Raybargrs.adStateOpen而是adStateOpen。 - RaybargadStateOpen会出现“变量未定义”的错误...这就是为什么我编辑并添加了rs.的原因...尽管我认为rs是错误的编辑 - 我已经重新编辑过了。 - whytheq.State值将是1+4=5(二进制0101)或1+8=9(二进制1001)。如果您愿意，可以使用简单的If rs.State = 1，但为什么不按正确的方式做呢？这将在所有情况下都起作用，避免未来难以找到的错误情况。 - RaybargADODB.Connection.State也使用ObjectStateEnum。 - Raybarg