Microsoft Access Database: Delete Record or Not to Delete Record, That’s the Microsoft Access Question

Should you delete records in Microsoft Access?

When using an Access database table which is very similar to the look of an Excel spreadsheet, you can edit and carryout basic formatting in this view including the ability to delete whole records.

Working in this view is therefore potentially dangerous and is not normally the correct view to be working in the first place; there are better and safer ways to edit data in Microsoft Access.

Microsoft Access Delete Record

Microsoft Access - record deleteSimple enough to do! Locate the record and press the DELETE key! But understand that in an Access database this is automatically saved ‘off‘ the disk and therefore you do not need to save your changes. In fact, if you are really unlucky, this can not be reversed – the undo action is not available.

In some cases, you will not be able to delete a record due to that table being related to another table which needs to maintain data integrity between the two tables and in a way protects users from accidentally deleting records in the first place.

My View on Deleting Records in Microsoft Access

Simple answer: Don’t do it! Instead, consider marking records as deleted with a field set as ‘Yes/No’ data type in your table so that it provides the following benefits:

  1. You can control and reinstate a record should you need to.
  2. Your queries (and reports) can utilise and filter records past and present.
  3. You don’t have to worry about any relational tables that interfere with this action.
  4. Provides a better archive of your data.

If you really need to delete records in Microsoft Access, then use one of the following better controlled methods:

  1. An action query (DELETE).
  2. A macro to handle related tables and order of deletion.
  3. VBA code to handle more powerful ways to delete and/or archive data.

So you shouldn’t really delete records in Microsoft Access and this can be protected and locked to prevent users from carrying out this frowned upon action.

To learn more about general Access database functionality and some of the Action queries you can uitilise, take a look at my eBooks or eBook bundle.

This entry was posted in Database Theory, MS Access, Queries, Tables and tagged , , , , . Bookmark the permalink.

One Response to Microsoft Access Database: Delete Record or Not to Delete Record, That’s the Microsoft Access Question

  1. Ben Beitler says:

    Just a another note of caution when deleting records in Microsoft Access. Your application has a setting that can switch off warnings when deleting any object. Make sure you do not switch this off as once this action has been carried out, there’s no way back unless you have a back-up to restore (I assume you do back-ups?)

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Confirm you are human.