How to call a Rollback in an Error-Handler

[Dugantrain]

Enclosed is a piece of a function I have which calls several ADO strings to perform actions on data in two different tables. Everything pretty much works fine, but what I'd like to do is have all of these actions Rollback in an Error-Handler, but only if the Error which was raised pertains to one or all of these actions:

Conn1.BeginTrans
Conn1.Execute sqlDeleteTravel 'Delete travel records
Conn1.Execute sqlUpdate0
Conn1.Execute sqlUpdate1
Conn1.Execute sqlUpdate2 ' 5 separate Updates on dirty import data
Conn1.Execute sqlUpdate3
Conn1.Execute sqlUpdate4
Conn1.Execute sqlInsertImport 'Insert Clean to Temp
Conn1.Execute sqlDelete_Import 'Delete out of Import
Conn1.CommitTrans
MsgBox "Clean-up Successful", , "Success!"
'Raise up outta this piece
Conn1.Close
Set Conn1 = Nothing
Exit Function

'And here is the error-handler which needs a Rollback
f_CleanImport_Err:
MsgBox Err.Description
Exit Function
End Function

The entire function is really too much to show here (don't want to bore everybody), but there is a lot more to the function which doesn't pertain to these ADO calls. So if an error anywhere is raised anywhere in this function, then I need the error-handler to determine whether or not it relates to my ADO and, if so, Rollback the Transaction. If not, Err.Description (for now, anyway).

 

[llkhoutx]

Conn1.RollbackTrans is what you want.

 

[Dugantrain]

Right, but if an error is triggered before the Transaction has begun, then I get an error saying that I'm trying to Rollback a Transaction that doesn't exist. So I need the error-handler to be conditional based on whether or not it pertains to this Transaction.

 

[llkhoutx]

Have two error branches, one on entering the routine and one after your commit your transaction, eg

 

[Dugantrain]

So Subs/Functions allow for more than one On Error Goto? Alright, easy enough, I've just never come across this before. Thanks so much.

 

[llkhoutx]

When you On Error goto Err_Exit, it's just a statement, so

On Error goto Err_Standard

'code


On Error goto Err_CommitRollback
Conn1.BeginTrans

'code


Conn1.CommitTrans
On Error goto Err_Standard

Should have given you this before.

 

[Dugantrain]

Works like a champ, thanks again!