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