UpdateOptions Property

Applies To

Dynamic-Type Recordset object, Dynaset-Type Recordset object, Forward-Only-Type Recordset object, Recordset object, Snapshot-Type Recordset object.

Description

Sets or returns a value that indicates how the WHERE clause is constructed for each record during a batch update, and whether the batch update should use an UPDATE statement or a DELETE followed by an INSERT (ODBCDirect workspaces only).

Settings And Return Values

The setting or return value is a Long that can be any of the following constants.

Constant	Description

dbCriteriaKey	(Default) Uses just the key column(s) in the WHERE clause.
dbCriteriaModValues	Uses the key column(s) and all updated columns in the WHERE clause.
dbCriteriaAllCols	Uses the key column(s) and all the columns in the WHERE clause.
dbCriteriaTimeStamp	Uses just the timestamp column if available (will generate a run-time error if no timestamp column is in the result set).
dbCriteriaDeleteInsert	Uses a set of DELETE and INSERT statements for each modified row.
dbCriteriaUpdate	(Default) Uses an UPDATE statement for each modified row.

Remarks

When a batch-mode Update is executed, DAO and the client batch cursor library create a series of SQL UPDATE statements to make the needed changes. An SQL WHERE clause is created for each update to isolate the records that are marked as changed by the RecordStatus property. Because some remote servers use triggers or other ways to enforce referential integrity, is it often important to limit the fields being updated to just those affected by the change. To do this, set the UpdateOptions property to one of the constants dbCriteriaKey, dbCriteriaModValues, dbCriteriaAllCols, or dbCriteriaTimeStamp. This way, only the absolute minimum amount of trigger code is executed. As a result, the update operation is executed more quickly, and with fewer potential errors.

You can also concatenate either of the constants dbCriteriaDeleteInsert or dbCriteriaUpdate to determine whether to use a set of SQL DELETE and INSERT statements or an SQL UPDATE statement for each update when sending batched modifications back to the server. In the former case, two separate operations are required to update the record. In some cases, especially where the remote system implements DELETE, INSERT, and UPDATE triggers, choosing the correct UpdateOptions property setting can significantly impact performance.

If you don't specify any constants, dbCriteriaUpdate and dbCriteriaKey will be used.

Newly added records will always generate INSERT statements and deleted records will always generate DELETE statements, so this property only applies to how the cursor library updates modified records.

Example

See the BatchSize property example.