Transfer from one table to another table for histrory file

[Emir via AccessMonster.com]

I am working on one table. After a month I need to transfer the records from
this current table to another table (as my history file table). Then after
the transfer I have to delete the records from the current table so I can use
it for the next month. Can somebody give me a simple set of source code that
could do these things, instead of the append query?

 

[tedmi]

Unless you are using a robust back-end database like SQL Server or Oracle,
it's usually not a good idea to move records between tables. The Jet db
engine lacks transaction controls: if the process fails between the time of
copying to the new destination and deleting from the old, the result will be
data corruption.

So if you are using the Jet datastore, I suggest you leave the records in
the same table, and use queries with appropriate criteria to select this
month's records for display and reporting.

 

[Klatuu]

Let me retract part of that.
Transactions are not supported for Linked Tables.
Sorry about that.

 

[Klatuu]

That is not correct. Jet does support transaction processing; however,
regardless of the engine, I would recommend using queries as you suggest to
filter the records and leave them all in one table.

Maybe there is no requirement at the moment, but you can rest assured that
at some point in time, users will want to be able to have reports that
include historical data and current data. With the data split into two
tables, that becomes more difficult.