Geek asks and answers

VBA error handling in ADODB Connection.Open

I have an ADODB connection in VBA to connect to a SQLServer database. I want to catch the error that occurs when calling connection.Open, and this database is not available.

My code is as follows:

Public Function Connect() As Boolean

On Error GoTo DBError

    Dim dbServer As String
    Dim dbName As String
    Dim dbUser As String
    Dim dbPwd As String

    dbServer = DatabaseSettings.dbServer
    dbName = DatabaseSettings.dbName
    dbUser = DatabaseSettings.dbUser
    dbPwd = DatabaseSettings.dbPwd

    Dim connectionString As String
    connectionString = "Server=" & dbServer & ";Database=" & dbName & ";User Id=" & dbUser & ";Password=" & dbPwd

    Set conn = New ADODB.Connection
    conn.Provider = "sqloledb"
    With conn
        .ConnectionTimeout = 2
        .CursorLocation = adUseClient
        .Open connectionString
        .CommandTimeout = 0
    End With

Connect = True
Exit Function

DBError:
    Connect = False
End Function

My problem is that when I try to run this code with the wrong connection, the error occurs and appears in the MsgBox and does not fall into the “On Error GoTo DBError”.

Is there something wrong in my error handling code, or do I need to find another way to catch this error?

Thank you for your help. Any suggestions are welcome.

+3
source share
2 answers

, , VBE , Tools... Options... General... Error Trapping "Break on Unhandled Errors". "Break on All Errors", .

+5

:

With conn
    .ConnectionTimeout = 2
    .CursorLocation = adUseClient
    .ConnectionString = connectionString
    .Open 
    .CommandTimeout = 0
End With
0

More articles:


All Articles