Batching Updates and Insertions

Batching Updates

The following example demonstrates how to use a transaction in DAO to perform multiple updates in a batch. It uses three pass-through queries in a transaction to transfer money from a savings account to a checking account. The application (the client) sends queries and updates to the server and retrieves the data it needs.

Sub TransferFunds ()
Dim MyWS As Workspace, MyDB As Database, MyQuery As QueryDef

On Error GoTo TransferFailed
    
Set MyWS = DBEngine.Workspaces(0)
Set MyDB = MyWS.OpenDatabase("somedb.mdb")
MyWS.BeginTrans                ' Begin transaction.
' Create temporary pass-through query.
Set MyQuery = MyDB.CreateQueryDef("")
MyQuery.Connect = "ODBC;DSN=Bank;UID=teller;DATABASE=access"
MyQuery.ReturnsRecords = False
MyQuery.SQL = "UPDATE Accounts SET Balance = Balance - 100
 _WHERE AccountID = 'SMITH_SAV'"
MyQuery.Execute                ' Subtract from savings account.
MyQuery.SQL = "UPDATE Accounts SET Balance = Balance + 100
 _WHERE AccountID = 'SMITH_CHK'"
MyQuery.Execute                ' Add to checking account.
MyQuery.SQL = "INSERT INTO LogBook (Type, Source, Destination,
 _Amount) VALUES ('Transfer', 'SMITH_SAV', 'SMITH_CHK', 100)"
MyQuery.Execute                ' Log transaction.
MyWS.CommitTrans                  ' Commit transaction.
MyDB.Close
Exit Sub

TransferFailed:
    MsgBox Error$
    MyWS.Rollback        ' If one operation fails, roll them all back.
    Exit Sub
End Sub

Note the use of the CreateQueryDef method to create a temporary pass-through query. This technique introduces the minimum possible overhead for the Jet database engine, while still making use of transactions transparently through Microsoft Basic. However, it limits functionality to what the server provides, and it can't be as easily parameterized.

Batching Insertions

If your application provides data-entry forms for multiple records of data, such as an order entry system, you can improve performance and robustness by saving new records in a local holding table and then transferring batches of records from the holding table to the server all at once within a transaction.

To insert a batch of records on a server using a transaction:

1. Create empty local tables containing the same fields as your remote tables.
2. Create a data-entry form based on these local tables.
3. On the form, create a command button labeled "Post Records" for sending records to the server.
4. Create an event procedure for the button's Click event that copies the local records to the server tables and deletes the records from the local tables. The event procedure should perform all insertions and deletions within a transaction.

For example, suppose you have an Orders form containing an Order Details subform based on two local tables — LclOrders and LclOrderDetails. The user enters a new order and a group of detail records in the subform. When the user clicks the Post Records command button on the Orders form, the following procedure is executed:

Sub PostRecords_Click ()
    Dim MyWS As Workspace, MyDB As Database
    On Error GoTo TransferFailed
    
    Set MyWS = DBEngine.Workspaces(0)
    Set MyDB = MyWS.OpenDatabase("somedb.mdb")

    MyWS.BeginTrans                ' Begin transaction.
    MyDB.Execute "INSERT INTO RmtOrdersEmpty SELECT * 
     _FROM LclOrders", dbFailOnError
    MyDB.Execute "INSERT INTO RmtOrderDetailsEmpty SELECT *
     _FROM LclOrderDetails", dbFailOnError
    MyDB.Execute "DELETE FROM LclOrders"
    MyDB.Execute "DELETE FROM LclOrderDetails"
    MyWS.CommitTrans            ' Commit transaction.
    MyDB.Close
    ...                        ' Clear form for next order entry.
    Exit Sub

TransferFailed:
    MsgBox Error$
    MyWS.Rollback   ' If one operation fails, roll them all back.
    Exit Sub
End Sub

This example clears the data entry form after deleting the records from the local tables to present the user with a blank form for the next entry. In addition, the dbFailOnError option is used with the two append queries so that an error occurs if any part of either query fails. The error handler then rolls back any changes made.

This procedure doesn't insert the records directly into the tables on the server. RmtOrdersEmpty and RmtOrderDetailsEmpty aren't the remote tables; instead, they are the following queries, which are based on the attached remote tables but return no records:

SELECT * FROM RmtOrders WHERE False
SELECT * FROM RmtOrderDetails WHERE False

Inserting records into empty queries on attached remote tables provides the fastest possible speed, similar to opening a recordset using the OpenRecordset method with the dbAppendOnly option.