Access SQL Transactions?

Question

I've read that MS Access supports transactions but when I try to use it, I get the error Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'. All I'm writing out is...

BEGIN TRANSACTION
    INSERT INTO MyTable1(Field1, Field2, etc...) VALUES (Value1, Value2, etc...)
    INSERT INTO MyTable2(Field1, Field2, etc...) VALUES (Value1, Value2, etc...)
COMMIT TRANSACTION

This is in a stored query in Access. Why is it giving me this error if it is supported?

http://msdn.microsoft.com/en-us/library/office/bb208950(v=office.12).aspx

[Do we have transactions in MS Access](http://stackoverflow.com/questions/2078432/do-we-have-transactions-in-ms-access) Look at the answer provided by @David-W-Fenton - Quick answer: The jet engine does not support transactions as a stored query. — Tim Sanders, Jul 09 '13 at 17:08
It does, but not as a stored query. The MS documentation isn't fully clear on that part. — Tim Sanders, Jul 09 '13 at 17:10

score 4 · Accepted Answer · answered Jul 09 '13 at 19:35

Transactions are not supported in MSAccess when you are using them in a query. Try firing a function to create those queries, which you can do in a module and have it run on PageLoad or even an OnTimer event. You can use the BeginTrans and Rollback commands inside the module.

Access SQL Transactions?

1 Answers1