DAO Recordset: Recordset Operations

|  Overview |  How Do I |  FAQ |  Sample |  |  ODBC Driver List

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

• Adding new records

• Editing existing records

• Deleting records

• Requerying recordsets

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

• DAO Queries

• DAO: Creating, Opening, and Closing DAO Objects

• DAO Recordset: Creating Recordsets

• DAO Recordset: Recordset Navigation

• DAO Recordset: Bookmarks and Record Positions

• DAO Recordset: Seeking and Finding

• DAO Recordset: Binding Records Dynamically

• DAO Recordset: Caching Multiple Records for Performance

• DAO Record Field Exchange (DFX)

• DAO Recordset: Using Aggregate SQL Functions with MFC DAO Classes

****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:

• Populating Recordsets and Counting Records with DAO

• AddNew Method

• Update Method

For information about where the new record appears and other considerations, see . 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 member function.

2. Call the recordset's 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 -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:

   • with the parameter FALSE (meaning “not Null”).

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

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

Editing Existing Records in DAO

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

• Edit Method

• Update Method

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 and (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 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 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 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 -derived class with DFX, assign values to the fields. To give a field the value Null, call (passing TRUE). If you have double buffering turned off, call and (passing FALSE) for each field you assign a value to.

5. Complete the process by calling the recordset's 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 .

If you move to another record without calling Update, the change is lost. For more information, see . 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 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 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 . You can tell whether you’re on a deleted record by calling .

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:

• Call to close the recordset, reset any parameters or other properties, and call again.

• Reset any parameters or other properties, then call .

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 is exactly as in DAO. But MFC adds one feature: your ability to change the recordset’s and/or ****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