auto delete data

[Dana R]

I need to have the contents of certain tables within my database auto-delete
after a certain period (i.e. contents in table one delete after 30 days)of
time. Is that possible?

 

[NetworkTrade]

mmmmm - well I will be interested to see what code others will share on this.
In general there has to be a "run" to create some sort of action as
otherwise the db is just sitting there i.e. it does do anything on its own
per se.

With a query based on the table then it would be easy to have the criteria
as defined by date and then upon open of the db/query it would always recalc
each time and then the data would be manipulated away in terms of the query
result (on which a form or report would be based) but the data would still be
in the table.

but there are alot of smarter folk out there than I so......

 

[Lynn Trapp]

While there is nothing within an Access database to automagically delete
records, the simple answer to your question is "Yes." You can create a
delete query or queries to remove data and run those on some kind of a
schedule. However, I would start by asking you WHY? What is the business
reason for needing to delete data from your tables after 30 days? Databases
are designed to store data. So, let Access do what it does best. The typical
approach would be to simply inactivate records that are not currently
needed.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Nikos Yannacopoulos]

To begin with, the correct (general) answer is the one Lynn gave: don't
delete.
Of course, this being a general answer, there might be exceptions.
In that case: no code required! Just a simple delete query, with a
criterion like < Date() - 30 on the date field, and an autoexec macro to
run it, will do the job just fine.

HTH,
Nikos

 

[Dana R]

Thank you for the solution, Just FYI the reasoning in deleting content after
30 days is because this database is linked with at website for a company
intranet that registers users for a company training and a new class is every
month so the same database can be reused...

 

[Dana R]

Thank you for the reply...as far as the delete query, just to make sure I am
doing this right, how am I to run a delete query? I thought I was pretty good
with access but I am not sure how to do that.....

 

[Lynn Trapp]

Dana,
It still shouldn't be necessary to delete the data, especially if you need
to keep a record of past training classes. You just need to see if the
website has a field available that can set a given record to inactive and
use an update query to set that field.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Dana R]

Lynn I agree, but I am just the web designer and the administrators want a
clean slate after 30 days, as there is no need to keep the records in the
db...as a physical form is required by regulators with the employees
signature that they attended-
Thank you

 

[Lynn Trapp]

In that case, then I would recommend writing a Delete query that is called
by some macro. You can then schedule the macro to run using some kind of
scheduling software. You will probably want to have this scheduled to run
during the night, when you can be most certain that no one is using the
database.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm
Jeff Conrad's Big List: www.ltcomputerdesigns.com/JCReferences.html

 

[Nikos Yannacopoulos]

Dana,

Make a simple Select query like you usually do; add the < Date() - 30
criterion; when happy that it returns the correct records (those that
should be deleted), go Query > Delete to change its type to Delete, and
save. Next time you attempt to open the query like you normally would,
it will (warn and) delete the records instead of opening in database view.
To do that through a macro, use an OpenQuery action in it. If you name
the macro Autoexec, it will run every time you open the database file.

*Back-up* before you try anything you are not sure about!!!

HTH,
Nikos

 

[Nikos Yannacopoulos]

I tend to agree with Lynn again... why discard the information? Just so
someone has to dig into tons of paper (if it van be found!) next time a
department or HR manager asks a question? Uncle Murphy says they might
never ask if you keep the data there, while if you don't the question
will definitely come, and chances are it will be right after the deletion!

What the heck... the customer is always right, even though they may be
clueless... the sad part is it's harder to convince them they're asking
for the wrong thing, than just go away and do it :-(

Nikos

 

[ahuntertate]

I agree with both Nikos and Lynn present potential issues. Another solution
that may solve the issues with just deleting the data is to archive the data.
Copy the data from the current database to a linked database and delete from
the current “Live†database. This allows you remove the data from the
database referenced by the website but still store it for reporting later.

HTH
Andrew

 

[Nikos Yannacopoulos]

Yeah, I guess this makes sense.

Nikos