Handling Collisions

When you attempt to update a group of records in a single batch operation, it is possible that other users are editing one or more records you are trying to update, causing a collision. A collision occurs when a batch update attempts to update a record after another user has changed the record.

When you call the Update method, you can choose either to force all the changes you have made into the database automatically, or to examine each conflicting record and determine whether to overwrite the changed value with your new value, keep the changed value, or return to the original value that the record had before it was edited. To force all changes, call the Update method with the force argument set to True. To examine each conflicting record and decide which value to keep, call the Update method with the force argument set to False (the default value). You can then use the BatchCollisions and BatchCollisionCount properties to handle any collisions.

If you’ve decided to handle collisions, examine the BatchCollisions property on the Recordset object. The BatchCollisions property returns an array that stores bookmarks pointing to records in the Recordset object on which a collision occurred. Each time a collision occurs during a batch update, a bookmark for the record is added to the array returned by the BatchCollisions property. You can then move to each of these bookmarks and examine the following properties of the Field object of the current record.

Caution Calling the Update method and specifying the dbUpdateBatch constant for the type argument and True for the force argument forces all your changes into the data source and overwrites any changes that other users made to the records. For this reason, it is safer to call the Update method without specifying the force argument, and then resolve collisions individually by using the array returned by the BatchCollisions property along with the Value, OriginalValue, and VisibleValue properties.

The following example shows how to use the array returned by the BatchCollisions property to examine conflicting records after a batch update.

Function BatchNoForceChanges()
	Dim wrk As Workspace
	Dim rst As Recordset, cnn As Connection
	Dim fld As Field
	Dim strConnect As String, intI As Integer
	Dim varCollision As Variant

	' Create ODBCDirect workspace.
	Set wrk = DBEngine.CreateWorkspace("ODBCDirect", "Admin", "", dbUseODBC)
	' Set default cursor driver to dbUseClientBatchCursor.
	wrk.DefaultCursorDriver = dbUseClientBatchCursor
	' Create connection string.
	strConnect = "ODBC;DSN=Pubs;DATABASE=Pubs;UID=sa;PWD=;"
	' Open connection.
	Set cnn = wrk.OpenConnection("", dbDriverNoPrompt, False, strConnect)

	' Open recordset for batch optimistic updating.
	Set rst = _
		cnn.OpenRecordset("SELECT * FROM sales", dbOpenDynaset, 0, dbOptimisticBatch)
	' Change all records in local recordset.
	Do Until rst.EOF
		rst.Edit
		rst!qty = rst!qty + 1
		rst.Update
		rst.MoveNext
	Loop

	' Update with force argument of False (default).
	' If another user has made changes since the updated records were
	' edited, update fails, and the BatchCollisionCount and BatchCollisions
	' properties will indicate the number of conflicts and the conflicting records.
	rst.Update dbUpdateBatch, False

	' Check for collisions.
	For intI = 0 To rst.BatchCollisionCount - 1
		rst.Bookmark = rst.BatchCollisions(intI)
			For Each fld In rst.Fields
				If fld.OriginalValue <> fld.VisibleValue Then
					Debug.Print fld.Value
					Debug.Print "OriginalValue: " & fld.OriginalValue
					Debug.Print "VisibleValue: " & fld.VisibleValue
					Debug.Print "Value: " & fld.Value
					.
					.	' Handle the collision as you want to.
					.
				End If
		Next fld
	Next intI
End Function 

Note If a collision occurs because the primary key field has been modified by another user, using the OriginalValue and VisibleValue properties to view the field value will fail and return errors. This is because querying these properties implicitly re-queries the recordset and marks the copy of the record as deleted in the client batch cursor.