Trapping ADO connection error

[PeterT]

It seems strange to me that the Connection object doesn't expect failed connections and that this has to be trapped with On Error. Am I missing something?

[Andrew R.]

What type of DB engine you're connectin to?

[PeterT]

SQL Server

[Andrew R.]

In this case I had the same problem. So, to catch errors during connection I use this function before connecting with ADO.

'------------------------------------------------------------------------------------
private Function lConnectSQLDB(strServer_p as string, _
strLogin_p as string, _
strPassw_p as string) as Long
'
' tries to connect to SQL DB, returns meaningful error if not successfully
'------------------------------------------------------------------------------------
on error GoTo ErrorHandler
'
' to connect to SQL Server/MSDE
Dim oSQLServer as Object
set oSQLServer = CreateObject("SQLDMO.SQLServer")
'
' set timeout
oSQLServer.LoginTimeout = 60
'
' try to connect
oSQLServer.Connect strServer_p, strSQLLogin_g, strPassw_p
'
' close connection if it was successful
oSQLServer.Close
lConnectSQLDB = 0
'
Exit Function
'
ErrorHandler:
oSQLServer.Close
lConnectSQLDB = Err.Number
Select Case Err.Number
Case 18456
' we're here if SQL Server refused provided login and password
Case 440
' we're here if SQL Server doesn't respond (doesn't exist)
Case 429
' we're here if user doesn't have SQL client
Case else
MsgBox Err.Number, Err.Description, vbCritical
End Select
End Function



To use this SQLDMO stuff your PC should have installed SQL Server or MSDE or SQL client.
Or none of these, but several dll's in special place.

I'm glad, if it will help you.

[PeterT]

Thank you for the code. I created a similar function using the ADO connection object. But that doesn't provide me with any specifics on the nature of the error. I'll check out SQLDMO.