Automatically Move Records (1 Viewer)

Hi,

I haven't used Access in years and now I've been thrown in at the deep end, so forgive me for any *silly* questions.

Basically, I'm trying to set up a system whereby one element of it is to automatically move a record from the 'Current Campaigns' table to the 'Archive' table when the Expiration Date (a field in the Campaigns table) is reached.

Is this possible? Easy or hard? VBA should do it, right? I'm used to working in Excel and I have created a system where this is done using =TODAY() and performing time&date calculations. Can this be done in Access too?

You can do it with a simple append and delete query. Not sure why anyone would ever want to do this, though. If you know which records you want to "archive" you can just use the same criteria to NOT include them in your result sets.

here is a quick example :

Dim stSQL
stSQL = "insert into arch_table (Select * from current_camp_table where [expire_date] <=#" & Date() & "#)"
DoCmd.RunSql stSQL
stSQL = "delete from current_camp_table where [expire_date] <=#" & Date() & "#"
DoCmd.RunSql stSQL

There are two schools of thought here. I'll lay them out.

1. Leave all records in the same table and filter by date.
Pro: Ease of use.
Con: As tables grow, searches take longer. Indexes get bigger.

2. Archive by date, with some selection criteria to choose the records to be appended to an archiving table, followed by a delete query with the exact same selection criteria.
Pro: Once the queries are written, they are easy to use. Tables remain smaller and thus easier to search.
Con: The overhead of having to periodically archive. The PITA of having to REMEMBER to archive.

I favor #2 ONLY because I consider it a neater, tidier solution. HOWEVER, #1 is perfectly legit if the number of expected records is relatively short. I wish I could tell you that there exists a hard line of table size above which you would always want to use the #2 solution. But it is a matter of what you can live with.