Help with Access Transactions

[snypa]

Hi guys

I'm using the query wizard in access and I'm running the queries using DoCmd.OpenQuery and I was wondering if is possible to use transactions with such queries

Cheers in advance

 

[ajetrumpet]

Transactions???????????????

 

[snypa]

Transactions???????????????

yes to allow me to roll back if there is an error in the processing

 

[Banana]

You can't exactly issue an transaction command within query; indeed, Jet doesn't support multiple statements. Therefore, you would achieve the same effect by writing a VBA procedure to initiate the transaction, call the queries and commit if successful.

Private Sub foo()

On Error Goto Abort

DBEngine.BeginTrans
CurrentDb.Execute "MyFirstQueryName", dbFailOnError
CurrentDb.Execute "MySecondQueryName", dbFailOnError
CurrentDb.Execute "MyThirdQueryName", dbFailOnError
DBEngine.CommitTrans

Exit Sub

Abort:

DBEngine.Rollback

End Sub

HTH.