BatchSize and UpdateOptions Properties Example

This example uses the BatchSize and UpdateOptions properties to control aspects of any batch updating for the specified Recordset object.

Sub BatchSizeX()

   Dim wrkMain As Workspace
   Dim conMain As Connection
   Dim rstTemp As Recordset

   Set wrkMain = CreateWorkspace("ODBCWorkspace", _
      "admin", "", dbUseODBC)
   ' This DefaultCursorDriver setting is required for
   ' batch updating.
   wrkMain.DefaultCursorDriver = dbUseClientBatchCursor

   Set conMain = wrkMain.OpenConnection("Publishers", _
      dbDriverNoPrompt, False, _
      "ODBC;DATABASE=pubs;UID=sa;PWD=;DSN=Publishers")
   ' The following locking argument is required for
   ' batch updating.
   Set rstTemp = conMain.OpenRecordset( _
      "SELECT * FROM roysched", dbOpenDynaset, 0, _
      dbOptimisticBatch)

   With rstTemp
      ' Increase the number of statements sent to the server
      ' during a single batch update, thereby reducing the
      ' number of times an update would have to access the
      ' server.
      .BatchSize = 25

      ' Change the UpdateOptions property so that the WHERE
      ' clause of any batched statements going to the server
      ' will include any updated columns in addition to the
      ' key column(s). Also, any modifications to records
      ' will be made by deleting the original record
      ' and adding a modified version rather than just
      ' modifying the original record.
      .UpdateOptions = dbCriteriaModValues + _
         dbCriteriaDeleteInsert

      ' Engage in batch updating using the new settings
      ' above.
      ' ...

      .Close
   End With

   conMain.Close
   wrkMain.Close

End Sub