Archiving

[Stockwell43]

Hello,

I am creating a Central Receiving Database for the user to enter ALL
documentation coming in to the department via Mail, Courier, inter-office
etc.., After 3 months we will not need this information in the database but
would still like to archive it just in case. Is there a way that after every
three months I can click a button and have the information transferred or
archived to another database that will not be used for production? If this
doesn't make sense to do or if my logic is off, please let me know.

Thanks!!

 

[KARL DEWEY]

The better way is to add an Archive field (Yes/No) to flag when the record is
history. Then in your queries have criteria to pull those not set as
archive.

You could add into your Autoexec macro to run an update query the set the
flag for all that are beyond your archive date -- <DateAdd("m",-3,Date())

 

[Stockwell43]

Hi Karl,

I would hate to go with the checkbox as there will be hundreds of records to
archive per month or quarter depending on how often management wants to do
this.

However, I like the Autoexe deal. Now would I place a button on the form
that would fire the query to do this? How do I create the AutoExe Macro? I
didn't see it in the menu option for macros.

Thanks!!

 

[KARL DEWEY]

No need for button. The macro is named Autoexec and runs everytime Access is
started unless you hold down the shift key. Choose action in macro OpenQuery
and enter the name of your query in the window titled Query Name.

The update query sets the flag for all that are beyond your archive date
using criteria <DateAdd("m",-3,Date()) on your date field. In the Update
To row of the query design view grid enter -1 (minus one) to set the flag of
the Archive Yes/No field.

Then in your regular queries have criteria to pull those not set as archive.
The criteria would be 0 (zero) for the Archive Yes/No field.