Access 2003 VBA Programmer's Reference

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 ...