UpdateBatch and CancelBatch Methods Example (VB)

This example demonstrates the UpdateBatch method in conjunction with the CancelBatch method.
'BeginUpdateBatchVB
Public Sub UpdateBatchX()

    'To integrate this code
    'replace the data source and initial catalog values
    'in the connection string

    'connection and recordset variables
   Dim rstTitles As ADODB.Recordset
   Dim Cnxn As ADODB.Connection
   Dim strCnxn As String
   Dim strSQLTitles As String
    'record variables
   Dim strTitle As String
   Dim strMessage As String

   ' Open connection
   Set Cnxn = New ADODB.Connection
   strCnxn = "Provider=sqloledb;Data Source=MyServer;Initial Catalog=Pubs;User Id=sa;Password=; "
   Cnxn.Open strCnxn

    ' open recordset for batch uodate
   Set rstTitles = New ADODB.Recordset
   strSQLTitles = "titles"
   rstTitles.Open strSQLTitles, Cnxn, adOpenKeyset, adLockBatchOptimistic, adCmdTable
   
   rstTitles.MoveFirst
   ' Loop through recordset and ask user if she wants
   ' to change the type for a specified title.
   Do Until rstTitles.EOF
      
      If Trim(rstTitles!Type) = "psychology" Then
         strTitle = rstTitles!title
         strMessage = "Title: " & strTitle & vbCr & _
            "Change type to self help?"

         If MsgBox(strMessage, vbYesNo) = vbYes Then
            rstTitles!Type = "self_help"
         End If
      End If

   rstTitles.MoveNext
   Loop

   ' Ask the user if she wants to commit to all the
   ' changes made above.
   If MsgBox("Save all changes?", vbYesNo) = vbYes Then
      rstTitles.UpdateBatch
   Else
      rstTitles.CancelBatch
   End If

   ' Print current data in recordset.
   rstTitles.Requery
   rstTitles.MoveFirst
   Do While Not rstTitles.EOF
      Debug.Print rstTitles!title & " - " & rstTitles!Type
      rstTitles.MoveNext
   Loop

   ' Restore original values because this is a demonstration.
   rstTitles.MoveFirst
   Do Until rstTitles.EOF
      If Trim(rstTitles!Type) = "self_help" Then
         rstTitles!Type = "psychology"
      End If
      rstTitles.MoveNext
   Loop
   rstTitles.UpdateBatch

   rstTitles.Close

End Sub
'EndUpdateBatchVB
UpdateBatch and CancelBatch Methods Example (VB)

See Also
