How can I best use VBA in Access or Excel to test an ODBC connection?

Question

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

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

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 ?

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.

Much obliged for any advice.

Answer 1

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

SELECT TOP 1 NAME FROM sysobjects

Answer 2

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 this for connection strings