Saved Deleted Data
-
- StarLounger
- Posts: 76
- Joined: 14 Jul 2010, 19:35
Saved Deleted Data
I am trying to create a backup for all delete records for my database. When a person is deleted off the main table I want all his information to get set to a backup table to be stored in. Is there a good way to do this, I would like it to add it to my delete button. So on click, copy the record to the backup table then delete the record off the main table.
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Saved Deleted Data
Instead of deleting records, I would add a Yes/No field Deleted to the table.
The Delete button would set this field to True.
Create a query based on the table that selects all records for which Deleted = False, and use this query as record source for forms, reports etc.
That way, the end user only sees the non-deleted records, but it's easy to view "deleted" records, and even to restore them if necessary - simply set the Deleted field to False again.
The Delete button would set this field to True.
Create a query based on the table that selects all records for which Deleted = False, and use this query as record source for forms, reports etc.
That way, the end user only sees the non-deleted records, but it's easy to view "deleted" records, and even to restore them if necessary - simply set the Deleted field to False again.
Best wishes,
Hans
Hans
-
- Her Majesty
- Posts: 499
- Joined: 19 Jan 2010, 07:13
Re: Saved Deleted Data
If you actually want to go to the trouble of moving the "deleted" records into another database (which makes sense if the database becomes large), you would have to disallow deletions on all the forms and subforms and handle moving the record to another database in the code behind the application. Unless you are extremely competent in VBA and Access, I wouldn't recommend that you go there. One of the obvious drawbacks to this approach is that you no longer have the original record in the current database to keep duplicates out.
Charlotte
-
- StarLounger
- Posts: 52
- Joined: 04 Feb 2010, 17:14
- Location: Brookings, South Dakota
Re: Saved Deleted Data
Just to add my
Similar to Hans' suggestion, I use an end date column (always on any table I am not using as a selection [list/combobox] source) so I can keep some kind of a time history on the data changes. Yes, it does increase the db size, but with computer speeds, multiprocessors and for me, so many of my databases use a SQL Server backend now, that growth is not the near the hindrance that it once was. It really takes out a lot of grief out of the db data maintenance.
Thanks.
Similar to Hans' suggestion, I use an end date column (always on any table I am not using as a selection [list/combobox] source) so I can keep some kind of a time history on the data changes. Yes, it does increase the db size, but with computer speeds, multiprocessors and for me, so many of my databases use a SQL Server backend now, that growth is not the near the hindrance that it once was. It really takes out a lot of grief out of the db data maintenance.
Thanks.
gary
Those who dance are considered insane by those who can't hear the music. - George Carlin
-
- StarLounger
- Posts: 78
- Joined: 10 Feb 2010, 12:54
- Location: Jeddah, Saudi Arabia
Re: Saved Deleted Data
I would do this with a Trigger. The main advantage being that you don't have to write any code other than the action that will occur when the trigger is fired.
A trigger is a special kind of stored procedure that goes into effect when you modify data in a specified table using one or more data modification operations: UPDATE, INSERT, or DELETE. Triggers can query other tables and can include complex SQL statements. They are primarily useful for enforcing complex business rules or requirements. For example, you could control whether to allow an order to be inserted based on a customer's current account status. (Office.com)
See here http://office.microsoft.com/en-gb/acces ... 65515.aspx for information on how to create a trigger.
Regards,
Kevin Bell
A trigger is a special kind of stored procedure that goes into effect when you modify data in a specified table using one or more data modification operations: UPDATE, INSERT, or DELETE. Triggers can query other tables and can include complex SQL statements. They are primarily useful for enforcing complex business rules or requirements. For example, you could control whether to allow an order to be inserted based on a customer's current account status. (Office.com)
See here http://office.microsoft.com/en-gb/acces ... 65515.aspx for information on how to create a trigger.
Regards,
Kevin Bell
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Saved Deleted Data
Hi Kevin,
Triggers work very well in SQL Server, but Stew (the one who started this thread) uses Access. Access doesn't have triggers at the table level.
Triggers work very well in SQL Server, but Stew (the one who started this thread) uses Access. Access doesn't have triggers at the table level.
Best wishes,
Hans
Hans
-
- StarLounger
- Posts: 78
- Joined: 10 Feb 2010, 12:54
- Location: Jeddah, Saudi Arabia
Re: Saved Deleted Data
Hi Hans,
I'm puzzled because the link I posted is from Office.com and shows how to create a trigger in Access at the table level in Access 2003.
Cheers,
Kevin
I'm puzzled because the link I posted is from Office.com and shows how to create a trigger in Access at the table level in Access 2003.
Cheers,
Kevin
-
- StarLounger
- Posts: 78
- Joined: 10 Feb 2010, 12:54
- Location: Jeddah, Saudi Arabia
Re: Saved Deleted Data
After re-reading the information in the link I guess I am even more confused as at the head of the section called Creating A Trigger there is a line that says,
" Note The information in this topic applies only to a Microsoft Access project (.adp)."
I would hazard a guess that this means that this Access Project is one that extracts its data from a SQL Server.
Go figure?????
Cheers,
Kevin
" Note The information in this topic applies only to a Microsoft Access project (.adp)."
I would hazard a guess that this means that this Access Project is one that extracts its data from a SQL Server.
Go figure?????
Cheers,
Kevin
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Saved Deleted Data
Yep, an .adp is an Access database that connects directly to a SQL Server database. A 'standard' Access database is .mdb or .accdb.
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 528
- Joined: 26 Jan 2010, 15:14
-
- Administrator
- Posts: 78595
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands