How to test connectivity with sql server 2005?

  • Thread starter Thread starter sandal
  • Start date Start date
S

sandal

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.
 
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.
Click to expand...

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
 
Back
Top