BatchSize Property

Applies To

Dynamic-Type Recordset object, Dynaset-Type Recordset object, Forward-Only–Type Recordset object, Recordset object, Snapshot-Type Recordset object.

Description

Sets or returns the number of statements sent back to the server in each batch (ODBCDirect workspaces only).

Settings And Return Values

The setting or return value is a Long that indicates the number of batched statements sent the server in a single batch update. The default value is 15.

Remarks

The BatchSize property determines the batch size used when sending statements to the server in a batch update. The value of the property determines the number of statements sent to the server in one command buffer. By default, 15 statements are sent to the server in each batch. This property can be changed at any time. If a database server doesn't support statement batching, you can set this property to 1, causing each statement to be sent separately.

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