Confirming Recordset Updates Succeed

[BarkerD]

I am writing some transactions involving multiple recordsets and multiple tables.

I have begun writing with the BeginTrans method of a Workspace object.

I would like to group a number of recordset updates into one transaction, but I don't know how to confirm that all updates succeed before I use the CommitTrans method. If any transaction fails, I want to use the Rollback Method.

Can anyone help me with this validation? I'm stuck.

Thanks in advance.

Duane Barker

 

[Keith P]

I am not sure that Access provdies 'confirm updates' as some others databases do.

Set a boolean true as the first instruction following beginTrans, e.g. transStarted = true. Write all your update code. Perform commitTrans and set transStarted = false (i.e. finished).

In your error handling provide for any expected errors, else check if transStarted = true (i.e. still updating). If = true then rollBack

I hope this is useful

 

[BarkerD]

I have been led to believe that once I perform my CommitTrans, that I will be unable to Rollback.

Every time I use the Update method on a recordset object, is there a way to check that it succeeded? This is important, since I have a multi-user environment, and I may get record=locking conflicts.

If all updates succeed, then I want to use the CommitTrans, else I will Rollback and undo the Record.

thanks again

Duane Barker

 

[Keith P]

Once you have committed the transaction you cannot roll it back.

What I am suggesting is the other side of the coin, instead of checking that the update succeeded check whether it failed. If it failed then rollback.

Whilst the beginTrans to commitTrans phase is running record locks have to be applied otherwise you could find that another user has locked a table that you need to rollback.

If your transaction is such that you are likely to tie up half the database with record locks then consider running your code in exclusive mode when others aren't using the database.

 

[BarkerD]

Thanks for your help so far.

I have written most of my update code.

My assumption from your feedback so far is that I write an error handling statement, and include the check for transStart = false.

I understand that much. The problem I have is identifying if an error has occured. What sort of statments would I need to check for a locking conflict, or even worse, if a connection is lost be the server.(I am having all kinds of problems with my linked database losing connection to the server, and then records being corrupted)

<EDIT> Umm found the solution. On Error .... Doh! Thanks for the help. The Code is working beautifully

Regards. Duane Barker <END EDIT>

Duane Barker

[This message has been edited by BarkerD (edited 10-19-2000).]