Access™ 2007 VBA Programmer's Reference

19.4. Binding ADODB Recordsets

Built-in table and view links do not provide enough flexibility for controlling the recordset for a form. Often it is extremely useful to build a recordset in code and then bind it to the desired object. Recordsets can be bound to combo boxes, list boxes, forms, and reports in ADP files. This section explores how to create and bind Recordsets in ADO.

19.4.1. Binding to a Form, ComboBox, or ListBox

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. Typically, the recordset is bound to the form during the Form_Open event, but can be set at any time while the form is open. The following is an example of binding a form to a Recordset object:

Sub BindRecordset()

  ' Define Variables
  Dim rsRecordSet As New ADODB.Recordset
  Dim cnConnection As New ADODB.Connection
  Dim frmForm As New Form
  Dim strConnection As String

  ' Create the Connection string
  strConnection = _
    "Provider=SQLOLEDB.1;Data Source=<machine name>\sqlexpress" & _
    ";Initial Catalog=NorthwindCS;user id=sa;password=password"

  ' Open the connection
  cnConnection.Open strConnection

  ' Open the Recordset
  rsRecordSet.Open "Products", cnConnection, adOpenKeyset, adLockOptimistic

  ' Bind the Recordset to the form
  Set frmForm.Recordset = rsRecordSet

  ' Clean up
  rsRecordSet.Close
  Set rsRecordSet = Nothing
  cnConnection.Close
  Set cnConnection = Nothing

End Sub

The code for binding ...