Code to delete records older than a week.

[Johnrg]

Guys,

We have a very simple, one table, access database attached to the backend of a peice of scanning equipment.

We make thousands and thousands of scans every day and the database fill ups very fast and gets slow.

We transfer this data into another program each day for reporting and record keeping so there is no need to keep this raw scanner data any longer than about a week.

Is there anyway to add some simple code to AUTOMATICALLY delete all records that are over a week old on a sort of revolving basis? - maybe at midnight when there is no one there? or maybe just once a week to delete records older than 7 days?

The computer, database and the scanner are ALWAYS turned on, we hardly ever turn it off so its not something that can be programmed at startup or shutdown.

Could anyone let me know if this is possible or easy to do? and how to do it?

Thanks

JohnG

 

[pbaldy]

You can use the timer event of a form to perform a process at a given interval, but personally I'd create a database that did your delete when it started (autoexec macro is one way) and then closes itself. Then I'd call that db from Windows Scheduled Tasks as appropriate. The method would be to execute a delete query with the appropriate criteria, such as

WHERE DateField < Date() - 7

 

[Johnrg]

Thanks for the reply.
As I mentioned the database never closes or opens?
Can you elaborate on how to do it with a form?
To be honest I have no idea.
Thanks
JohnG

 

[Dairy Farmer]

but personally I'd create a database that did your delete when it started (autoexec macro is one way) and then closes itself. Then I'd call that db from Windows Scheduled Tasks as appropriate. The method would be to execute a delete query with the appropriate criteria

Create a new FE(a new database with linked tables to the BE).
Create a Delete Query.
Create a Form with an On Load Procedure that runs the query then closes the FE. Make this VB not a Macro.
Install Runtimes on the server.
Copy the FE to the server.
Use Scheduled Tasks to run the FE every day.

FE = Front End (a database with linked tables to the BE).
BE = Back End (a database that holds only the tables).
Delete Query = a query that deletes data from a table based on criteria (i.e. data older than X days)
On Load Procedure = Macro or Function that runs when a form or report is loaded.
Runtimes = A free aplication from Microsoft that allows the use of a database without the need to install Access.

 

[pbaldy]

You did mention the database never closes; I mentioned making another one for this task. I would not load it on the server as mentioned by Dairy, particularly since your computer is on all the time anyway.

If you want to go the timer route, you use the timer event of a form that will be left open. At some interval you check the time and when it's the appropriate time you run your process. Since you only want to do it once a day, I personally wouldn't want the admittedly small overhead of an event running all the time, so I'd use the second db method.