• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 100
  • Last Modified:

Excel ADODB connection error - How do I create error handler for this issue

Can you tell me how to capture an error for an Excel ADODB connection error ?  Currently I have the below code (note this Connection module is called from a Main module) The issue is IF there is ever a ADODB connection issue or someone quickly changes the name of the source file then the ADODB connection fails.  The user gets an unfriendly error and in some cases the macro actually gets deleted.

If there is a connection issue I want to provide the user with a friendly message, but more importantly exit the macro peacefully and minimize the macro getting deleted or leaving code page displayed..

I assume I do an OnError for the error handling, but where in the module ?

Code Snippet
Dim adoConn as ADODB.Connection, adoRecordset As ADODB.Recordet,strConn As String

strConn = "Driver-{Microsoft Excel Driver......................................................MyFileName & ";IMEX=1;"

Set adoConn = New ADODB.Connection: adoConn.Open strConn
Set adoRecordset = New ADOB.Recordset: adoRecordset.Open Source:=strSQLStatement, ActiveConnection:=adoConn
1 Solution
Fabrice LambertFabrice LambertCommented:

Just write a standard error handler:
Public sub mySub()
On Error Goto Error
    Dim strConn As String
    strConn = "Driver-{Microsoft Excel Driver......................................................MyFileName & ";IMEX=1;"

    Dim adoConn as ADODB.Connection
    Set adoConn = New ADODB.Connection
    doConn.Open strConn

    adoRecordset As ADODB.Recordet
    Set adoRecordset = New ADOB.Recordset
    adoRecordset.Open Source:=strSQLStatement, ActiveConnection:=adoConn    

    '// ect
Exit Sub
    Select Case Err.Number
    Case .....
        '// Add code for errors you can handle here
        Resume Next (or resume to a label)
    Case Else
            '// Error you can't handle, escalate the error
           '// The calling code will also need an error handler
           '// Or display a nice message to the user if you can't escalate, this will preent the VBA project from collapsing.
        Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext
    End Select
End Sub

Open in new window

upobDaPlayaAuthor Commented:
Perfect..but one follow-up question...what do you mean by "cant escalate"....
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
With ADODB, error may occur if...
1) There is an issue with the connection string
2) There is an issue opening the Recordset
3) There is an issue while manipulating the Recordset

So you will need to handle each error if produced separately.

You may try something like this and tweak it as per your requirement.

Set adoConn = New ADODB.Connection

On Error GoTo ErrorConnection
adoConn.Open strConn

Set adoRecordset = New ADOB.Recordset

On Error GoTo ErrorRecordset
adoRecordset.Open Source:=strSQLStatement, ActiveConnection:=adoConn

'Manipulating the Recordset here
On Error GoTo CloseAll
'your code manipulating the recordset goes here

'If the code completed without an error
MsgBox "The task completed successfully.", vbInformation
Exit Sub

'If something went wrong during manipulation of recordset, close the connection and recordset
MsgBox "Your custom msg here"
Exit Sub

'If the code reaches this point, that means there was an issue with Recordset source whether it's a table or SQL query string
'If that's the case, the connection was made to the database successfully and it is still open so close the connection as well
MsgBox "There was an error in opening the Recordset.", vbExclamation, "Recordset was not opened!"
Exit Sub

'If there was error in connection string
MsgBox "Something went wrong with Connection string.", vbExclamation, "Unable To Connect To The Database!"
End Sub

Open in new window

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

HuaMin ChenSystem AnalystCommented:
Use On Error to capture relevant error, by using Debug.Print to check relevant values of variables in your codes.
upobDaPlayaAuthor Commented:
Created multiple OnError blocks
Fabrice LambertFabrice LambertCommented:
what do you mean by "cant escalate"....
this happen when the error reached the top calling function (or procedure), if the error continue to propagate itself, the system catch it by displaying an unpleasent error message, and collapsing your project.

I don't think that writing multiple error Handler within the same function is usefull.
First, you can discriminate the error by its number.
Second, multiple error handlers are difficult to maintain, and error prone.
Third, multiple error handlers probably indicate that your function do to much things, and should be broke into several smaller functions that do one thing only, and do it right.
upobDaPlayaAuthor Commented:
I appreciate the feedback let me re-review both solutions.
upobDaPlayaAuthor Commented:
I thought previously i could award points across multiple solutions - is this no longer possible
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now