Delete from one table, send to another 1

[electricamp]

I have a database of information. When i dont need this information anymore, i would like to delete it from my main table and send it to an "archive table". Is this possible. I also wouldnt mind just dimming the record in the table that it originally resides in. Help would be greatly appreciated.

Peace

 

[JerryDennison]

If the database in not getting to large in size to be unmanageable you might want to consider simply adding a Yes/No field to your table called Inactive and setting it's value to true for those records you no longer need to view but would still like to have for historical purposes. Then for your forms and reports only include those records that are Active by setting the criteria of the Inactive field to False.

 

[Praxden]

I have a sample archive program that does what you want. If you would like it please email me at jbrooks@triad.rr.com and I'll send it to you.

 

[BNPMike]

Just use SQL

Step 1 Write a select query with a where clause which selects your records eg where date earlier than 20 days ago.

Step 2 Run it to see if it is picking the records you intended.

Step 3 Turn it into an append query ie insert into archive table etc and save it as QAppArchive

Step 4 Now turn it back to a select and then into a delete query on the main database still with exactly the same selection criteria. save it as QDelOld

Step 5 Stick the two together in a macro

Step 6 Press the button

Don't use VBA unless you have to. Life's to short.

ps periodically reorganise your database to recover wasted space etc mike.stephens@bnpparibas.com