Delete old records automatically?

[Guest]

I have a Database that has 1,424,245 records. Access 2000. We have a proxy
server that logs everything. So I have an IPAdress, Date, Time, Website
field. I generate reports and whatever from this data. It's only 5 weeks old
and working great, but I only have a need to keep 2 weeks worth of data. The
logs that are imported into Access are saved elsewhere so I just use the
database to crunch reports out. However if my boss calls and says I want Joe
Smiths activity from 1 month ago I want to be able to import it, run the
report, without Access automatically deleting it right away because of the
date stamp. If I can import, run report, and have it delete thats fine. Is
that possible? I hope I am making sense.
I don't have the delete feature even setup...I guess that's my inital
question. Can I say I want Access to delete all records older than 14 days?

 

[Guest]

Instead of deleting why not ad a flag field (Yes/No) for archive/history?
Use an update query to set the flag. Criteria on your DateAction field
would be --
<Date()-14
OR
<DateAdd("w",-2,Date())

 

[Guest]

I didn't know you could archive it. Will it shrink the database? I know 150MB
isn't that big, but shaving everything to a 2 week period is about 800,000
records and speeds up the querys. I guess that's my only concern...I wait way
to long for a query to run. Knowing I don't need anything older than 2 weeks
(except for a rare occasion) I just figured deleting would go quicker.

 

[Guest]

Hi, Jeri.

As long as you have a way to query the server to temporarily add old records
for a report, and that the number of instances you need to do it are few, it
seems prudent to keep your report table "lean and mean". Use a Delete query
to remove the old records:

DELETE YourTable.YourDateField
FROM YourTable
WHERE (([YourDateField]<Now()-14));

Hope that helps.
Sprinks

 

[Guest]

Why not flag all over two weeks and delete all over two months?

 

[Guest]

Thanks Sprinks...that's what I was looking to do!

Hi, Jeri.

As long as you have a way to query the server to temporarily add old records
for a report, and that the number of instances you need to do it are few, it
seems prudent to keep your report table "lean and mean". Use a Delete query
to remove the old records:

DELETE YourTable.YourDateField
FROM YourTable
WHERE (([YourDateField]<Now()-14));

Hope that helps.
Sprinks

I have a Database that has 1,424,245 records. Access 2000. We have a proxy
server that logs everything. So I have an IPAdress, Date, Time, Website
field. I generate reports and whatever from this data. It's only 5 weeks old
and working great, but I only have a need to keep 2 weeks worth of data. The
logs that are imported into Access are saved elsewhere so I just use the
database to crunch reports out. However if my boss calls and says I want Joe
Smiths activity from 1 month ago I want to be able to import it, run the
report, without Access automatically deleting it right away because of the
date stamp. If I can import, run report, and have it delete thats fine. Is
that possible? I hope I am making sense.
I don't have the delete feature even setup...I guess that's my inital
question. Can I say I want Access to delete all records older than 14 days?