How to Handle Deleting Records in a Split Database
- Thread starter Weekleyba
- Start date
arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Today, 21:42
- Joined
- May 7, 2009
- Messages
- 20,745
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, 07:42
- Joined
- Feb 28, 2001
- Messages
- 31,022
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, 05:42
- Joined
- Nov 25, 2004
- Messages
- 3,302
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.
Cotswold
Well-known member
- Local time
- Today, 13:42
- Joined
- Dec 31, 2020
- Messages
- 1,034
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:
Cotswold
Well-known member
- Local time
- Today, 13:42
- Joined
- Dec 31, 2020
- Messages
- 1,034
@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, 13:42
- Joined
- Dec 5, 2020
- Messages
- 53
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
