Step 6: Conclude the Update (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
Imagine that the batch update concluded with errors. How you resolve the errors depends on the nature and severity of the errors and the logic of your application. However, if the database is shared with other users, one typical error is that someone else modifies the field before you do. This type of error is called a conflict. ADO detects this situation and reports an error.
This step in the tutorial has two parts: If there are no update errors, then the data source reflects the update changes. The transaction is committed. Committing the transaction finalizes and ends the transaction.
The code to accept the update looks like this:
cnn.CommitTrans
If there are update errors, they will be trapped in an error-handling routine. Filter the Recordset with the adFilterConflictingRecords constant so only the conflicting rows are visible. The error-resolution strategy is merely to print the author's first and last names (au_fname and au_lname), then roll back (that is, undo) the transaction. Rolling back the transaction discards any successful updates and ends the transaction.
The code to reject the update looks like this:
rst.Filter = adFilterConflictingRecords
rst.MoveFirst
Do While Not rst.EOF
Debug.Print "Conflict: Name = "; rst!au_fname; " "; rst!au_lname
rst.MoveNext
Loop
cnn.RollbackTrans
After the update concludes, the Recordset and Connection objects are closed and the example exits. The code looks like this:
rst.Close
cnn.Close
This is the end of the descriptive tutorial.