ACC: How to Use dBASE SET DELETED in MS Access (1.x/2.0)

• Microsoft Access versions 1.0, 1.1, 2.0

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

When you delete a record in a dBASE or a Microsoft FoxPro database, the record is actually only marked for deletion. It is not permanently removed until the database is packed in dBASE or FoxPro. This characteristic can produce confusing effects when you open an attached table in Datasheet view.

This article demonstrates how to use functionality similar to the dBASE DELETED commands in Microsoft Access so that records marked for deletion do not appear in any output form, such as a table in Datasheet view or a report in Print Preview.

MORE INFORMATION

In dBASE or FoxPro, records that are marked for deletion still appear in any output form unless you SET DELETED to ON. Because of this, Microsoft Access displays such records in an attached dBASE or FoxPro table, even if the records are marked for deletion.

To see this characteristic in Microsoft Access, follow these steps:

1. Attach the dBASE table called NEWCUST.DBF. This table should be present in your Microsoft Access program directory if you installed the sample databases with Microsoft Access.

2. Open the NEWCUST table in Datasheet view. Note that there are 13 records in this table.

3. Select the last record in the table by clicking the record selector on the leftmost side of the datasheet.

4. Press the DELETE key. Note that the record disappears from the datasheet, leaving 12 records in this table.

5. Close the Datasheet window and reopen it. Note that all 13 records reappear.

To prevent this behavior from occurring, Microsoft Access provides functionality identical to the dBASE and FoxPro SET DELETED command. To take advantage of this functionality, be sure that you set Deleted to On if you do not want records that have been marked for deletion to show. Use the following procedure to do this:

1. Open the MSACCESS.INI file in any text editor, such as Notepad.

2. Find the [dBASE ISAM] section.

3. Change the [dBASE ISAM] setting

         Deleted=Off
   

         -to-
   

         Deleted=On
   

4. Save the MSACCESS.INI file and restart Microsoft Access.

   When you view the dBASE data, records marked for deletion will no longer appear. To permanently remove records marked for deletion, you must open the dBASE table in dBASE format and run the PACK command.