Question Move a record to another Table Automatically

[tils1975]

Using access 2003, i would like to add a button to a form so that when clicked all the data in that record is moved (cut and paste) to another identical table, like archive. Does anyone have any ideas ???

 

[jdraw]

Research INSERT INTO SQL query

 

[Beetle]

Have you considered simply adding a boolean (Yes/No) field to your table to mark records as active/inactive, then having your queries, forms and reports filtered for only active records unless otherwise necessary?

 

[tils1975]

Hi jdraw,

Thankyou, for a quick reply. What does this come under in sql, do you know any good sites.

 

[tils1975]

Hi Beetle, thankyou. I want the action to act as a delete, however instead off deleting i would prefer it to be stored in another backup table. a safty net.

 

[Beetle]

a safety net.

A safety net from what? "Archiving" records to a duplicate table within the same database file doesn't protect you from things like file corruption, or someone (accidentally or otherwise) deleting the file. In those cases you lose everything, including your archive table. It does give you some recourse if someone "deletes" a record that they shouldn't have, but if you just mark records as inactive instead of deleting them then you don't need to be protected from this in the first place.

If you want to proceed, you would need to run an Append query (which begins with INSERT INTO like JDraw referenced in #2) to add the appropriate record(s) from the main table to the archive table, then a Delete query to delete the same record(s) from the main table. You can use the QBE grid (aka Query Builder) to play around with the syntax if you want. I recommend doing so on a backup copy of your database. Open the query designer and select Append query, then you can build the query how you need and switch to SQL view to see what the actual SQL syntax looks like if you want. Ditto for the Delete query. This is a good way to start to learn some SQL syntax. Build it with the QBE then switch to SQL view.

Having said that, I still don't see the benefit in your case. This method adds extra overhead and bloat without any real gain. You still have the same number of records, but now they're spread over two tables. If you had a situation where you were approaching the 2GB file size limit for Access, and you didn't have an option of using a different database (like SQL Server) for the backend, then it would make sense to archive records to a table in a different back end file that you link to, but you haven't said that you have that problem. An Access database can hold millions of records, so most of the types of applications that are developed with Access don't approach the file size limit (at least from a data standpoint) unless they are storing images or other objects that take up a lot of space, or they have been in use for a long time. There are exceptions of course, but the point is I don't see any benefit in archiving "deleted" records to a duplicate table unless you had a file size problem, and in that case you would archive to a table in a different back end.