如何最好地在 Access 或 Excel 中使用 VBA 来测试 ODBC 连接？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/632385/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I best use VBA in Access or Excel to test an ODBC connection?
提问by AR.
Given a pre-configured ODBC System DSN, I'd like to write a function that gracefully teststhat connection using VBA.
给定一个预配置的 ODBC 系统 DSN，我想编写一个函数，使用 VBA优雅地测试该连接。
Private Function TestConnection(ByVal dsnName As String) As Boolean
' What goes here?? '
End Function
Edit: To clarify, the System DSNs are pointing to external SQL Server 2005 databases, with Windows NT authentication.
编辑：澄清一下，系统 DSN 指向外部 SQL Server 2005 数据库，使用 Windows NT 身份验证。
One approach I've tried is to send some random query to the target database and catch the error. If the query works, return true. If there's an error then return false. This works just fine but it feels...kludgy. Is there a more elegant way, especially one that doesn't rely on On Error Goto?
我尝试过的一种方法是向目标数据库发送一些随机查询并捕获错误。如果查询有效，则返回 true。如果有错误，则返回 false。这工作得很好，但感觉......笨拙。有没有更优雅的方法，尤其是不依赖On Error Goto 的方法？
Note: It's a legacy Access 2000 database I'm working on, so any solution can't have any Access 2007 or 2003 dependencies. I'd like to make it generic to VBA, but if there's a simple way in Access that's fine too.
注意：这是我正在处理的旧版 Access 2000 数据库，因此任何解决方案都不能有任何 Access 2007 或 2003 依赖项。我想让它对 VBA 通用，但是如果 Access 中有一种简单的方法也可以。
Much obliged for any advice.
非常有义务提供任何建议。
回答by shahkalpesh
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
EDIT: DSN Format could be "DSN=MyDSN;UID=myuser;PWD=myPwd;"
Look thisfor connection strings
编辑：DSN 格式可以是“DSN=MyDSN;UID=myuser;PWD=myPwd;”
看看这个连接字符串
回答by Nigel Heffernan
I'm too late to give you a useful answer to your question, but I came herebecause I wanted to see if StaCkOverflow has a better answer than the code I'm currently using to test ADODB connections.
我为时已晚，无法为您的问题提供有用的答案，但我来到这里是因为我想看看 StaCkOverflow 是否有比我目前用于测试 ADODB 连接的代码更好的答案。
...It turns out that the answer is 'No', so I'll post the code for reference: someone else will find it useful.
...事实证明答案是“否”，所以我将发布代码以供参考：其他人会发现它很有用。
Coding notes: this isn't a generic answer: it's a method from a class encapsulating the ADODB.Connection object, and it assumes the existence of object 'm_objConnect'.
编码说明：这不是一个通用的答案：它是来自封装 ADODB.Connection 对象的类的方法，并且它假定对象“m_objConnect”存在。
TestConnection: a VBA Class method for publishing debugging information for an ADODB.Connection object
TestConnection：用于发布 ADODB.Connection 对象的调试信息的 VBA 类方法
This prints out the connection string, the current status, a list of ADODB errors (if any) and a full listing of the onnection's named properties.
这会打印出连接字符串、当前状态、ADODB 错误列表（如果有）和连接命名属性的完整列表。
Public Sub TestConnection() On Error GoTo ErrTest
Dim i As Integer
If m_objConnect Is Nothing Then
Debug.Print "Object 'm_objConnect' not instantiated."Else
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 iEnd If
ExitTest: Exit Sub ErrTest: Debug.Print "Error " & Err.Number & " raised by " & Err.Source & ": " & Err.Description Resume Next
End Sub
Private Function ObjectStateString(ObjectState As ADODB.ObjectStateEnum) As String
Select Case ObjectState Case ADODB.ObjectStateEnum.adStateClosed ObjectStateString = "Closed" Case ADODB.ObjectStateEnum.adStateConnecting ObjectStateString = "Connecting" Case ADODB.ObjectStateEnum.adStateExecuting ObjectStateString = "Executing" Case ADODB.ObjectStateEnum.adStateFetching ObjectStateString = "Fetching" Case ADODB.ObjectStateEnum.adStateOpen ObjectStateString = "Open" Case Else ObjectStateString = "State " & CLng(ObjectState) & ": unknown state number" End Select
End Function
Share and enjoy: and watch out for line-breaks, helpfully inserted where they will break the code by your browser (or by StackOverflow's formatting functions).
分享和享受：并注意换行符，这些换行符会被您的浏览器（或通过 StackOverflow 的格式化功能）插入到它们会破坏代码的位置。
回答by DJ.
There no magic function that will test this without actually connecting and trying an operation.
在没有实际连接和尝试操作的情况下，没有任何神奇的功能可以测试这一点。
If you feel bad about the random query part - you can query the system tables
如果你对随机查询部分感觉不好 - 你可以查询系统表
For Access
访问
SELECT TOP 1 NAME FROM MSysObjects
For SQL Server
对于 SQL Server
SELECT TOP 1 NAME FROM sysobjects
回答by nepdev
If you merely have to test that the database server is actually available, this can be done, despite what is being said here that it cannot.
如果您只需要测试数据库服务器是否实际可用，则可以这样做，尽管这里说的是它不能。
In that case, you can attempt to open a TCP connection to the specific server and port. The default instance of SQL Server, for example, listens on TCP port 1433. Attempting a simple TCP connection in VBA will tell you if it succeeds or not. Only if that is successful I would query using the ODBC connection.
在这种情况下，您可以尝试打开到特定服务器和端口的 TCP 连接。例如，SQL Server 的默认实例侦听 TCP 端口 1433。在 VBA 中尝试一个简单的 TCP 连接会告诉您它是否成功。只有成功了，我才会使用 ODBC 连接进行查询。
This is a lot more graceful and efficient. It would remove any "gross" error from your ODBC test code. However, as I said, it is only applicable if you need to test for the mere existence/availability of the database server instance.
这更加优雅和高效。它会从您的 ODBC 测试代码中删除任何“严重”错误。但是，正如我所说，它仅适用于需要测试数据库服务器实例的存在/可用性的情况。
