Moving data from one table to another

[Abbos]

Hi there,

I have a simple database with a form that displays the contents of a table called Products. I want to be able to delete items from Products but these should be move into ProductsArchive table and I will then create a seperate form to view Products that have now been removed but were once currently live if that makes sense?

I would like to write some code that on the form when the user clicks on the delete button it will just move the data relating to that record into the ProductsArchive table.

Can anyone help?

 

[Abbos]

Got this working. For anyone else looking for help take a look at this thread:

http://www.access-programmers.co.uk/forums/showthread.php?t=100814&highlight=move+data

 

[neileg]

OK, but the alternative I would prefer is to leave all the products in one table and simply flag the ones that are obsolete.

 

[The_Doc_Man]

Nieleg is correct for tables that remain small and/or remain local. When you are getting to the point of having very large tables (many, many records), you will want to archive in order to reduce search time. When to do it is up to you. The odds are that you will not notice a difference in speed until your main tables get into the tens/hundreds of thousands of records. But eventually you might wish to archive to relieve bloat for records you really don't need in the active part of the DB.