如何最好在Access或Excel中使用VBA来测试ODBC连接? [英] How can I best use VBA in Access or Excel to test an ODBC connection?

问题描述

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

Given a pre-configured ODBC System DSN, I'd like to write a function that gracefully tests that connection using VBA.

Private Function TestConnection(ByVal dsnName As String) As Boolean

    ' What goes here?? '

End Function

为明确起见，系统DSN指向具有Windows NT身份验证的外部SQL Server 2005数据库.

To clarify, the System DSNs are pointing to external SQL Server 2005 databases, with Windows NT authentication.

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

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 ?

注意:这是我正在使用的旧版Access 2000数据库，因此任何解决方案都不能具有任何Access 2007或2003依赖项.我想使其对VBA具有通用性，但是如果Access中有一种简单的方法也可以.

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.

很多建议.

推荐答案

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;"
在此中查找连接字符串

DSN Format could be "DSN=MyDSN;UID=myuser;PWD=myPwd;"
Look this for connection strings

这篇关于如何最好在Access或Excel中使用VBA来测试ODBC连接?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！