VBA Error handling on ADODB Connection.Open

Question

I have an ADODB connection in VBA for connecting to an SQLServer database. I want to catch the error that is raised when connection.Open is called and the given database is unreachable.

My code looks like this:

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 an incorrect connectionString an error is raised and shown in a MsgBox and not caught by the "On Error GoTo DBError".

Is there something wrong in my error handling code or do i need to find another way of catching this error?

Thank you for your help. Any suggestions are welcome.

score 6 · Accepted Answer · answered Mar 22 '11 at 14:20

6

Not sure if this is it, but in the VBE window make sure the Tools...Options...General...Error Trapping option is set to "Break on Unhandled Errors". If it were set to "Break on All Errors" this may bypass your handlers.

answered Mar 22 '11 at 14:20

tpascale

2,516
5
25
38

Thank you this solved my problem. Do you know if i can force the user to have this setting in some way? Otherwise i will have a problem when I "release" my application. Maybe check what the setting is set to? Thanks again for your help. – PKeno Mar 24 '11 at 07:01
don't know offhand but I think you'll find it here: http://msdn.microsoft.com/en-us/library/aa221603(v=office.11).aspx – tpascale Mar 24 '11 at 18:09

score 0 · Answer 2 · answered Mar 22 '11 at 14:42

0

Try to use the below, worked here:

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

answered Mar 22 '11 at 14:42

Tiago Cardoso

2,057
1
14
33

VBA Error handling on ADODB Connection.Open

2 Answers2

Linked