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. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|
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 |