VBA code to check if SQL Server exists

[paulosq]

Hi All,

I have an Excel workbook that retrieves data from a SQL Server database on the local network. When the computer is where the workbook is on the network, all right, but when not on the network, I get the message:

"Run-time error '-2147467259 (80004005)': [DBNETLIB] [ConnectionOpen (Connec ()).] SQL Server does not exist or access denied."​

I tried to treat this using On Error GoTo ErrHandler:, but the instruction is ignored.

I need that when the SQL server is not available, the application VBA ignore retrieve data and present the user with a message more friendly.

Please can someone help me?

I am using MS Excel 2010 64-bit with SQL Server 2008 R2 and my code to test the connection is this:

Sub TstCnn()​

​

On Error GoTo ErrHandler:​

​

Dim adoCnn As ADODB.Connection​

Dim strCnn As String​

Dim blnCnn As Boolean​

​

strCnn = "Provider=SQLOLEDB.1;"​

strCnn = strCnn & "Data Source=localhost;Initial Catalog=BD_Financial;"​

strCnn = strCnn & "Integrated Security=SSPI;"​

​

Set adoCnn = New ADODB.Connection​

​

adoCnn.Open strCnn​

​

If adoCnn.State = adStateOpen Then​

blnCnn = True​

adoCnn.Close​

End If​

​

MsgBox blnCnn​

​

ExitHere:​

On Error GoTo 0​

Exit Sub​

​

ErrHandler:​

MsgBox "NOK"​

Resume ExitHere:​

​

End Sub​

Many thanks and sorry for my bad English.

Paulo S. Quateli