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.

I put two possible answers here: https://stackoverflow.com/questions/35281342/test-sql-server-connection/59196981#59196981 — spioter, Dec 05 '19 at 14:23

score 6 · Answer 1 · answered Mar 10 '09 at 21:56

6


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

answered Mar 10 '09 at 21:56

shahkalpesh

33,172
3
63
88

This is a good approach and I had initially accepted it as the answer. However, it still errors out if the "DSN HERE" is an invalid connection string. Methinks ADO needs a TryOpen method! :) – AR. Mar 11 '09 at 17:08
1

DSN should be known before-hand. What is it that you are trying? – shahkalpesh Mar 11 '09 at 17:52

score 2 · Answer 2 · answered Aug 24 '12 at 11:20

I'm too late to give you a useful answer to your question, but I came here because I wanted to see if StaCkOverflow has a better answer than the code I'm currently using to test ADODB connections.

...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'.

TestConnection: a VBA Class method for publishing debugging information for an ADODB.Connection object

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.

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 i

End 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).

score 0 · Answer 3 · answered Dec 02 '14 at 08:58

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.

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.

DJ. · Answer 4 · 2009-03-10T22:01:01.057

0

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

edited Mar 10 '09 at 22:01

answered Mar 10 '09 at 21:54

DJ.

16,045
3
42
46

DSNs are pointing to an external SQL Server database. Sorry, should have specified - I'll update the question to reflect. – AR. Mar 10 '09 at 21:57
Access cannot use ODBC to connect to a Jet database, so the first is not going to be of any use within Access. – David-W-Fenton Mar 14 '09 at 23:22

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

4 Answers4

Linked