Modifying Rows of Recordsets

You can perform CRUD (Create, Read, Update, and Delete) operations on most dynamic Recordsets with VBA code. To test whether a Recordset is editable, check the value of the Updatable property. You can't edit Forward-Only or Snapshot-type Recordsets, and most dynamic queries with INNER or OUTER JOINs aren't editable.

Editing and Adding Rows

To edit (update) the current row, apply the Edit method and then set the new value of each field you want to change. The changes don't become permanent until you apply the Update method. Following is the generalized syntax for an update operation on fields other than the primary key (usually Field(0)) of the current row:

rsName.Edit
rsName.Fields(1) = "New String Value"
rsName.Fields(2) = New Numeric Value
…
rsName.Fields(n) = "New String Value"
rsName.Update

You replace the rsName.Edit line with rsName.AddNew to insert a new row into the Recordset. Fields to which you don't assign values are supplied with default values, if specified; otherwise, fields contain Null values. If the primary key field uses the AutoNumber data type, don't attempt to assign the field a value. Figure 30.8 shows examples of Edit and AddNew operations on rsTemp in the Debug Window.

Deleting Rows

Deleting the current row is simple—just invoke the Delete method. Applying the Update method isn't required. You receive no warning that you're about to irrevocably remove a record from the underlying table. (The deleted row doesn't disappear from the underlying table until you move the record pointer.) The last three statements in Figure 30.8 illustrate deleting the Ersatz Frankfurter grüne Soße row and confirming that the row is gone (forever). The last row of the table becomes Original Frankfurter grüne Soße.

Using Jet Transactions

Access uses implicit transactions with any referential integrity constraints you've applied to the database tables. Transactions are important when you're updating, inserting, or deleting records in a table with dependencies on other table(s). For instance, if you delete a record of the Products table, records in the Order Details table that refer to the deleted product become orphan records. An orphan record is a record in a related or dependent table (Order Details) that has no corresponding record in a base or primary table (Orders).

The Northwind database enforces referential integrity on the ProductID field of the Product and Order Details tables, but doesn't specify cascading updates or cascading deletions. Referential integrity prevents deletion of a product with dependent orders. ProductID is an AutoNumber field, so you can't alter its value, and cascading updates don't apply. It's possible to manually delete all dependent Order Detail records, and then manually delete the corresponding Products record. It's not a good database programming practice, however, to alter the information on customers' past and pending orders.

Use explicit transactions where a single operation involves multiple modifications of a single table, simultaneous modifications to two tables in a single database that aren't subject to referential integrity constraints, or simultaneous modifications to tables in more than one database. An explicit transaction assures that all operations complete (called a commit) or all operations that completed up to the point of failure are reversed (called a rollback).

Jet transactions meet the ACID test. The ACID acronym is derived from the following requirements for transactions in a relational database:

• Atomicity requires all updates of a specific transaction to be committed (made durable) or all updates to be aborted and rolled back to values existing immediately before execution.

• Consistency means that all operations result in data entities that preserve consistency constraints on the tables participating in the update.

• Isolation requires that a transaction's intermediate (uncommitted work) results not be visible to other concurrent transactions operating on the same tables.

• Durability means that committed updates to managed resources (such as a database record) survive failures, including communication failures, process failures, and server system failures. Transactional logging, such as that used by SQL Server and other client/server RDBMSs, even allows you to recover the durable state after disk media failures.

The three Jet transaction instructions—BeginTrans, CommitTrans, and Rollback—are methods of the Workspace object, not the Database object. Use of the Workspace object is required so that transactions can span multiple Database objects opened within a single Workspace object.

Listing 30.1 contains the generalized code for processing a Jet transaction that creates an invoice in another database when an order is shipped.

Code Listing 30.1. Code Framework for a Jet Transaction Spanning Two Databases

							Private wsCurrent As Workspace
Private dbSales As Database        'Current database
Private dbAccounting As Database   'External database
Private rsdOrders As Recordset     'Dynaset-type
Private rsdInvoices As Recordset   'Dynaset-type

Private Sub OrdersToInvoices()
   Set wsCurrent = DBEngine.Workspaces(0) 'Default Workspace
   Set dbSales = wsCurrent.Databases(0)   'Default sales database
   Set dbAccounting = wsCurrent.OpenDatabase ("filename.mdb")
   Set rsdOrders = dbSales.OpenRecordset("SQL Statement1")
   Set rsdInvoices = dbAccounting.OpenRecordset("SQL Statement2")
   On Error GoTo errRollback
   wsCurrent.BeginTran
      rsdOrders.Edit
      'Edit operations on a shipped order
      …
      rsdOrders.Update

      rsdInvoices.AddNew
      'AddNew operation to create an invoice from the order
      …
      rsdInvoices.Update
   wsCurrent.CommitTran
   MsgBox "New invoice created."

errExitSub:
   rsdOrders.Close
   rsdInvoices.Close
   Exit Sub

errRollback:
   wsCurrent.Rollback
   MsgBox "Can't create invoice."
   Resume errExitSub
End Sub
						

The wsCurrent.BeginTran method initiates transaction processing. Jet stores the changes to both Recordsets in a temporary buffer until invoking the wsCurrent.CommitTran method, which makes permanent changes to the underlying tables. If an error occurs when invoking the rsdOrder.Update or rsdInvoices.Update methods, execution breaks to the errRollback: error handling code. The wsCurrent.Rollback method clears the temporary buffer and no changes to tables occur.

Transactions are especially useful to assure that multiple line items for an order are added to a new order. Without a transaction, it's possible for a new order to be created and some (but not all) line items added. With a transaction, if a failure occurs that prevents adding all line items, the entire transaction for the new order is rolled back.