All geek questions in one place

VBA data-level error handling

I have several legacy Excel files with a lot of stored procedure calls and db connections that are all done the same way with recordsets ...

since there is no try-catch-finally concept in VBA, as in .NET, is there any best practice to apply a slightly more reliable level of data access for this? I would like to have a good example of how to open a connection, execute a stored procedure that requires some parameters, and then close the connection and release resources in any case of success or error.

+4
source share
2 answers

Now I use a different approach, I created a managed .NET data layer and exported it as a COM class, following the instructions here: Calling .NET from VBA so I have to deploy and register the COM assembly along with the excel file, and it hurts a bit, I I admit, but at least I can correctly access data and use C #, ADO.NET and so on ...

+4
source

In VBA you need to use On Error lock: 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
+7
source

Source: https://habr.com/ru/post/1338076/

More articles:


All Articles