How to Handle Deleting Records in a Split Database (1 Viewer)
- Thread starter Weekleyba
- Start date
arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Today, 23:41
- Joined
- May 7, 2009
- Messages
- 19,247
add relationship to your table and Enforce Referential Integrity (and click Cascade delete on related records).
this means when you delete from Parent table, it delete the related records in the child (subform) tables.
this means when you delete from Parent table, it delete the related records in the child (subform) tables.
- Local time
- Today, 10:41
- Joined
- Feb 28, 2001
- Messages
- 27,224
It kind of depends. There is a school of thought (that not everyone ascribes to) that says "Never delete a record during normal operation. Just mark it obsolete and let your queries ignore it for you." The follow-up to that is when you do back-end maintenance such as a backup operation, you also clean out the records previously marked obsolete. Do that while your DB is out of service. There are also those who do an ARCHIVE operation by exporting obsolete records to an external file before deleting them. Let's just say there is no uniformity of opinion on your original question.
Arnelgp's answer of using Referential Integrity and the Cascade Delete option is how you do a parent-record deletion safely. But there is still the question of whether you should do it at all.
Arnelgp's answer of using Referential Integrity and the Cascade Delete option is how you do a parent-record deletion safely. But there is still the question of whether you should do it at all.
GPGeorge
George Hepworth
- Local time
- Today, 08:41
- Joined
- Nov 25, 2004
- Messages
- 1,915
Splitting a database (as is recommended) really has little or no bearing on the question of deleting records. When you link the interface objects to the tables in the database accdb, for all practical purposes managing the data remains the same.
- Local time
- Today, 11:41
- Joined
- Feb 19, 2002
- Messages
- 43,355
If the users have permission to delete records and I agree, with some applications or some tables, data should never be deleted, then give the user a button on the form and then add the delete code to the button. Keep in mind that if you use Cascade Delete, you need to understand that deleting the parent will delete the child records. Not all relationships should allow cascade delete. For example, if you have a state table, that would be the 1-side (parent) of the relationship. You probably #1 don't want to delete states at all, although if they haven't been used, it is ok and #2 you don't want to delete a customer if you accidentally try to delete California. Defining the relationship will prevent you from deleting a bunch of customers if you try to delete California. RI will prevent the state from being deleted but NOT if you allow cascade delete on this relationship. So, Cascade Delete is very powerful and I use it frequently but it is NOT appropriate for all relationships. You use it in cases where if the parent record were deleted, the child records would lose their meaning as in if you delete an Order, the OrderDetails have no reason to exist.
If the delete is for a single record on a subform, put a button on the subform if it is in continuous view. If it is in DS view, you can't use a button but you can add an unbound field that can be used for the same purpose. The scary thing is to try to put the delete button for a child record on the parent form. It is too risky because the delete will delete the Current subform record which may not be the record you think it is.
And finally, delete is forever so this is the one case I recommend that you use a prompt to give the user a second chance. Prompts when over used loose their meaning. Users just blow by them because they're used to just saying yes. Eventually, they just stop reading your message. So, Prompt only when the answer is actually critical.
If you start the application understanding that at some point the user will want to delete a record, then you have become a professional or are at least on the way
and then you accomodate the process from day 1. No one who hasn't created several significant applications ever thinks about this up front. That means as Doc suggested, "delete" is not physical for some tables. Instead you have a field or maybe two fields DateDeleted and possibly DeletedBy which get set. The flip side of this is that pretty much ALL queries that select data from the table in question need to ignore rows where DateDeleted is not null. Retrofitting this is certainly possible and maybe you should do that but it depends on the data and the cost of losing it accidentally.
If the delete is for a single record on a subform, put a button on the subform if it is in continuous view. If it is in DS view, you can't use a button but you can add an unbound field that can be used for the same purpose. The scary thing is to try to put the delete button for a child record on the parent form. It is too risky because the delete will delete the Current subform record which may not be the record you think it is.
And finally, delete is forever so this is the one case I recommend that you use a prompt to give the user a second chance. Prompts when over used loose their meaning. Users just blow by them because they're used to just saying yes. Eventually, they just stop reading your message. So, Prompt only when the answer is actually critical.
If you start the application understanding that at some point the user will want to delete a record, then you have become a professional or are at least on the way
and then you accomodate the process from day 1. No one who hasn't created several significant applications ever thinks about this up front. That means as Doc suggested, "delete" is not physical for some tables. Instead you have a field or maybe two fields DateDeleted and possibly DeletedBy which get set. The flip side of this is that pretty much ALL queries that select data from the table in question need to ignore rows where DateDeleted is not null. Retrofitting this is certainly possible and maybe you should do that but it depends on the data and the cost of losing it accidentally.
Last edited:
Cotswold
Active member
- Local time
- Today, 16:41
- Joined
- Dec 31, 2020
- Messages
- 528
I wouldn't archive deleted records but keep them in your live tables. Whole invoices could be lost, rather than odd tranasctions. The big question here is why are there deleted records? Is someone making lots of entry mistakes or is there a fiddle going on? Someone could be creating invoices and printing for cash sales and then deleting them, before collecting their reward. Or they could be creating cash tickets, then deleting , again before collecting their reward.
What I would do was have a field in the tables called Dele as a Yes/No. The table would also have a field to record who deleted it. I didn't have a reason for delete as it may make them think why it's there. Generally as advised in post#3 by the Doc Man
The underlying Query would have a filter option If Dele = False to exclude deleteds. I always thought it the better solution, as it was possible that compact/repair maintained a better table. If you have parent and child relationships the parent prevents the child from being seen, so you only need to delete the parent.
The users were given the option to delete records and as far as they knew they were deleted. The system administrator, or head of accounts, or myself then had a separate screen which allowed deleted records to be viewed. Also reports overall and on individual staff were included You can then see those making the most mistakes or committing fraud. They could also decide what steps to take against the company who was paying the bribe. Additionally, in some systems there was an option in the separate program to remove deletes by date range or user name. A few people lost their jobs at companies up and down the country.
What I would do was have a field in the tables called Dele as a Yes/No. The table would also have a field to record who deleted it. I didn't have a reason for delete as it may make them think why it's there. Generally as advised in post#3 by the Doc Man
The underlying Query would have a filter option If Dele = False to exclude deleteds. I always thought it the better solution, as it was possible that compact/repair maintained a better table. If you have parent and child relationships the parent prevents the child from being seen, so you only need to delete the parent.
The users were given the option to delete records and as far as they knew they were deleted. The system administrator, or head of accounts, or myself then had a separate screen which allowed deleted records to be viewed. Also reports overall and on individual staff were included You can then see those making the most mistakes or committing fraud. They could also decide what steps to take against the company who was paying the bribe. Additionally, in some systems there was an option in the separate program to remove deletes by date range or user name. A few people lost their jobs at companies up and down the country.
Last edited:
- Local time
- Today, 11:41
- Joined
- Feb 19, 2002
- Messages
- 43,355
Cotswold
Active member
- Local time
- Today, 16:41
- Joined
- Dec 31, 2020
- Messages
- 528
@PatHartman
Yes but that must be pretty obvious to everyone. In this case I didn't advise to archive and there really is no need to archive deleteds. A deleted record usually indicates it isn't required.
They are generated by an order cancellation, or after adding an order to the wrong customer, an entry error, a spurious order added by someone who thinks it is funny to book orders to others, or as I say fraud. As a general rule the vast majority are from cancellations, with the minimum number from fraud. The only archive I use is to backup older records.
In the UK invoices need to be kept for 7 years. Order transactions once complete are usually of no interest whatsoever to daily operations but of some use to management. So I will provide an archive option. This will transfer to archive all records older than say 3 to 5 years old, which is usually used once each year. Afterwards it will and remove them from the main database. If for some reason older records need to be accessed, then they can be by linking the archive database to the main. Of course, after the archive has completed those records will be deleted and on packing will also remove the hidden records that users thought they had deleted but actually only marked them hidden.
Yes but that must be pretty obvious to everyone. In this case I didn't advise to archive and there really is no need to archive deleteds. A deleted record usually indicates it isn't required.
They are generated by an order cancellation, or after adding an order to the wrong customer, an entry error, a spurious order added by someone who thinks it is funny to book orders to others, or as I say fraud. As a general rule the vast majority are from cancellations, with the minimum number from fraud. The only archive I use is to backup older records.
In the UK invoices need to be kept for 7 years. Order transactions once complete are usually of no interest whatsoever to daily operations but of some use to management. So I will provide an archive option. This will transfer to archive all records older than say 3 to 5 years old, which is usually used once each year. Afterwards it will and remove them from the main database. If for some reason older records need to be accessed, then they can be by linking the archive database to the main. Of course, after the archive has completed those records will be deleted and on packing will also remove the hidden records that users thought they had deleted but actually only marked them hidden.
kentgorrell
Member
- Local time
- Today, 16:41
- Joined
- Dec 5, 2020
- Messages
- 48
Must agree that a Deleted Date and Deleted By column is the ideal. Then filter your record sets to Where Deleted Date Is Null.
When users complain that data has gone missing and, as the developer, it's your fault, you can show them why and even recover it.
When users complain that data has gone missing and, as the developer, it's your fault, you can show them why and even recover it.
I really like these differentiations of reasons for users deleting data that are addressed by @Cotswold and @kentgorrell. When mistakes happen or data disappears, you want to know why - especially in order to do better in the future.
Similar threads
