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
Simple 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:
- You can control and reinstate a record should you need to.
- Your queries (and reports) can utilise and filter records past and present.
- You don’t have to worry about any relational tables that interfere with this action.
- 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:
- An action query (DELETE).
- A macro to handle related tables and order of deletion.
- 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.