Saved Deleted Data

[Stew]

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.

[HansV]

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.

[Charlotte]

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.

[geedeearr]

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.

[BigKev]

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

[HansV]

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.

[BigKev]

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

[BigKev]

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

[HansV]

Yep, an .adp is an Access database that connects directly to a SQL Server database. A 'standard' Access database is .mdb or .accdb.

[grovelli]

Triggers are available in Access 2010

[HansV]

Thanks.