How can I best use VBA in Access or Excel to test an ODBC connection?
Solution 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
EDIT: DSN Format could be "DSN=MyDSN;UID=myuser;PWD=myPwd;"
Look this for connection strings
Solution 2
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).
AR.
I'm a hack, not a hacker. A hobbyist, not a pro. But I do love to code!
Updated on December 02, 2020Comments
-
AR. over 3 years
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.