CODEWITHSUNDEEP
excelerror-handlingnestedvba
genespos
genespos

Reputation: 3311

excel-vba nested error handling

I wrote a vba-code for inserting data into a mysql-DB and to save time I've thought to make execute multiple "insert" with only one connection to the DB but I'm worried about possible errors in the insert command so, in case of error, I want to execute queries once at time.

This makes it possible to execute the commands without errors but, If there was an error, it will be again and I'll need to handle it.

To do this I thought the code below but, looking for something similar, I didn't find anything ... Maybe it was a bad idea? Is there a better way to do this?

This is my idea:

For i = 1 To 100
    '...do something

    'First error handling activation
    On Error GoTo ErrHandle1

    'Reset SQL String
    SQLStr = ""

    'Loop for making SQL string for multiple INSERT
    For ii = 1 To 50
        '...example instructions:
        SQLStr = SQLStr & "INSERT INTO myTab (myField) VALUES ('" & ii / i & "'); "
    Next ii
    'Execute all insert at one time
    rs.Open SQLStr, Cn, adOpenStatic

GoNext:
On Error GoTo 0
Next i

Exit Sub

'primary error handling
ErrHandle1:
'No instrutions here
'I use resume only to reset error
Resume Handle1
Handle1:

'I try again INSERT loop with a connection to DB for every query
'Second error handling activation
On Error GoTo ErrHandle2
For ii = 1 To 50
    'String for only one INSERT
    SQLStr = "INSERT INTO myTab (myField) VALUES ('" & ii / i & "'); "
    'Single query execution
    rs.Open SQLStr, Cn, adOpenStatic
Next ii
'Back to base code
GoTo GoNext

'secondary error handling
ErrHandle2:
MsgBox Err & " - " & Error(Err)
Resume Next

Upvotes: 0

Views: 682

Answers (1)

user3819867
user3819867

Reputation: 1120

I recommend placing error handling routines on the level of the error (with the If False separator from normal course). Avoid GoTo 0 in case of fundamental errors, it will just use up resources.

On Error GoTo ErrHandleri
For i = 1 To 100
'...do something
    For ii =1 To 50
    On Error GoTo ErrHandlerii
        'do other thing
    If False Then
Errhandlerii:
    'do error handling stuff
    End If
    Next ii
If False Then
ErrHandleri:
'code, e.g. paint the whole line blue
End If
Next i

Upvotes: 1

Related Questions