Step 5: Update the Data (ADO Tutorial)
You are Here...
- Make a connection to a data source.
- Optionally, create an object to represent an SQL query command.
- Optionally, specify values in the SQL command as variable parameters.
- Execute the command. If the command is row-returning, store the rows in a storage object.
- Optionally, navigate, examine, manipulate, and edit the data.
- If appropriate, update the data source with changes from the storage object. Optionally, embed the update in a transaction.
- If a transaction was used, accept or reject the changes made during the transaction. End the transaction.
Discussion
You've just changed the data in several rows of the Recordset. ADO supports two basic notions relating to the addition, deletion, and modification of rows of data.
The first notion is that changes aren't immediately made to the Recordset; instead, they are made to an internal copy buffer. If you decide you don't want the changes, then the modifications in the copy buffer are discarded. If you decide to keep the changes, then the changes in the copy buffer are applied to the Recordset.
The second notion is that changes are either propagated to the data source as soon as you declare the work on a row complete (that is, immediate mode). Or, all changes to a set of rows are collected until you declare the work for the set complete (that is, batch mode). The LockType property determines when the changes are made to the underlying data source. The CursorLocation property can affect which LockType settings are available. For instance, the adLockPessimistic setting is not supported if the CursorLocation property is set to adUseClient.
In immediate mode, each invocation of the Update method propagates the changes to the data source. In batch mode, each invocation of Update or movement of the current row position saves the changes to the copy buffer, but only the UpdateBatch method propagates the changes to the data source. The Recordset was opened in batch mode (adLockBatchOptimistic) so updates will be made in batch mode.
Optionally, updates can be performed in a transaction. A transaction establishes an environment where the operations in the transaction either all succeed, or their effects are all canceled.
A common example of a transaction is a banking application where an operation to deduct an amount from one account, and another operation to deposit the same amount to another account, must both succeed. If one operation fails, then the other must be undone; otherwise the accounts will be out of balance.
Transactions typically allocate and hold limited resources on the data source for long periods of time. For that reason it is advisable that a transaction exist for as brief a period as possible. (That is why this tutorial didn't begin the transaction as soon as the connection was made.)
Practically speaking, this tutorial does not require a transaction, but one is included for purposes of illustration. The code to start a transaction and perform a batch update looks like this:
cnn.BeginTrans
rst.UpdateBatch