Does using workspace.rollback allow the rollback of many SQLstatemnts?

[cravincreeks]

Hello all.

I'm hoping to modify my code to allow it to rollback many insert/update/delete SQL statements. If an error occurs in any part of my code, I'd like to roll back ALL transactions. Would the following code allow me to do so? Or does this only roll back one SQL statement at a time?

Public Sub CheckinTable()
'Begin transaction
Dim wkDefault As Workspace
Set wkDefault = DBEngine.Workspaces(0)
wkDefault.BeginTrans

On Error Go To errhandler

'the many insert/update/delete statements would go here, many of which are done in sub procedures

errhandler:
wkDefault.Rollback
End sub



Modifying my code to allow this would require me to make some substantial mods to my code and some existing tables. Before I invest the time, I'd like to know that this solution will work.

Thanks

AZ

 

[FancyPrairie]

Yes, that will work. Just don't forget to commit the transaction (I don't see it in your code). Also, you're using the DAO method, so I'm assuming you're dealing with pre-Access 2000. If not, then you would want to do it a different way.

 

[cravincreeks]

Thanks for getting back. I am using Access 2000. However, I do have a reference to Microsoft DAO 3.6 library set. I have not been explicitly been using ADO or ADOX. Does this make a difference? If so, what do I need to be doing differently?

Thanks again!

AZ

 

[SonOfEmidec1100]

By default you would be using DAO.

What you are doing is just what transactions are designed for - all sql statements for same workspace since the BeginTrans are rolled back or committed.

Also note you need an EXIT SUB before the error handler.

F.Y.I. If you were using ADO the BEGINTRANS etc should be against the connection and all sql on that connection would be rolled back or committed.