17.3. Binding Recordsets to Objects

Sometimes built-in links do not provide the desired flexibility for controlling the recordset and it can be very useful to build the recordset in code and bind it to an object. Recordsets can be bound to combo boxes, list boxes, forms, and reports (ADP files only).

17.3.1. Binding to a Form, Combo Box, or List Box

The code and methods used for binding forms, combo boxes, and list boxes are basically the same. They all have a Recordset property that can be assigned an active ADO recordset object.

Private Sub Form_Open(Cancel As Integer)
Dim RS As ADODB.Recordset
Dim CN As ADODB.Connection
Dim STRConnect As String
Set RS = New ADODB.Recordset
Set CN = New ADODB.Connection
STRConnect = "Provider=SQLOLEDB.1" & _
    ";Data Source=(local)" & _
    ";Initial Catalog=NorthwindCS" & _
    ";user id=sa" & _
    ";password=password"

CN.Open STRConnect
RS.Open "Products", CN, adOpenKeyset, adLockOptimistic
Set Me.Recordset = RS
RS.Close
CN.Close
Set RS = Nothing
Set CN = Nothing
End Sub

17.3.2. Binding to a Report

Unlike with forms, list boxes, and combo boxes, reports are not nearly as easy to dynamically bind to an active recordset. In addition, it's not possible at all with MDB files. The key difference is that the recordset has to be a shaped recordset, using the Microsoft Data Shaping services for OLEDB (MSDataShape) provider or the Microsoft Client Data Manager (Microsoft.Access.OLEDB.10.0) provider.

For example, the Invoice report in the NorthwindCS.adp sample file is based on the invoices view stored on SQL Server. However, if you try to bind a simple ADO recordset based on the invoices view to the Invoice report like you might do for a form, you would get an error or see unpredictable behavior.

Private Sub Report_Open(Cancel As Integer)
'Dim RS As ADODB.Recordset
'Dim CN As ADODB.Connection
'Dim STRConnect As String
'
'Set RS = New ADODB.Recordset
'Set CN = New ADODB.Connection
'STRConnect = "Provider=SQLOLEDB.1" & _
'      ";Data Source=(local)" & _
'      ";Initial Catalog=NorthwindCS" & _
'      ";user id=sa" & _
'      ";password=password"

'
'CN.Open STRConnect
'RS.Open "customers", CN, adOpenKeyset ', adLockReadOnly
'Set Me.Recordset = RS
'Set RS = Nothing
'Set CN = Nothing
End Sub

To get a starter shape:

?reports![products by category].shape
result:
SHAPE (SHAPE {SELECT CategoryName, ProductName, UnitsInStock
FROM dbo.[Products by Category]} AS rsLevel0
COMPUTE rsLevel0, Count(rsLevel0.[ProductName])
AS __Agg0 BY CategoryName AS __COLRef0) AS RS_148

You can then use the above SQL to bind the shaped recordset to the Products by Category report by clearing the report's Recordsource property and adding the following code to the report's Open event procedure:

Private Sub Report_Open(Cancel As Integer)
  Dim RS As ADODB.Recordset
  Dim CN As ADODB.Connection
  Dim strConnect As String
  Dim strSQL As String
  Set RS = New ADODB.Recordset
  Set CN = New ADODB.Connection
  strConnect = "Provider=Microsoft.Access.OLEDB.10.0" & _
    ";Data Provider=SQLOLEDB.1" & _
    ";Data Source=(local)" & _
    ";Initial Catalog=NorthwindCS" & _
    ";user id=sa" & _
    ";password=password"
CN.Open strConnect
strSQL = "SHAPE (SHAPE {SELECT CategoryName, " & _
  "ProductName, UnitsInStock FROM [Products by Category]} " & _
  "AS rsLevel0 COMPUTE rsLevel0, Count(rsLevel0.[ProductName]) " & _
  "AS __Agg0 BY CategoryName AS __COLRef0) AS RS_230"

RS.Open strSQL, CN, adOpenKeyset ', adLockReadOnly
    Set Me.Recordset = RS
    Set RS = Nothing
    Set CN = Nothing
End Sub

However, there is still one more step that must be done before the report will render correctly. Although the above SQL is valid, if you use the above code to bind a shaped recordset to the Products by Category report, the report will generate the error shown in Figure 17-22 and the Categories will be displayed as "#Name?"

Figure 17.22. Figure 17-22

After clicking OK, the report would then look similar to the report shown in Figure 17-23

The reason for the error is that the CategoryName field is aliased as __COLRef0 in the SQL statement but the text box bound to CategoryName is still expecting CategoryName. Since CategoryName is not defined in the SQL, an error is generated. Why this doesn't work since we are using the same SQL given to us by Access for the same report is a good question. Under the hood, Access must be accounting for this discrepancy in some way and coordinating the two values dynamically. However, there are two ways to fix the problem:

Figure 17.23. Figure 17-23

• Change the Control Source property of the CategoryName text box to __COLRef0. This fixes the problem by using the alias defined in the SQL statement

• Change the __COLRef0 alias in the SQL to CategoryName, as follows:

  SHAPE (SHAPE {SELECT CategoryName, ProductName, UnitsInStock
  FROM dbo.[Products by Category]} AS rsLevel0
  COMPUTE rsLevel0, Count(rsLevel0.[ProductName])
  AS __Agg0 BY CategoryName AS CategoryName) AS RS_148

  
  

Now if you run code to bind the SQL statement, the names will match and the report will display as expected.

As you have probably figured out by now, binding recordsets to reports is usually more trouble than it is worth. If you have a lot of reports then we normally suggest using the Recordsource property and letting Access do the shaping for you. For forms and combo or list boxes, however, binding recordsets can be a very effective means of quickly connecting to remote data sources on the fly without relying on linked tables or queries.

17.3.3. Using Persisted Recordsets

While regular bound recordsets can be very useful, sometimes an application will need the same recordset in multiple forms or controls and the data gets retrieved multiple times. In cases where the data usage is read-only and seldom changes, a quick and easy method for caching the data locally can be very useful. With an MDB file, you have the option of storing data locally in tables. However, although local tables are easy to populate by appending data from a linked ODBC table, it is not as convenient when the data has been retrieved via an ADO recordset.

Fortunately, the ADO object model allows for a very simple method of saving data to a local XML file and quickly recreating it as an ADO recordset when needed. This often overlooked and underutilized feature of ADO can dramatically reduce network traffic and increase application performance when used correctly. The best scenarios for using local persisted recordsets are when data is read-only, seldom changes, and is used in multiple locations throughout an application.

For example, you may have a "states" table for an order entry application. This is a good candidate because states rarely change, there is no need to modify a state's name, and the data is likely used in several places: the Customers form when adding the customer, the Vendors form when adding vendors, and the Orders form when entering shipping addresses. With persisted recordsets, once the data is brought down locally and cached, you can then use it in all your forms without retrieving the data again from the server.

Creating a persisted recordset is fairly straightforward: just create an ADO recordset (using ADO 2.6 or later) and use the Save method to persist the data to an XML file. The following steps use the Products form from the NorthwindCS.adp sample file as an example.

17.3.3.1. Persisting the Data to XML

Add a command button named cmdSaveXML to the header section of the Products form in NorthwindCS.adp. Add the following code to the Click event of the newly added command button:

Private Sub cmdSaveXML_Click()
  Dim adoRS As ADODB.Recordset

  ' Create the recordset. You can create a separate ADO connection
object
  ' but the following code uses the current ADP connection for
simplicity
  ' MAke sure to use a keyset cursor and adLockBatchOptimistic locking
  Set adoRS = New ADODB.Recordset
  adoRS.Open "products", CurrentProject.Connection, adOpenKeyset,
adLockBatchOptimistic

  ' The following single line of code saves the data to an XML file
  adoRS.Save "D:	estproducts.xml", adPersistXML
  adoRS.Close
  Set adoRS = Nothing
End Sub

Once saved, the XML file can be opened in Notepad or a Web browser like Microsoft Internet Explorer, and should look similar to Figure 17-24

Figure 17.24. Figure 17-24

17.3.3.2. Loading the XML Data

Clear the Recordsource property of the form (but not the controls) and add the following code to the Open event of the form:

Private Sub Form_Open(Cancel As Integer)
  Dim adoRS As ADODB.Recordset

  ' Create the recordset against the existing XML file
  Set adoRS = New ADODB.Recordset
  adoRS.Open "D:	estproducts.xml"

  ' Bind the new recordset to the form
  Set Me.Recordset = adoRS
  adoRS.Close
  Set adoRS = Nothing

End Sub

At this point, the form should be functional and a user should be able to scroll through records in the XML file.