how to delete records in a table and save them in a deleted rec table

[cromio]

I have a table with a subject id. The table can have multiple records for the same id number. I want to be able to select the id and have all records matching the id deleted from table one and saved in a second (deleted records) table. I would like to be able to execute this with a button in a form in which it would execute for the current record.

 

[alvechurchdata]

You could use a pair of queries; an Append Query to copy the records to the Deleted table then a Delete Query to delete them from the original table. There is always the worry that the two queries might not be affecting exactly the same group of records.

A safer technique would be to open both recordsets and step through moving the records one at a time.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[fredericofonseca]

Looping through two recordsets is only viable for very small tables.

If there is NO chance that someone may add/delete records from the table you are trying to delete the records from (this at the same time you are doing your own operation) then a "insert into ... select from" followed by a "delete from" is the best option.

If someone else may be updating the table at the same time then you have a problem as Access will not make life easier on you. A possible way around this is to perform the insert/delete sequence, and then perform another select to see if a further insert/delete is required.



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[alvechurchdata]

Looping through two recordsets is only viable for very small tables.

I agree it could take several minutes (or hours) on a large table but it's the only way I can think of getting some sort of audit trail to guarantee that the same records are being copied and deleted and that no records are going to get lost.

Having said that I've just had another thought. The process could be done with three separate queries and a report:

Create a temporary table and copy the records to that table.

Copy all the records from the temporary table to the archive table.

Use a subquery to delete all the records in the main table which match the records in the temporary table.

Run a report off the temporary table as an audit trail of the archived records.


The temporary table gives us a snapshot view of the records to be processed in the main table and won't be affected by any other users' operations.

Geoff Franklin

http://www.alvechurchdata.co.uk