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
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

HuaMin ChenProblem resolverCommented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.