Using Transactions

[cascot]

If I want to do something like the following:

1. Add new records into Table_A

2. Update the value of [Field1] in Table_A to = "SET"

3. Update the value of [Field2] in Table_A where [Field1] is equal to "Set"

Can I do all of that inside a transaction, or will it be the case that the updated values of [Field1] will not be visible to the update query in point 3. as the transaction has not yet been committed?

I already have something similar, but one of the queries is failing with an ODBC timeout (it's an SQL Server database) as a table it uses is locked. That same table has been updated by queries earlier in the Transaction, so I think it is because I have not yet committed the transaction (so the updated table remains locked).

If this last query fails I want to rollback the whole lot. How can I get around this?

Nested Transactions maybe?

 

[jsteph]

You need to use the Connection's transaction methods, not Jet's, and of course, all updates must be done via the connection, ie., you can't use any Jet queries or dao on odbc Linked tables for this to work. Within the transaction, it will see it's own pending data changes.

Other connections, based on whether the connection is set to dirty read (read uncommitted--I'm not sure what MS's terms are for this but they should be somewhat self-explanatory) it will be able to see those changes, but I think most dbms's defaults are 'read committed', so other connections won't be able to see the changes your connection's transactions make until they're committed.
--jsteph