Looking to take your VBA skills further?...

Discover twinBASIC — a powerful new development platform that expands on VBA and VB6 with advanced features, modern tools, and enhanced compatibility. Perfect for those ready to elevate their projects or transition from VBA, twinBASIC lets you build on what you already know and take your applications to the next level!

Try out twinBASIC Community Edition - it's free!

Undelete Options (records)

        0 votes: *****     2,403 views      No comments
by Allen Browne, 20 April 2005    (for ALL VERSIONS of Access)

Microsoft Access Tips for xBase developers

Provided by Allen Browne, 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

Rate this article:  Your rating: PoorYour rating: Not so goodYour rating: AverageYour rating: GoodYour rating: Excellent


This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Undelete Options (records)'?

No comments yet.

Why not be the first to comment on this article?!

Have your say...

Name
E-mail (e-mail address will be kept private)
Comments


Comments require approval before being displayed on this page (allow 24 hours).