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