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:
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).
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 ...
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:
The server-side and client-side code follow in the next two sections.
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 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
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