如何最好地在 Access 或 Excel 中使用 VBA 来测试 ODBC 连接？

Question

给定一个预先配置的 ODBC 系统 DSN，我想编写一个函数，使用 VBA 优雅地测试该连接。

Private Function TestConnection(ByVal dsnName As String) As Boolean

    ' What goes here?? '

End Function

编辑：澄清一下，系统 DSN 指向外部 SQL Server 2005 数据库，并使用 Windows NT 身份验证。

我尝试过的一种方法是向目标数据库发送一些随机查询并捕获错误。 如果查询有效，则返回 true。 如果有错误则返回 false。 这工作得很好，但感觉……很笨拙。 是否有一种更优雅的方法，尤其是不依赖于 On Error Goto 的方法？

注意：我正在处理的是旧版 Access 2000 数据库，因此任何解决方案都不能具有任何 Access 2007 或 2003 依赖项。 我想让它对 VBA 通用，但如果 Access 中有一种简单的方法也很好。

非常感谢任何建议。

Answer 1

Dim cnn As ADODB.Connection
Dim canConnect as Boolean
Set cnn = New ADODB.Connection

cnn.Open "DSN HERE"
If cnn.State = adStateOpen Then
    canConnect = True
    cnn.Close
End If

Msgbox canConnect

编辑：DSN 格式可以是“DSN=MyDSN;UID=myuser;PWD=myPwd;”
查看这里以获取连接字符串

Answer 2

我来不及为您的问题提供有用的答案，但我来到这里是因为我想看看 StaCkOverflow 是否有比我当前用于测试 ADODB 连接的代码更好的答案。

...事实证明答案是“否”，所以我将发布代码以供参考：其他人会发现它有用。

编码说明：这不是一个通用答案：它是封装 ADODB.Connection 对象的类中的方法，并且假定对象“m_objConnect”存在。

TestConnection：用于发布 ADODB.Connection 对象调试信息的 VBA 类方法

这会打印出连接字符串、当前状态、ADODB 错误列表（如果有）以及连接的完整列表命名属性。

 公共子测试连接（） 
  出错时 GoTo ErrTest

 Dim i As Integer

如果 m_objConnect 为空则

Debug.Print "Object 'm_objConnect' not instantiated."

否则
Debug.Print m_objConnect.ConnectionString
Debug.Print "Connection state = " & ObjectStateString(m_objConnect.State)

Debug.Print

If m_objConnect.Errors.Count > 0 Then
    Debug.Print "ADODB ERRORS (" & m_objConnect.Errors.Count & "):"
    For i = 0 To m_objConnect.Errors.Count
        With m_objConnect.Errors(i)
            Debug.Print vbTab & i & ":"  _ 
                      & vbTab & .Source & " Error " & .Number & ": " _ 
                      & vbTab & .Description & " " _ 
                      & vbTab & "(SQL state = " & .SqlState & ")"
        End With
    Next i
End If

Debug.Print

Debug.Print "CONNECTION PROPERTIES (" & m_objConnect.Properties.Count & "):"
For i = 0 To m_objConnect.Properties.Count - 1
    Debug.Print vbTab & i & ":" _ 
              & vbTab & m_objConnect.Properties(i).Name & " = " _ 
              & vbTab & m_objConnect.Properties(i).Value
Next i

结束如果
ExitTest:

      退出子程序

  错误测试：

      调试.打印“错误”&   错误编号和错误号  “由”&提出  错误来源和错误  “：”&   错误描述

      Resume Next
 End Sub
 Private Function ObjectStateString(ObjectState As ADODB.ObjectStateEnum) As String
 Select Case ObjectState

  案例 ADODB.ObjectStateEnum.adStateClosed

      对象状态字符串=“已关闭”

  案例 ADODB.ObjectStateEnum.adStateConnecting

      ObjectStateString =“正在连接”

  案例 ADODB.ObjectStateEnum.adStateExecuting

      ObjectStateString = "正在执行"

  案例 ADODB.ObjectStateEnum.adStateFetching

      ObjectStateString = "正在获取"

  案例 ADODB.ObjectStateEnum.adStateOpen

      对象状态字符串=“打开”

  其他情况

      ObjectStateString =“状态”&   CLng(ObjectState) &   “：未知状态号”

  结束 选择
结束功能

分享并享受：并注意换行符，在浏览器（或 StackOverflow 的格式化功能）会破坏代码的位置插入换行符会很有帮助。

Answer 3

没有神奇的功能可以在不实际连接和尝试操作的情况下测试这一点。

如果您对随机查询部分感觉不好 - 您可以查询系统表

For Access

SELECT TOP 1 NAME FROM MSysObjects 

For SQL Server

SELECT TOP 1 NAME FROM sysobjects 

Answer 4

如果您只需测试数据库服务器是否实际可用，则可以这样做，尽管这里说它不能。

在这种情况下，您可以尝试打开到特定服务器和端口的 TCP 连接。
例如，SQL Server 的默认实例侦听 TCP 端口 1433。在 VBA 中尝试简单的 TCP 连接会告诉您是否成功。 仅当成功时我才会使用 ODBC 连接进行查询。

这更加优雅和高效。 它将消除 ODBC 测试代码中的任何“严重”错误。 但是，正如我所说，它仅适用于您需要测试数据库服务器实例是否存在/可用性的情况。