sql server programming: 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

[quoted text, click to view]

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


--
Hope this helps.

Dan Guzman
SQL Server MVP

[quoted text, click to view]