Check for valid SQL server connection

[Robert W. King]

Hi folks!

I'm a relative novice at Excel 2003 and VBA, and a complete novice using
SQL. The project I'm working on is to build a Excel macro to retrieve
records from SQL database tables and stuff selected fields into cells on a
spreadsheet. I have some fragments of working code that I'm trying to expand
to fit my purpose.

'Define the SQL access handles

Dim rs As New ADODB.Recordset
Dim cn As New ADODB.Connection
Dim strSQL As String

'Connect to the database.

cn.ConnectionString = "provider =
SQLOLEDB;server=SQLSRVR;database=DBASEXYZ;Trusted_Connection =
yes;Integrated Security=SSPI"
cn.ConnectionTimeout = 30
cn.Open

rs.ActiveConnection = cn
rs.CursorLocation = adUseClient ' allows access to recordcount

That code is then followed by the the SQL query and so forth. However, what
I want to do beforehand is to check whether I have a valid connection
established to the SQL server database. I presume that there is some
property, et cetera I can test immediately following the cn.Open statement
that should let me find out whether the open was successful, but in my
thrashing about with the help files and a treeware manual or two, I've yet
to learn any more about it. Can anyone give me a hand?

 

[Jamie Collins]

I want to do beforehand is to check whether I have a valid connection
established to the SQL server database. I presume that there is some
property, et cetera I can test immediately following the cn.Open statement
that should let me find out whether the open was successful

As you are opening the Connection synchronously, you would get a
run-time error if the connection failed. If you are riding over
run-time errors (e.g. using On Error Resume Next) you could
subsequently check the connection's State property.

Alternatively, you could connect asynchronously but sinking the
Connection object's events in a class module and get any error message
when the ConnectComplete event e.g.

Option Explicit

Private WithEvents m_Con As ADODB.Connection

Private Sub m_Con_ConnectComplete( _
ByVal pError As ADODB.Error, _
adStatus As ADODB.EventStatusEnum, _
ByVal pConnection As ADODB.Connection)

End Sub

Jamie.

--