Special Edition Using Microsoft Office Access 2003
Taking Advantage of Disconnected Recordsets
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
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
To give frmBatchUpdate a trial run, do this:
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 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.
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.
|