Trap SQL Server Error 4060

[jeffro]

I'm using Access 2003 and conneting to a SQL Server Database.
When the database first opens, a vba function is called and creates the
dns-less connection and it works great if the server name and database name
exists.

If I change either the servername or database name to a non-existant one,is
there a way to trap the default "SQL SERVER Error: 4060" message in VBA?
The on error go to trap does find that it's access err.number 3059...but the
4060 error message pops up prior to the trap.

Thanks in advanced!

 

[Brendan Reynolds]

You could attempt to open an ADO connection using the same server and
database name. The ADO error is easily trapped. Here's an example that uses
late binding for ADO, so you don't have to set a reference to the ADO object
library if you're not using it for other purposes ...

Public Function CheckConnection() As Boolean

Const strcConnection As String = "Provider=SQLOLEDB.1;" & _
"Integrated Security=SSPI;Persist Security Info=False;" & _
"Initial Catalog=NoSuchDatabase;Data Source=NoSuchServer"

'Dim cnn As ADODB.Connection
Dim cnn As Object

On Error GoTo ErrorHandler
'Set cnn = New ADODB.Connection
Set cnn = CreateObject("ADODB.Connection")
cnn.Open strcConnection
CheckConnection = True
ExitProcedure:
On Error Resume Next
If Not cnn Is Nothing Then
'If cnn.State <> adStateClosed Then
If cnn.State <> 0 Then
cnn.Close
End If
End If
Exit Function

ErrorHandler:
CheckConnection = False
Debug.Print Err.Number, Err.Description
Select Case Err.Number
Case -2147467259
'expected error
'do nothing
Case Else
'unexpected error
MsgBox "Error " & Err.Number & ": " & Err.Description
End Select
Resume ExitProcedure

End Function

--
Brendan Reynolds
Access MVP


"(e-mail address removed)"

 

[jeffro]

Thank you!
It worked perfectly!

You deserve a raise