Visual Basic 6 Black Book
(Publisher: The Coriolis Group)
Author(s): Steven Holzner
ISBN: 1576102831
Publication Date: 08/01/98

ADO: Binding Controls To Record Sets

To bind a control to an ADO Recordset object, you just set that control’s DataSource property to that object, and then set whatever other data properties that control needs to have set (see, for example, Table 24.1 in the previous chapter, which lists the data properties of various controls).

Let’s see an example. In our ADO code example, the adocode example (see “A Full-Scale ADO Example” in this chapter), we create a record set, adoRecordset, and open the db.mdb database we created in the last chapter in it. We can bind the fields in that database to the text boxes Text1 and Text2 this way when the adocode main form loads:

Private Sub Form_Load()
    Dim db As Connection
    Set db = New Connection

    db.Open "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data _
        Source=C:\vbbb\adocode\db.mdb;"

    Set adoRecordset = New Recordset
    adoRecordset.Open "select Grade, Name from students", _
        db, adOpenStatic, adLockOptimistic

    Set Text1.DataSource = adoRecordset
    Text1.DataField = "Name"
    Set Text2.DataSource = adoRecordset
    Text2.DataField = "Grade"

End Sub

That’s all it takes—now we’ve bound two text boxes to an ADO record set.

ADO: Adding A Record To A Record Set

To add a new record to an ADO record set, you use the AddNew method. After you’ve updated the fields of the current record, you save that record to the database with the Update method. Here’s how you use AddNew:

recordset.AddNew [ Fields [, Values]]

Here are the arguments for this method:

•  Fields—A single name or an array of names or ordinal positions of the fields in the new record.

•  Values—A single value or an array of values for the fields in the new record. If Fields is an array, Values must also be an array with the same number of members. The order of field names must match the order of field values in each array.

Let’s see an example. Here, we’ll add a new record to the record set adoRecordset in our ADO code example, the adocode example (see “A Full-Scale ADO Example” in this chapter), when the user clicks the appropriate button:

Private Sub cmdAdd_Click()
    On Error GoTo ErrLabel
    adoRecordset.AddNew

    Text1.Text = ""
    Text2.Text = ""

    Exit Sub

ErrLabel:
    MsgBox Err.Description
End Sub

Note that we also clear the two text boxes that display the field data, Text1 and Text2, so users can enter the data they want in the new record. When done, they press the Update button to update the data source.

ADO: Refreshing The Record Set

Sometimes you want to refresh the data in a record set—you might be dealing with multiply-connected databases, for instance, where other users are making changes as well—and you can use the ADO Refresh method for that. Let’s see an example. Here, we’ll refresh the record set adoRecordset in our ADO code example, the adocode example (see “A Full-Scale ADO Example” in this chapter), when the user clicks the appropriate button:

Private Sub cmdRefresh_Click()
    On Error GoTo ErrLabel

    adoRecordset.Requery

    Exit Sub

ErrLabel:
    MsgBox Err.Description
End Sub

And that’s all it takes to refresh the record set.

ADO: Updating A Record In A Record Set

After changing the data in a record’s fields or adding a new record, you update the data source to record the change, using the Update method:

recordset.Update Fields, Values

Here are the arguments for this method:

•  Fields—A single name or an array of names or ordinal positions of the fields in the new record.

•  Values—A single value or an array of values for the fields in the new record. If Fields is an array, Values must also be an array with the same number of members. The order of field names must match the order of field values in each array.

Let’s see an example. When users want to update records in our ADO code example, the adocode example (see “A Full-Scale ADO Example” in this chapter), they click the appropriate button, and we’ll update the data source this way:

Private Sub cmdUpdate_Click()
    On Error GoTo ErrLabel

    adoRecordset.Update

    Exit Sub

ErrLabel:
    MsgBox Err.Description
End Sub

That’s all we need—now we’re ready to update records in an ADO record set.

ADO: Moving To The First Record In A Record Set

To move to the first record in an ADO record set, you use the Recordset object’s MoveFirst method (this method takes no parameters). Let’s see an example. When the user clicks the appropriate button in our ADO code example, the adocode example (see “A Full-Scale ADO Example” in this chapter), we’ll move to the first record in our record set, adoRecordset:

Private Sub cmdFirst_Click()
    On Error GoTo ErrLabel

    adoRecordset.MoveFirst

    Exit Sub

ErrLabel:
    MsgBox Err.Description
End Sub

And that’s all the code we need to move to the first record.

ADO: Moving To The Last Record In A Record Set

To move to the last record in an ADO record set, you use the Recordset object’s MoveLast method (this method takes no parameters). Let’s see an example. When the user clicks the appropriate button in our ADO code example, the adocode example (see “A Full-Scale ADO Example” in this chapter), we’ll move to the last record in our record set, adoRecordset:

Private Sub cmdLast_Click()
    On Error GoTo ErrLabel

    adoRecordset.MoveLast

    Exit Sub

ErrLabel:
    MsgBox Err.Description
End Sub

And that’s all the code we need to move to the last record.