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