Transactions in Access (1 Viewer)

[MikeAngelastro]

Hi,

I'm using Access 2002 and I want to wrap a few things in a transaction. But things are not happening as I am expecting. For instance, within a transaction I append one record to a table. Although the table may have 100,000 records, five of them may be related to the same item and one of the fields represents quantity. My next step is to do a DSum to get the total quantity for the item. However, the quantity associated with the one record I added is not included in the total. Do I have to commit the append first?

When I try to nest transactions, the tables are not even recognized as being in the database unless I remove the transactions.

This transaction thing appears to be quite limited and I'm wondering just how useful it really is.

I looked for a good summary on transactions and not even MS has one.

Does anyone have any knowledge to share with me on transactions?

Thanks,

Mike

 

[dynamictiger]

I haven't used transactions yet. However, I can comment based on experience in other areas.

You must commit your append first.

Even though you have committed your append most probably the DSum will still not include your most recent addition. I had to use movelast to retrieve the value just added. Why this is so I have no idea, it is something to do with the Access engine method of adding stuff.

HTH

 

[WayneRyan]

Mike,

I've only used them with ORACLE. It's just a way of stepping
through a process posting milestones as you go. At any point
the data added, modified, or created is not available until that
transaction is completed.

It's purpose is to allow you to define a process, say updating
an inventory. If halfway through the update, you lose power,
the transactions are not final and you can restart the process
over again.

I'm sure that when Pat looks in later she'll have a much
better (i.e. understandable) explanation.

Wayne

 

[Pat Hartman]

Your explaination of transactions is correct Wayne. I haven't had occasion to use them with Access but I am quite familiar with how they work in a mainframe environment.

I believe that there are differences in how transaction processing is supported in ADO vs DAO. Make sure you are reading the correct documentation for the access method you are using.

MikeAngelastro, if you want to check the total of a set of records including the one you are adding, query the set already there and add your new value manually. If the total exceeds the valid amount, roll back the transaction and don't add the new record.

 

[MikeAngelastro]

I want to thank all you for your explanations. I will work through them to learn how to better use transactions.


Thanks,

Mike

 

[MikeAngelastro]

Hi,

I learned a few things about transactions in Access.

Always set a record set object to nothing after closing it. If you use a recordset object name, then close it, then begin a transaction, and then use the same recordset object name, the data in the second recordset object will not be visible to the code - even though the EOF property is false. To fix this the first recordset object must be set to nothing before beginning the transaction.

If you append records to a table in a transaction, the appended records are not visible to the code until the transaction has been commited - unless the transaction is nested. If the transaction is nested, the whole nest must be commited before the appended records are visible to the code. This is also true of deletes and updates.

Thanks,

Mike

 

[MikeAngelastro]

One more thing about transactions:

After you commit or rollback a transaction, do NOT close the workspace it was within. Doing so disables the form's RecordsetClone functionality.

Thanks,

Mike

 

[WayneRyan]

Mike,

No, thank you!

Wayne

 

[snicker]

Any way to see the code for a transaction? even a simple one

 

[MikeAngelastro]

Hi Snicker,

I just saw your question. I hope it is not too late. Here is some code.

Sub SampleTran()
On Error GoTo HandleError

Dim ws As Workspace
ws.BeginTrans

'Code to copy some rows from Table A to Table B.

'Code to delete same rows in Table A.

ws.CommitTrans

Exit Sub

HandleError:
ws.Rollback

End Sub

The idea is that if there is an error, everything is rolled back.

Mike

 

[snicker]

thats great thanks!

 

[MikeAngelastro]

Hi,

Other problems with using transactions in Access are:

1. The DoCmd statements like RunSQL or OpenQuery are not affected by Rollback. This means that you should not use them in transactions. The simplest way around this problem is to use QueryDef.Execute statements instead. These, assuming that they are action queries, will be rolled back if a Rollback occurs.

2. Stepping through your code to debug it is a problem because, until a transaction is committed, you will not be able to look at a table to see if it had been updated. The way to deal with this is to comment out the transaction statments, debug the code, and then when you are sure that program works uncomment the transaction code. A useful technique is to have a Boolean debug constant that the code will look at before creating the transaction. So if you need to debug, set this constant = True. When done set it to False.

3. According to some of my reading, doing any data updating is faster inside a transaction. If this is true, transactions may be useful in spite of the caveats. In addition, your program can recover if an error occurs.

Does anyone have any salient information to add?

Thanks,

Mike

 

[Pat Hartman]

Thank you for reporting back. Not many people use transactions so it is hard to get information.