A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

auto delete data



 
 
Thread Tools Display Modes
  #1  
Old May 2nd, 2005, 08:01 PM
Dana R
external usenet poster
 
Posts: n/a
Default auto delete data

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?
  #2  
Old May 3rd, 2005, 02:44 AM
NetworkTrade
external usenet poster
 
Posts: n/a
Default

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


"Dana R" wrote:

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?

  #3  
Old May 3rd, 2005, 12:44 PM
Lynn Trapp
external usenet poster
 
Posts: n/a
Default

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


"Dana R" wrote in message
...
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?



  #4  
Old May 3rd, 2005, 01:45 PM
Nikos Yannacopoulos
external usenet poster
 
Posts: n/a
Default

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

NetworkTrade wrote:
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......

  #5  
Old May 3rd, 2005, 03:37 PM
Dana R
external usenet poster
 
Posts: n/a
Default

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

"Lynn Trapp" wrote:

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


"Dana R" wrote in message
...
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?




  #6  
Old May 3rd, 2005, 03:38 PM
Dana R
external usenet poster
 
Posts: n/a
Default

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

"Nikos Yannacopoulos" wrote:

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

NetworkTrade wrote:
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......


  #7  
Old May 3rd, 2005, 04:39 PM
Lynn Trapp
external usenet poster
 
Posts: n/a
Default

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" wrote in message
...
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...

"Lynn Trapp" wrote:

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


"Dana R" wrote in message
...
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?






  #8  
Old May 3rd, 2005, 05:51 PM
Dana R
external usenet poster
 
Posts: n/a
Default

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" wrote:

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" wrote in message
...
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...

"Lynn Trapp" wrote:

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


"Dana R" wrote in message
...
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?






  #9  
Old May 3rd, 2005, 07:32 PM
Lynn Trapp
external usenet poster
 
Posts: n/a
Default

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


"Dana R" wrote in message
...
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" wrote:

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" wrote in message
...
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...

"Lynn Trapp" wrote:

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


"Dana R" wrote in message
...
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?








  #10  
Old May 4th, 2005, 12:40 PM
Nikos Yannacopoulos
external usenet poster
 
Posts: n/a
Default

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

Dana R wrote:
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.....

"Nikos Yannacopoulos" wrote:


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

NetworkTrade wrote:

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


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Compare fields and delete ONLY duplicate data, not entire record Artmajor General Discussion 1 September 8th, 2004 05:59 AM
Is this possible with Excel Chart? q582gmzhi Charts and Charting 1 September 8th, 2004 03:33 AM
How do I delete multiple BLANK rows within data? The Old Bailey General Discussion 2 September 6th, 2004 12:08 PM
auto new record on enter or data entry Laura Q Using Forms 0 July 27th, 2004 05:53 PM


All times are GMT +1. The time now is 03:57 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.