Delete old records automatically?

J

jeridbohmann

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?
 
K

KARL DEWEY

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())
 
J

jeridbohmann

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.
 
S

Sprinks

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
 
J

jeridbohmann

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

Sprinks said:
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

jeridbohmann said:
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?
Click to expand...
Click to expand...
 
Top