DAO Recordset: Recordset Operations

HomeOverviewHow Do IFAQSampleTutorialODBC Driver List

This article discusses several key recordset operations, particularly those involving updating records. Topics covered include:

For related information about using recordsets, see the following articles:

Important   Your ability to update records requires that you have update permission for your database and that you have an updatable table-type or dynaset-type recordset. You can't update snapshot-type recordsets with the MFC DAO classes.

Adding New Records in DAO

For general information about adding records in DAO, see the following topics in DAO Help:

For information about where the new record appears and other considerations, see CDaoRecordset::AddNew. For information about the role of double buffering, see the article DAO Record Field Exchange: How DFX Works.

To add a new record to a recordset

  1. Determine whether your recordset is updatable.

    Call the recordset's CanUpdate member function.

  2. Call the recordset's AddNew member function.

    The fields of the new recordset, represented by the recordset's edit buffer (the recordset’s data members), are initially all Null. AddNew prepares the MFC recordset's edit buffer, which DAO calls the copy buffer. For more information about the edit buffer with AddNew, see the article DAO Record Field Exchange: How DFX Works.

  3. Assign values to the new record's fields.

    If you're using a CDaoRecordset-derived class with DFX, assign values to the fields. If you have double buffering turned off, make the following two calls for each field you assign a value to:

    Or, if you explicitly want the field to be Null, call only SetFieldNull, with the parameter TRUE.

  4. Complete the process by calling the recordset's Update member function.

    Update adds the record. If no transaction is in effect, the change takes place immediately. Otherwise, the change takes place when you call CDaoWorkspace::CommitTrans.

If you move to another record without calling Update, the change is lost. For more information, see AddNew.

Editing Existing Records in DAO

For general information about editing records in DAO, see the following topics in DAO Help:

If you have double buffering turned on, MFC maintains a copy of the edit buffer so it can detect changes to the recordset fields for you. You don't have to call SetFieldDirty and SetFieldNull (passing FALSE) for each change. For more information, see the article DAO Record Field Exchange: Double Buffering Records.

For details about what Edit does and about the role of double buffering, see CDaoRecordset::Edit and the article DAO Record Field Exchange: How DFX Works.

To edit an existing record in a recordset

  1. Determine whether your recordset is updatable.

    Call the recordset's CanUpdate member function.

  2. Move to the record you want to edit.

    Use any of the recordset's navigation mechanisms that take you to a specific record.

  3. Call the recordset's Edit member function.

    Edit prepares the recordset's edit buffer, which DAO calls the copy buffer. For more information about the edit buffer with Edit, see the article DAO Record Field Exchange: How DFX Works.

  4. Assign values to the fields you want to change.

    If you're using a CDaoRecordset-derived class with DFX, assign values to the fields. To give a field the value Null, call SetFieldNull (passing TRUE). If you have double buffering turned off, call SetFieldDirty and SetFieldNull (passing FALSE) for each field you assign a value to.

  5. Complete the process by calling the recordset's Update member function.

    Update changes the record in the database. If no transaction is in effect, the change takes place immediately. Otherwise, the change takes place when you call CDaoWorkspace::CommitTrans.

If you move to another record without calling Update, the change is lost. For more information, see Edit. For information about double buffering, see the article DAO Record Field Exchange: Double Buffering Records.

Deleting Records in DAO

For general information about deleting records in DAO, see the topic "Delete Method" in DAO Help.

To delete a record from a recordset

  1. Determine whether your recordset is updatable.

    Call the recordset's CanUpdate member function.

  2. Move to the record you want to edit.

    Use any of the recordset's navigation mechanisms that take you to a specific record.

  3. Call the recordset's Delete member function.

    You don't call Update for a deletion.

  4. Move to another record before you attempt any other recordset operations.

In table-type and dynaset-type recordset objects, Delete removes the current record and makes it inaccessible. Although you can't edit or use the deleted record, it remains current. Once you move to another record, however, you can’t make the deleted record current again. Subsequent references to a deleted record in a recordset are invalid and cause an exception to be thrown. For more information, see Delete. You can tell whether you’re on a deleted record by calling IsDeleted.

Requerying in DAO

If you need to re-run a recordset query, perhaps with new parameter values each time, you can do either of the following:

The purpose of either approach to requerying the recordset is to refresh the recordset by running its query again, perhaps with changed parameters or properties. You can also requery to refresh results in a multi-user environment (multiple users or recordsets modifying the same data). In general, Requery is somewhat more efficient than Close and Open, but see the Important note below.

This description of the use and behavior of Requery is exactly as in DAO. But MFC adds one feature: your ability to change the recordset’s m_strFilter and/or m_strSort data members before you either call Requery or call Open again. If you do change either data member, MFC closes, then reopens the recordset.

Important   If you change m_strFilter or m_strSort, you lose the performance benefit of Requery. In this case, calling Requery performs no better than calling Close and Open.

For more information, see Requery in the Class Library Reference, and see the topic "Requery Method" in DAO Help.

See Also   DAO: Where Is..., DAO Recordset