Passing Disconnected Recordsets | Ado Examples and Best Practices

Team-Fly

ADO makes it easy to pass data between layers using the Recordset object. Unlike RDO and DAO, ADO can construct a totally stand-alone Recordset object. After the Recordset is passed to another layer (also running ADO), reconstituting the Recordset is done automatically.

To create a stand-alone (disjoint) Recordset in code, you need to do the following:

Create the Recordset object in the usual way, using the Client/Batch cursor library—set the CursorLocation property of the Connection or Recordset object to adUseClient before they are open.
Set the LockType property to batch-optimistic locking (adLockBatchOptimistic).
Run your query, and when the Recordset is open, set its ActiveConnection property to Nothing. This forces ADO to complete populating the Recordset and to release the Recordset from the connection. Any further action against the Recordset will not require access to the database. And no, you don't have to close the connection after having passed the Recordset object.

At this point you can pass the Recordset object from layer to layer, as long as the target layer has a compatible version of ADO (e.g., 2.1 SP2 and 2.5) installed. Because ADO is installed with Microsoft Office, Internet Explorer, Visual Studio, and a plethora of other applications, the chances of it being installed are pretty good. The target code can make changes to the Recordset in the usual manner (assuming the Recordset was updatable in the first place).

Tip

If you aren't sure if the Recordset is updatable, the easiest way to tell (in code) is to check using the Supports method.

 If rs.Supports (adAddNew) Then ...

Note

If Supports returns True, then you have an updatable Recordset. I kept getting a read-only Recordset despite setting all of the properties correctly, or so I thought. It turns out that I had omitted a comma in the Recordset.Open method.When I switched to named arguments, the correct argument was passed to the Open method and the Recordset was updatable.

After you pass the Recordset back to the calling tier, the code there can work with the Recordset as if it were created there—up to a point. All of the additions, changes, and deletions are saved in the Recordset on the target. When (or if) you need to post changes to the underlying database tables, follow these steps:

Pass the altered Recordset back to a tier that has access to the database.
Reconnect the data source by resetting the ActiveConnection property to another open connection.
Use the UpdateBatch method.

The server-side and client-side code follow in the next two sections.

The Server-side Code

This code constructs a new Recordset without benefit of a Command object. It builds the query by concatenating the single input parameter that finishes the Like expression. Once constructed, the Recordset object's ActiveConnection property is "set" to Nothing to dissociate it from the Connection. No, we don't use the Set operator—it takes too long and it's not necessary. Just assign the ActiveConnection property to Nothing, as shown:

 Public Function GetRecs(Author As String) As Recordset Dim rs As Recordset OpenConnection Set rs = New Recordset With rs     .CursorLocation = adUseClient     .CursorType = adOpenStatic     .LockType = adLockBatchOptimistic     .Open "select Au_id, Author, Year_Born " _ & " from authors where author like '" & Author & "' ", _             cn, Options:=adCmdText     .ActiveConnection = Nothing End With cn.Close Set GetRecs = rs End Function Private Function OpenConnection() Set cn = New Connection cn.Open "Provider=SQLOLEDB.1;Integrated Security=SSPI;" _ & "Initial Catalog=biblio;Data Source=betav2" End Function

The following simple code posts the changes to the Recordset. The changes made by the client are embedded in the Recordset itself. Notably missing from this code is the error handler to deal with collisions. That was discussed in Chapter 7.

 Private cn As Connection Public Function PostChanges(rs As Recordset) As Integer OpenConnection Set rs.ActiveConnection = cn rs.UpdateBatch cn.Close End Function

The Client-side Code

The client-side code looks like the code below. This entire application, with both client-side and server-side code, is provided on the CD in the Passing Recordsets directory.

I use several enumerations to map the individual Recordset Field objects as well as the TextBox control arrays. These not only help readability for humans, but they also help performance as discussed earlier.

 Dim rsLocal As Recordset Enum fldRs     AuID     Author     Year_Born End Enum Enum idxText     Add     Update     Delete End Enum Enum idxTab     Query     Add     Update     Delete End Enum

The following routine is used to fetch the Recordset from the server-side or simply another component. Because we need to test to find out if there are pending changes before running the query (which would discard these changes), we have to go through a number of steps to see if the Recordset exists, if it is open, and if its edit mode is still unchanged.

 Private Sub cmdQuery_Click() If rsLocal Is Nothing Then     ElseIf rsLocal.State = adStateOpen Then         If rsLocal.EditMode = adEditNone Then         rc = MsgBox("If you requery now, changes will be lost. " _ & "Press OK to continue or Cancel to keep current records.", _ vbOKCancel + vbCritical, "Request to requery")         If rc = vbOK Then         Else             Exit Sub         End If     End If End If

If we know there are no pending changes (or we don't care to keep them), we run the query, passing the filter string that will be inserted in the query at the server, as follows:

 Set rsLocal = GetRecs(txtFilter)

After the Recordset is returned, we can then pass it to the MSHFlexGrid, which knows how to display it. We do have to tune the columns a little, as the grid does not set the widths correctly, but that's easy using the TextWidth function.

The following code does this:

 If rsLocal.EOF Then          MsgBox "No rows returned"     Set MSHFlexGrid1.Recordset = Nothing Else     Set MSHFlexGrid1.Recordset = rsLocal     MSHFlexGrid1.Col = fldRs.AuID     MSHFlexGrid1.ColWidth(fldRs.Author) = _     TextWidth(String(Len(rsLocal(fldRs.Author)), "M")) End If

If the Recordset is updatable, we enable the portions of the tab control containing the buttons and dialogs that control changes to the Recordset, as follows:

 If rsLocal.Supports(adAddNew) Then fraAdd.Enabled = True …

The change routines ensure that the row selected from the grid is deleted or updated, or that a new row is added. They also change the color in the grid to reflect the changes as they are made. This reminds the user that these changes are temporary—not saved to the database.

The following delete routine confirms the operation and simply uses the Delete method against the Recordset. Deleted grid rows are marked in red to show that they are no longer available.

 Private Sub cmdDelete_Click() rc = MsgBox("Press OK to confirm deletion of this record or Cancel to abort.", _ vbOKCancel + vbCritical, "Request to delete record") If rc = vbOK Then     rsLocal.Delete End If Setcolor (255) End Sub

The add routine, shown next, validates the new data and uses the AddNew method to initiate an Add operation. The new data is posted from the text boxes, and the Update method saves the new row in memory. No changes are posted to the database until the Recordset is passed back to the server, and we're not ready to do that yet.

 Private Sub cmdAdd_Click() Dim rc As Integer If ValidateAuthor(idxText.Add) Then    fraAdd.Enabled = False    rsLocal.AddNew    rsLocal(fldRs.Author) = txtAuthor(idxText.Add)    rsLocal(fldRs.Year_Born) = txtYear_Born(idxText.Add)    rsLocal.Update    txtAuthor(idxText.Add) = ""    txtYear_Born(idxText.Add) = ""    fraAdd.Enabled = True End If

In the following code, a new row is added to the grid and painted green. Because we don't know what the primary key will be, we simply show it as "??".

 With MSHFlexGrid1     .Rows = .Rows + 1     .Row = .Rows − 1     .Col = fldRs.AuID: .Text = "??"     .Col = fldRs.Author: .Text = rsLocal(fldRs.Author)     .Col = fldRs.Year_Born: .Text = rsLocal(fldRs.Year_Born)     .Setcolor (&HFFFF00) End With End Sub

Remember that this sample application is driven from a tabbed dialog control. This next set of code is embedded beneath the Update on that control. When the user selects the Update tab, the selected row is shown in a couple of text boxes. Changes made to these boxes are posted to the local Recordset when the cmdUpdate button is clicked, but only after the contents of the text boxes are validated.

 Private Sub cmdUpdate_Click() Dim rc As Integer If ValidateAuthor(idxText.Update) Then     fraUpdate.Enabled = False     rsLocal(fldRs.Author) = txtAuthor(idxText.Update)     rsLocal(fldRs.Year_Born) = txtYear_Born(idxText.Update)     rsLocal.Update     fraUpdate.Enabled = True     ' Make grid look like Recordset

After the Recordset is changed, we copy the changes back to the selected row in the grid so the grid matches the current (RAM-based) Recordset, as shown in the following code. We paint the row yellow to show that its values are transitory and not posted to the database yet.

     With MSHFlexGrid1         .Col = fldRs.Author         .Text = txtAuthor(idxText.Update)         .Col = fldRs.Year_Born         .Text = txtYear_Born(idxText.Update)     End With     Setcolor (&HFFFF&) End If End Sub

Posting the Recordset Changes to the Database

When the user decides to post the changes to the database, the following routine bundles up the Recordset and passes it to the server-side component discussed earlier. Yes, we need collision-detection code here, too. We discussed this in Chapter 7—remember?

 Private Sub cmdPostChanges_Click() rc = PostChanges(rsLocal) cmdQuery_Click      ' Refetch rows to sync grid End Sub

Team-Fly