Transaction using multiple DoCmd.Run SQL Statements

[birgitd]

I'm trying to set up something like:

BEGIN TRANS

DoCmd.RunSQL strSql1
DoCmd.RunSQL strSql2
DoCmd.RunSQL strSql3

mgs = "Are you sure you want to run this SQL?"

If MsgBox(mgs, vbQuestion + vbYesNo) = vbYes Then
COMMIT TRANS
End If
Else
ROLLBACK TRANS

But it doesn't work. If my strSql3 fails, strSql1 & strSql2 are still committed.

Any help would be appreciated.

Thanks,
Birgit