UtterAccess HomeUtterAccess Wiki

Welcome Guest ( Log In | Register )

Custom Search
Edit Discussion
> Deleting Records    
Deleting Records

Related Content:
    RunSQL vs Execute



A question that seems to be asked many times is

How do I stop the 'You are about to delete...etc'

message box that is displayed by default (depending on your settings in Tools-->Options-->Edit/Find tab-->Confirm settings) when you are running a 'delete action query. The following may offer a different approach - or a solution.

An alternative to actually deleting data

The problem with actually deleting records from your lovingly crafted database is that once you've clicked on your 'Yes, Please delete my records' button, your records are gone forever. There's no going back, no saying 'I didn't really mean it', no point in praying and you probably wont earn many brownie points. It's even worse if you have related records and Enforce Referential Integrity with Cascade Delete Related Records ticked in the Relationship window as you potentially could be deleting 1000's of records in one fell swoop!!!. Unless you have a recent backup there is no way of getting the data back. Fortunately, there is another way. It takes a little bit more work on the part of you the developer, but heh - that's what you're here for!!!

Instead of really deleting the records, just add an extra Yes/No (Boolean) field to your tables. Call it something like NotCurrent and set the Default Value to False. Now when you want to delete a record, instead of actually deleting it you merely UPDATE your new field to True which is flagging the record as having been 'deleted'[/nowiki>. It's now a simple task to filter your recordsets using queries to only include records that have your NotCurrent field equal to False - hence current records that haven't been previously <nowiki>'pseudo' deleted. You also have the added benefit of viewing - or reinstating if you wish by updating your NotCurrent field to False - previously 'deleted' records by returning a recordset with your NotCurrent field equal to True.

You can bet your bottom dollar that at some point in the future, somebody is going to want to compare/query/analyse/report on historical data which is not a problem as long as the data is actually still available - and you can say with a smile on your face - 'No problem'


Really deleting records without the confirmation

If, on the other hand you really do want to delete your records without having the 'You are about to' etc message box, then the general syntax (assuming numerical record ID) is

CODE
If Msgbox ("You are about to delete data, do you want to continue?", vbYesNo + vbCritical,"Confirm Delete")
= vbYes Then
  CurrentDb.Execute "DELETE MyID FROM MyTable WHERE MyID = " & Me.MyIDControl, dbFailOnError
End If

Use it advisably though as there's no going back, particularly when referential integrity/cascade deletes applies!!!

Edit Discussion
Custom Search
Thank you for your support!
This page has been accessed 3,766 times.  This page was last modified 01:36, 10 February 2012 by Jack Leach. Contributions by Alan Greenwood  Disclaimers