VBA data layer error handling

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 l开发者_如何转开发ike 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.

---

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

---

I am actually using a different approach now, I created a .NET managed data layer and exported it as COM class, following the instructions here: Calling .NET from VBA in this way I have to deploy and register the COM assembly together with the excel file and it's a bit painful I admit, but at least I can handle data acces in a proper way and use C#, ADO.NET and so on...