Using Batch Optimistic Updating

In many client/server applications, optimistic updates occur on a record-by-record basis. This usually happens with the following series of events:

1. A user edits a record.
   
   
2. The user tries to save the record.
   
   
3. The server attempts to place a lock on that record, and if successful, the record is updated. Otherwise, a lock violation is handled by the application.
   
   
4. The user moves to another record and the entire process is repeated.

Although this process works well for many applications, it is often more efficient to have the user edit multiple records that are cached locally and then submit these records to the server in a single batch for updating. This process is called batch optimistic updating. Note that this differs from performing transactions with the BeginTrans and CommitTrans methods. When using the BeginTrans and CommitTrans methods, data is sent to the server to be cached every time you use the AddNew or Update methods within the transaction. Then, data is written to the server database from the server’s cache when you use the CommitTrans method to commit the transaction.

Û To use batch optimistic updating

1. Create an ODBCDirect workspace.
   
   
2. Set the DefaultCursorDriver property of the workspace to dbUseClientBatchCursor.
   
   
3. Open a Connection or Database object from the ODBCDirect workspace.
   
   
4. Use the OpenRecordset method on the Connection or Database object to open a Recordset and specify the dbOptimisticBatch constant in the lockedits argument.

   Note When you don’t use the options argument, which precedes the lockedits argument, you must specify 0 for options.

5. Perform any edits to the Recordset object. All edits are cached locally.
   
   
6. When you are ready to update the data source, call the Update method on the Recordset object, specifying dbUpdateBatch for the type argument.

Note If you attempt a batch update while a record in that Recordset object is being edited by the user, the record being edited will automatically be updated before the batch update begins.

The following example illustrates how to use batch optimistic updating.

Function RunInBatch()
	Dim wrk As Workspace, cnn As Connection, rst As Recordset
	Dim strConnect As String

	' 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 on connection.
	Set rst = _
	cnn.OpenRecordset("SELECT * FROM sales", dbOpenDynaset, 0, dbOptimisticBatch)

	' Change all records in local recordset.
	With rst
		Do Until .EOF
			.Edit
			!qty = !qty + 1
			.Update
			.MoveNext
		Loop
		' Update all records in data source.
		.Update dbUpdateBatch
	End With
End Function

If multiple records have been edited locally, and you want to update the current record before you perform the batch update, you can call the Update method and specify the dbUpdateCurrentRecord constant for the type argument. This writes the current record to the data source without writing any other batch updates. This is illustrated in the following example.

' Edit and update first record.
	' Only first record is written back to data source.
	With rst
		.MoveFirst
		.Edit
		!qty = !qty + 2
		.Update dbUpdateCurrentRecord
		' Update remaining records in data source.
		.Update dbUpdateBatch
	End With