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