An access 2003 mdb uses odbc to connect with sql server 2005. Before any
operations are attempted I'd like to make sure the odbc connection is
good, ie not down due to network or server issues. Is there a simple way
to make this test? I'm using sql authentication.
Below is a VBA function example that tests connectivity using ADO/ODBC. You
can change the connection string and code according to your needs. This
method requires a reference to the Microsoft ActiveX Data Objects Library.
Private Function GoodConnection()
Dim connection As New ADODB.connection
On Error GoTo Connection_Error
connection.Open "Driver={SQL Server};" & _
"Server=MyServer;" & _
"Database=MyDatabase;" & _
"Uid=MyUser;" & _
"Password=MyPassword"
On Error GoTo 0
connection.Close
Set connection = Nothing
GoodConnection = True
Exit Function
Connection_Error:
Dim strErrorMessages As String
Dim Error As ADODB.Error
For Each Error In connection.Errors
strErrorMessages = strErrorMessages & Error.Description & vbCrLf
Next Error
MsgBox "Unable to connection to SQL Server: " & strErrorMessages
Set connection = Nothing
GoodConnection = False
End Function
