Transactions and linked tables A2007?

[spikepl]

The description of "Transaction statement (Microsoft Access SQL)" in online help (Access 2007) ends with the following sentence:

Transactions are not supported for linked tables.

I have a nicely working DB, using DAO throughout, and splitting the DB into a frontend and backend would be advantageous for maintenance reasons. But, I have transactions in one particular corner .... and the quoted statement is a bit of a stumbling block, if I understand it correctly.

Is there any other way of having transactions for a split DB?

 

[gemma-the-husky]

what do the transactions do?

can you not replace them with a managed code/process?

 

[spikepl]

I have a bunch of tables containing data from input. The data has to be stored in two tables having a one-to-many relations, so that I make a new record in Table 1, add corresponding records in Table2, and then start over, if necessary. Table1 & 2 are the basis for further processing, and already contain data. Wrapping all this in a transaction is intended to make sure that I do not wind up with any corruption, so I would wind up with a record in Table1 but none in Table2. The source of corruption could be a system crash mid-way though the update... perhaps there is a better way of dealing with it? ( a crash could admittedly also happen during the execution of committed transaction)

But in any case, I also got curious. Is there no way to do transactions with linked tables?

 

[spikepl]

Bump.

Any more views on this subject?

 

[Banana]

Did you actually try a test code? An easy way to test this is to create a transaction then immediately rollback and seeing if the new row appears anyway.

I'm pretty sure that the help file is inaccurate and meant to say "ODBC linked tables" and not just any linked table.

 

[spikepl]

Wauw - I threw an error in the midst of the update (in the split version), and it DID roll it back!!!

Never thought of questioning the documenation, apart from mentally bitching about being left by the documenation with no suggestions as to what to do

 

[spikepl]

Forgot to thank you - so thank you !

 

[Banana]

Thanks for reporting back - it was too long ago since I played with transactions and was sure I did do it with a split database. Yes, sometime the help documentation may be a bit off or inaccurate or daresay, wrong? In this case, I am sure it meant that one shouldn't use transactions with linked ODBC table (you would need to use the server-side language; essentially writing a passthrough query to initiate a transaction and performing the actions then either commit/rollback, but it's probably easier to write a stored procedure and just call it anyway)

Best of luck!