Questions That Should Be Asked More Frequently

[Previous] [Next]

Q. I can handle optimistic updating conflicts once, but I don't want to continually trap for them. Is there any way I can make sure that my second update attempt succeeds?

A. You can use transactions to accomplish this if you're working with a transactional database. This will prevent other users from changing the record while you resync and update. The following code begins a transaction before resynchronizing the records whose updates failed because of conflicts:

 rsCustomers.Filter = adFilterConflictingRecords cnDatabase.BeginTrans rsCustomers.Resync adAffectGroup, adResyncUnderlyingValues 'Resolve conflicts.  rsCustomers.UpdateBatch cnDatabase.CommitTrans 

After resynchronizing those conflicts, resolve your conflicts within the Recordset object, and then call Recordset.UpdateBatch to submit the changes and Connection.CommitTrans to commit the transaction. Because you retrieve the current contents of the conflicting records within a transaction, that data is locked for the lifetime of the transaction and can be modified only within that transaction. As a result, there is no chance of another user modifying that data and causing conflicts when you call UpdateBatch this second time. Keep in mind that the greatest benefit in using transactions is also the greatest drawback: the data is locked for the lifetime of the transaction. See Chapter 3 for more on using transactions.

Q. I think I found a way to avoid conflicts. I set the Update Criteria property to adCriteriaKey, and now I rarely see update conflicts. But I'm a little worried. Was this a good idea?

A. Probably not. You're correct in assuming that by setting Update Criteria to adCriteriaKey you'll avoid generating conflicts. However, you're likely to overwrite changes made by other users. Reread the section "Controlling the Criteria in the WHERE Clause" in Chapter 10 to make sure you understand why you'll avoid generating conflicts by using the property this way. Generally, you don't want to overwrite other users' changes.