Purge of Data

[tekquest]

All,

I have a database that is used to keep my customer information, there has been a privacy act implemented and finalised data that is 7 years old needs to be purged.

I have a table with information on a client, their purchase information and when they paid for the document, or if they took the item out on a lease / payback, when they have started and completed the payments.

Can I implement a filter that will delete any data that is finalised? (e.g. a customer "johnsmith" took out a payback scheme for 2 months on a PC on the 01-01-03, he has paid 50$ a week for 20 weeks = $1000 and has finalised the deal on the date of 01-06-03, the contract is now finalised. Can I purge this data and any associated details (his name, contact telephone, payment ammount etc) after 7 years from the date of the 01-06-03???

Is this a macro that should be built? or a function? I would like to put it on my switchboard and password protect it (the purges don't have to be dynamic, at the click of a button would be perfect) so other staff cannot access this??

Any info would be greatly appreciated, if you need further clarification on what I want to do, please ask me,

Thanks very much for reading,

Luke

 

[danvlas]

Well, you could create the relationship with 'Cascade Delete Related Records'
Then you could create a recordset that retrieves the Customers that should be purged, and finally delete those customers via a loop through that recordset. All subsequent information in child tables will then be deleted automatically.

Good luck


Daniel Vlas
Systems Consultant

 

[tekquest]

Hi Daniel,

I have had a look at the database and read about "cascade delete related records" but I am unclear about one thing, is this a macro, or a module?

There is already an existing relationship of the tables, would I be using a Dynaset record set?

Thanks,

Luke

 

[tekquest]

I also get the error message when I try to add the "enforce referential integrity" and tick "cascade delete related records" which is:

"Ms access cannot create this relationship and enforce referential integrity"

Data in the table "Recnal" violates referential integrity rules"

obviously I know I cannot tick the "enforce referential integrity" and then tick the "cascade delete related records" because they do not become available unless "enforce ref.. " is ticked.

Can I create a new relationship with this this table to do so? and still get the same outcome?

Thanks,

Luke