Step 6: Conclude the Update (ADO Tutorial)

See Also   

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 error 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.

In this tutorial, this step has two parts: If there are no update errors, commit the transaction. This concludes the update.

If there are 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 the transaction, discarding the successful updates. This concludes the update.

...
conn.CommitTrans
...
On Error
rs.Filter = adFilterConflictingRecords
rs.MoveFirst
Do While Not rs.EOF
   Debug.Print "Conflict: Name: " & rs!au_fname " " & rs!au_lname
   rs.MoveNext
Loop
conn.Rollback
Resume Next
...

This is the end of the tutorial.