Taking Advantage of Disconnected Recordset s

Taking Advantage of Disconnected `Recordset`s

If you set the value of the Recordset's LockEdits property to adBatchOptimistic and the CursorType property to adKeyset or adStatic, you create a batch-type Recordset object that you can disconnect from the data source. You can then edit the Recordset object offline with a client-side cursor, reopen the Connection object, and send the updates to the data source over the new connection. A Recordset without an active connection is called a disconnected Recordset. The advantage of a disconnected Recordset is that you eliminate the need for an active server connection during extended editing sessions. Batch updates solve the Access front-end scalability issues mentioned at the beginning of the chapter.

Note

Unfortunately, you can't assign a disconnected Recordset to the Recordset property of a form or subform and take advantage of batch updates. Bound forms require an active connection to the database. You must write VBA code to handle updating, adding, and deleting records.

To learn more about updatability issues with disconnected Recordsets and the Client Data Manager (CDM) added by Access 2002, open Microsoft Knowledge Base article Q301987, "Using ADO in Microsoft Access 2002" and download the white paper.

Batch updates with disconnected Recordsets are stateless and resemble the interaction of Web browsers and servers when displaying conventional Web pages. The term stateless means that the current interaction between the client application and the server isn't dependent on the outcome of previous interactions. For example, you can make local updates to a disconnected Recordset, go to lunch, make additional updates as needed, and then send the entire batch to the server. A properly designed batch update application lets you close the application or shut down the client computer, and then resume the updating process when you restart the application.

Tip

Disconnected Recordsets minimize the effect of MSDE "five-user tuning" on the performance of Access online transaction processing (OLTP) applications. Batch updates execute very quickly, so most user connections remain open for a second or less.

Transaction processing with stored procedures or T-SQL statements that incorporate BEGIN TRANS...COMMIT TRANS...ROLLBACK TRANS statements are the better choice for OLTP operations on multiple tables, such as order-entry systems. It's possible for batch updates to succeed partially, which might result in a missing line item. You can use the Errors collection to analyze and potentially correct such problems, but doing so requires high-level VBA coding skills.

The Basics of Disconnecting and Reconnecting Recordsets

Following is an example of VBA pseudocode that creates and operates on a disconnected Recordset and then uses the UpdateBatch method to persist the changes in the data source:

 Set rstName = New ADODB.Recordset With rstName    .ActiveConnection = cnnName    .CursorType = adKeyset  .CursorLocation = adUseClient  .LockEdits = adBatchOptimistic  .Open ";SELECT * FROM TableName WHERE Criteria", Options:=adCmdText Set .ActiveConnection = Nothing 'Disconnect the Recordset  'Close the connection to the server, if desired  'Edit the field values of multiple records here  'You also can append and delete records  'Reopen the server connection, if closed Set .ActiveConnection = cnnName  .UpdateBatch 'Send all changes to the data source End With rstName.Close

If calling the UpdateBatch method causes conflicts with other users' modifications to the underlying table(s), you receive a trappable error and the Errors collection contains Error object(s) that identify the conflict(s). Unlike transactions, which require all attempted modifications to succeed or all to be rolled back, Recordset batch modifications that don't cause conflicts are made permanent in the data source.

An Example Batch Update Application

The frmBatchUpdate form of the ADOTest.mdb application and ADOTest.adp project demonstrates the effectiveness of batch updates with MSDE. For example, you can edit data, persist the edited disconnected Recordset as an ADTG or XML file, close the form (or Access), and then reopen the form and submit the changes to the server. A subform, sbfBatchUpdate, which is similar to the frmADO_Jet and frmADO_MSDE forms you created early in the chapter, displays the original and updated data. The subform is read-only; VBA code simulates user updates to the data. The example also demonstrates how to use VBA code to display the XML representation of a Recordset object in Internet Explorer (IE) 5+.

To give frmBatchUpdate a trial run, do this:

If you haven't installed the entire set of sample applications, copy ADOTest.mdb, ADOTest.adp, or both from the \Seua11\Chaptr30 folder of the accompanying CD-ROM to a ...\Program Files\Seua11\Chapter30 folder.
Verify that your local MSDE instance is running, and then open frmBatchUpdate, which connects to the NorthwindCS database, opens a Recordset, saves it in a local file named Batch.rst, and closes the connection. The subform displays the first 10 rows of seven fields of the Customers table by opening a Recordset from the local Batch.rst file (see Figure 30.19).

Figure 30.19. Opening frmBatchUpdate displays a disconnected Recordset opened on the Customers table for batch updates and saves the initial Recordset as Batch.xml.
Click the Update Disconnected Recordset button to replace NULL values in the Region cells with 123. The button caption changes to Restore Disconnected Recordset and the Send Batch Updates to Server button is enabled. The new values don't appear in the datasheet because the UpdateBatch method hasn't been applied at this point.
Click the Send Batch Updates to Server button to reopen the connection, execute the UpdateBatch method, and close the connection. The datasheet displays the updated Recordset returned by the server (see Figure 30.20).

Figure 30.20. Clicking Send Batch Updates to Server updates the server table and then retrieves the updated Recordset by opening and then closing another connection.
Click Restore Disconnected Recordset and Send Batch Updates to Server to return the Customers table to its original state. (Clicking Update and Restore Disconnected Recordset toggles the Region values in the local Recordset.)
Click the Open Batch.xml in IE 5+ button to launch IE with file:// path/Batch.xml as the URL. IE 5+'s XML parser formats the attribute-centric document and color-codes XML tags (see Figure 30.21).

Figure 30.21. IE 5+'s XML parser formats the XML document saved as Batch.xml. This example shows the Schema elements collapsed and the data elements expanded. (The Region attribute of the row element is in the updated state.)

If you update the local copy of the Recordset and don't send the changes to the server, you receive a message reminding you that changes are pending when you close the form. If you don't save the changes to the server and reopen the form, a message asks if you want to send the changes to the server before proceeding.

VBA Code in the `frmBatchUpdate` Class Module

The VBA code of the event-handling and supporting subprocedures of the frmBatchUpdate Class Module illustrates how to program many of the ADO properties and methods described in the preceding sections devoted to the Connection and Recordset objects. The Command object isn't used in this example, because the form opens Recordset objects on a temporary Connection object or from a copy of a Recordset persisted to a local file in ADTG format.

The `Form_Load` Event Handler

Listing 30.3 shows the VBA code for the Form_Load event handler. The first operation uses the VBA Dir function to determine whether the Batch.rst file exists; if so, response to the message specified by the MsgBox function determines whether existing updates are processed by the cmdUpdate_Click subprocedure or discarded.

Listing 30.3 Code for Saving the Initial `Recordset` Object

[View full width]

 Private Sub Form_Load()   'Open the connection, and create and display the Recordset   blnUseJet = False 'Set True to use the Jet provider   'Test for presence of the saved Recordset   If Dir(CurrentProject.Path & "\Batch.rst") <> "" Then    'File is present so updates are pending    If MsgBox("Do you want to send your changes to the server?", vbQuestion + vbYesNo, _       "Updates Are Pending for the Server") = vbYes Then      Call cmdUpdate_Click      Exit Sub    Else       Kill CurrentProject.Path & "\Batch.rst"    End If   End If    'Create a Form object variable for the subform    Set sbfBatch = Forms!frmBatchUpdate!sbfBatchUpdate.Form    Me.cmdBulkUpdate.SetFocus    Me.cmdUpdate.Enabled = False    Me.cmdOpenXML.Enabled = False    'Open a connection to the server    Call OpenConnection    'Create a Recordset for Batch Updates    strSQL = "SELECT CustomerID, CompanyName, Address, City, Region, PostalCode, Country FROM  Customers"    With rstBatch       Set .ActiveConnection = cnnBatch       .CursorType = adOpenStatic       .CursorLocation = adUseClient       .LockType = adLockBatchOptimistic       .Open strSQL    'Save the Recordset to a file    .Save CurrentProject.Path & "\Batch.rst", adPersistADTG    'Save an XML version    On Error Resume Next    Kill CurrentProject.Path & "\Batch.xml"    .Save CurrentProject.Path & "\Batch.xml", adPersistXML    On Error GoTo 0    Me.cmdOpenXML.Enabled = True    'Disconnect the Recordset    Set .ActiveConnection = Nothing    If .Fields("Region").Value = "123" Then      Me.cmdBulkUpdate.Caption = "Restore Disconnected Recordset"    Else      Me.cmdBulkUpdate.Caption = "Update Disconnected Recordset"    End If   End With   'Destroy the connection   cnnBatch.Close   Set cnnBatch = Nothing   'Open a local Recordset from the saved file   Call OpenRstFromFile   'Delete the source of the file Recordset   Kill CurrentProject.Path & "\Batch.rst"   Me.Caption = "Datasheet Contains Values from Server (Disconnected Recordset)" End Sub

Note

In a real-world application, you probably wouldn't delete a saved Recordset that contains updates. Instead of deleting the file with a Kill instruction, you would open the saved Recordset to permit continued editing.

The Set sbfBatch = Forms!frmBatchUpdate!sbfBatchUpdate.Form statement creates a Form object for the subform, so you can set property values for the sbfBatchUpdate subform by code of the frmBatchUpdate form in the OpenRstFromFile subprocedure. Combining the VBA code for forms and subforms in a single Class Module makes the code more readable.

For more information on the strange syntax to point to another Form or Report object, see "Referring to Access Objects with VBA," p. 1218.

After disabling the Send Updates to Server and Open Batch.xml in IE 5+ buttons, the code calls the OpenConnection subprocedure to create a temporary Connection object, creates a Recordset object with batch-optimistic locking, saves the Recordset to Batch.rst and Batch.xml, and disconnects the Recordset from the connection with the Set .ActiveConnection = Nothing statement. Finally the code closes the Connection, releases it from memory, calls the OpenRstFromFile subprocedure, and deletes the Batch.rst file.

The `OpenConnection` Subprocedure

The OpenConnection subprocedure (see Listing 30.4) accommodates a Jet database by setting the value of blnUseJet to True in the Form_Load event handler. By default, the code attempts to open the connection with integrated Windows security. If this attempt fails, the code attempts to use SQL Server security with the sa logon ID (UID=sa) and no password. (If you've secured the sa account, add the password for the account to PWD=.)

Listing 30.4 Connecting to a Jet Database or Use SQL Server or Integrated Windows Security to Connect to the Local MSDE Instance

 Private Sub OpenConnection()   'Specify the OLE DB provider and open the connection   With cnnBatch      If blnUseJet Then         .Provider = "Microsoft.Jet.OLEDB.4.0"         .Open CurrentProject.Path & "\Northwind.mdb", "Admin"      Else       On Error Resume Next       'Try integrated Windows security first       .Open "Provider=SQLOLEDB.1;Data Source=(local);" & _         "Integrated Security=SSPI;Initial Catalog=NorthwindCS"       If Err.Number Then           Err.Clear           On Error GoTo 0           'Now try SQL Server security           .Open "Provider=SQLOLEDB.1;Data Source=(local);" & _             "UID=sa;PWD=;Initial Catalog=NorthwindCS"        End If      End If    End With  End Sub

The `OpenRstFromFile` Subprocedure

The code for the OpenRstFromFile Subprocedure derives from that behind the frmADO_Jet and frmADO_MSDE forms. The primary difference in the code of Listing 30.5 is that the Recordset.Open method specifies the temporary Batch.rst file as its data source.

Listing 30.5 Opening a Saved `Recordset` Object and Assigning It to the `Recordset` Property of the Subform

 Private Sub OpenRstFromFile()   If rstBatch.State = adStateOpen Then    rstBatch.Close   End If   rstBatch.Open CurrentProject.Path & "\Batch.rst", , adOpenStatic, _    adLockBatchOptimistic, adCmdFile   With sbfBatch    'Assign rstBatch as the Recordset for the subform    Set .Recordset = rstBatch    .UniqueTable = "Customers"    .txtCustomerID.ControlSource = "CustomerID"    .txtCompanyName.ControlSource = "CompanyName"    .txtAddress.ControlSource = "Address"    .txtCity.ControlSource = "City"    .txtRegion.ControlSource = "Region"    .txtPostalCode.ControlSource = "PostalCode"    .txtCountry.ControlSource = "Country"   End With End Sub

The `cmdBulkUpdate` Event Handler

Clicking the Update Disconnected Recordset/Restore Disconnected Recordset button executes the cmdBulkUpdate event-handler (see Listing 30.6). The Set sbfBatch.Recordset = Nothing statement prevents flashing of the subform during edits performed in the Do While Not .EOF...Loop process. This loop traverses the Recordset and changes the values of unused Region cells from NULL to 123 or vice-versa. After the loop completes, the form hooks back up to the edited Recordset. The call to the Form_Load subprocedure displays the updated Customers table fields in the subform.

Note

Real-world applications use an unbound form and unbound text boxes to edit the Recordset. The form requires command buttons to navigate the Recordset by invoking Move... methods. The event handler for an Update Record button makes the changes to the field values of the local Recordset.

Listing 30.6 The `cmdBulkUpdate` Event Handler Uses a Loop to Emulate Multiple Recordset Editing Operations

 Private Sub cmdBulkUpdate_Click()   Dim blnUpdate As Boolean   Dim strCapSuffix As String   'Housekeeping for form and button captions   strCapSuffix = " While Disconnected (Updates Are Pending)"   If Me.cmdBulkUpdate.Caption = "Update Disconnected Recordset" Then       Me.Caption = "Changing Empty Region Values to 123" & strCapSuffix    blnUpdate = True    Me.cmdBulkUpdate.Caption = "Restore Disconnected Recordset" Else    Me.Caption = "Returning Region Values from 123 to Null" & strCapSuffix    blnUpdate = False    Me.cmdBulkUpdate.Caption = "Update Disconnected Recordset"   End If   'If you don't execute the following instruction, the subform   'datasheet can cause flutter vertigo during updates   Set sbfBatch.Recordset = Nothing   'Set the Field variable (improves performance)   Set fldRegion = rstBatch.Fields("Region")   'Now update or restore Region values   With rstBatch    .MoveFirst    Do While Not .EOF      If blnUpdate Then       If IsNull(fldRegion.Value) Then         fldRegion.Value = "123"      End If    Else      'Restore the original Null value      If fldRegion.Value = "123" Then       fldRegion.Value = Null      End If    End If    .MoveNext   Loop   On Error Resume Next   'For safety   Kill CurrentProject.Path & "\Batch.rst"   On Error GoTo 0   .Save CurrentProject.Path & "\Batch.rst", adPersistADTG End With 'Now restore the subform's Recordset property Set sbfBatch.Recordset = rstBatch Me.cmdUpdate.Enabled = True End Sub

Tip

Create a Field variable (fldRegion), instead of using a Recordset.Fields(strFieldName).Value = varValue instruction. Specifying a Field variable improves performance, especially if the Recordset has many fields.

The `cmdUpdate` Event Handler

Clicking the Send Updates to Server button executes the cmdUpdate event handler and the UpdateBatch method to update the server tables (see Listing 30.7). Before executing the update, Debug.Print statements record the OriginalValue and Value property values for the first row in the Immediate window.

Listing 30.7 Updating the Server Tables Reconnects the `Recordset` to the Data Source, Executes the `UpdateBatch` method, and Closes the `Connection`

 Private Sub cmdUpdate_Click()   'Recreate the connection   Call OpenConnection   'Reopen the Recordset from the file   With rstBatch     If .State = adStateOpen Then          .Close     End If     Set rstBatch.ActiveConnection = cnnBatch     .Open CurrentProject.Path & "\Batch.rst", , adOpenStatic, _     adLockBatchOptimistic, adCmdFile    'To demonstrate these two properties    Debug.Print "Original Value: " & .Fields("Region").OriginalValue    Debug.Print "Updated Value: " & .Fields("Region").Value    'Send the updates to the server    .UpdateBatch    .Close   End With   'Clean up   Set rstBatch = Nothing   cnnBatch.Close   Set cnnBatch = Nothing   Kill CurrentProject.Path & "\Batch.rst"   'Load the subform datasheet from the server   Call Form_Load  Me.Caption = "Updated Values Retrieved from Server" End Sub

The `cmdOpenXML` Event Handler

The cmdOpenXML event handler for the Open Batch.rst in IE 5+ button demonstrates use of the VBA Shell function to launch another application (see Listing 30.8). The argument of the Shell function is identical to the instruction you type in the Run dialog's Open text box to launch an application manually. If successful, the Shell function returns the task ID value of the running application; if not, the function returns an empty Variant value.

Listing 30.8 Opening a Persistent `Recordset` Object Saved as an XML File in IE 5+

 Private Sub cmdOpenXML_Click()   'Launch IE 5+ with Batch.xml as the source URL   Dim strURL As String   Dim strShell As String   Dim varShell As Variant   strURL = "file://" & CurrentProject.Path & "\Batch.xml"   strShell = "\Program Files\Internet Explorer\Iexplore.exe " & strURL   varShell = Shell(strShell, vbNormalFocus)   If IsEmpty(varShell) Then    MsgBox "Can't open Internet Explorer", vbOKOnly + vbExclamation, _      "Unable to Display Batch.xml"   End If End Sub

The `Form_Unload` Event Handler

Variables in form Class Modules disappear (go out of scope) when the form closes. However, it's a good programming practice to "clean up" all object variables before closing a form. In addition to cleanup operations, this event handler (see Listing 30.9) detects the presence of unsent updates in Batch.rst. Setting the intCancel argument to True cancels the unload operation.

Listing 30.9 The `Form_Unload` Event Handler Checks for Unsent Updates and, if the User Clicks Yes in the Message Box, Closes Open Objects and Sets Them to `Nothing`

 Private Sub Form_Unload(intCancel As Integer)   'Check for pending updates before unloading   If Dir(CurrentProject.Path & "\Batch.rst") <> "" Then    If MsgBox("Are you sure you want to quit now?", vbQuestion + vbYesNo, _       "Updates Are Pending for the Server") = vbNo Then      intCancel = True      Exit Sub    End If   End If    'Clean up objects   If rstBatch.State = adStateOpen Then    rstBatch.Close   End If   Set rstBatch = Nothing   If cnnBatch.State = adStateOpen Then     cnnBatch.Close   End If   Set cnnBatch = Nothing   'If you don't execute the following instruction,   'you receive an error when opening the form   Set sbfBatch.Recordset = Nothing End Sub

Tip

Unlike Visual Basic forms, values you assign with VBA code to Access Form, Report, and Control objects persist after closing the object and exiting the Access application. In some cases, reopening the object results in an error message. Executing the Set sbfBatch.Recordset = Nothing instruction before closing the form and its subform prevents the possibility of an error on reopening the form, because the source value of the Recordset property isn't present before the Form_Load event handler executes.