Microsoft Access Tips for xBase developers

Provided by Allen Browne, allen@allenbrowne.com


Undelete Options

Unlike xBase, where records marked for deletion are not removed from the database until the PACK command is issued, deletions in Access are immediate and permanent. In converting an xBase program, you have several options.

The first is to bite the bullet and consider whether undeleting is really desirable. In many situations, you will find no genuine need to do things the way you have always done them.

But if you must, create a Yes/No field named (say) Deleted. This is effectively what xBase does, assigning an extra byte for a deletion marker. To trap a deletion and replace it with a deletion marker requires just two lines of lines in the form's Delete event:

    Cancel = True          ' Cancel the deletion.
    Me![Deleted] = True    ' Set the Deleted field instead.

Selection of the non-deleted records in queries is then a trivial task. Alternatively, filter out "deleted" records by setting the Filter in its Open event.

From code that deletes records, transactions to buffer a batch of deletions may be all you need. A BeginTrans followed either by a CommitTrans or RollBack will give you a safety net for the current session. See Archive: Move records to another table for an example of using transactions.

Another possibility is the creation of an audit trail in the Delete event. Access does not support this innately, and it is not a simple process. See Audit Trail for an example.

A final possibility in Access 2000 or 2002 is to use an Access project instead of an Access database. The project stores its data in SQL Server instead of JET. The result is that triggers and transaction logs are exposed.

Finally, although the deletions occur without a PACK command, Access does not release the space used by the old records until the database is compacted.


Home Index of tips Top