VBA Error handling on ADODB Connection.Open

vba adodb
PKeno picture PKeno · Mar 22, 2011 · Viewed 23.8k times · Source

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.

Answer

tpascale picture tpascale · Mar 22, 2011

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.

Related questions

Accessing SQL Database in Excel-VBA

I am copying an VBA code snippet from MSDN that shows me how to grab results from a SQL query into excel sheet (Excel 2007): Sub GetDataFromADO() 'Declare variables' Set objMyConn = New ADODB.Connection Set objMyCmd = New ADODB.Command Set objMyRecordset = …

Read More
How to populate a ComboBox with a Recordset using VBA

There is some literature available at expert's exchange and at teck republic about using the combobox.recordset property to populate a combobox in an Access form. These controls are usually populated with a "SELECT *" string in the 'rowsource' properties of …

Read More
Excel VBA - connect to sql with a trusted connection (no uid/pwd)

Is there a way to have Excel connect to Sql Server using the current user's network windows authentication (trusted connection/integrated security)? I don't want to rely on a sql login or have a uid/password in my code.

Read More