VBA data layer error handling

Question

I have some legacy Excel files with tons of calls to stored procedures and db connections all done in the old fashion with recordsets...

since in VBA there is no concept of try-catch-finally like in .NET is there any best practice to apply to have a bit more solid data access layer? I would like to have a nice example on how to open connection, execute a stored procedure which requires some parameters and then in any case of success or errors, close the connection and release resources.

Answer 1

In VBA you have to use On Error blocks: its messy but it works

On Error Goto ConnectionFail
' open connection
on Error GoTo 0
...
On Error GoTo QueryFail
' execute querys etc
on Error goto 0
...
Cleanup:
' close connection destroy objects etc
Exit Sub
ConnectionFail
On Error GoTo 0
' handle failure
GoTo Cleanup
QueryFail
On Error GoTo 0
' handle failure
GoTo Cleanup
End Sub