Delete before date that is continuously moving (1 Viewer)

[jazsriel]

Okay, I am not sure if this belongs in the table section, but it is dealing with the data of tables.

I am looking for a way to click a button and have information from a table deleted if it has been in the DB for more than 3 years (not sure the time frame yet, but lets just go with that for now). I have no information on how to accomlish this but I am open to any and all ideas. I just do not want the user to have to input a date, as that is when things will get sticky, some people where I work tend to type numbers into the DB incorrectly.

I have a way to delete empty fields using this (which was going to be part of my question):
CurrentDb.Execute "DELETE * FROM tablenamehere WHERE fieldnamehere Is Null;"

 

[jdraw]

A few points for consideration.
-DELETEs can be so permanent, you might consider a new field in the record indicating "inActive".
That way the data is still available if ever needed. You can adjust current code to deal with "inActive" records.

-do validation/consistency checks in the BeforeUpdate event of your Form. The idea here is to prevent "bad" data from being inputted and accepted by your code. From a different view, those who type numbers into the DB incorrectly have found a weakness in your validation. Your process is allowing bad data to be accepted.

-Do some analysis by checking your current data to see how extensive the issue is and possibly "why". Does the bad data come from 1 user? Is there a pattern to these "unwanted/incomplete" records. Perhaps, additional training of users is in order.

 

[The_Doc_Man]

First, I will emphasize and support jdraw's comments completely. Deleting data is not always a good idea, particularly if one of the functions of the database involves historical information.

Second, if you want to delete everything "older than x years old" then look up the DateAdd function. You would also need DateSerial and a couple of other functions.

DateAdd function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

DateSerial function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

Month function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

Day function (Visual Basic for Applications)

Office VBA reference topic

learn.microsoft.com

To get a date that would be "x years old as of today" use

DIM OldDate as Date, X As Integer                '(or could use X As LONG...  your choice)
...
...
OldDate = DateSerial( DateAdd( "yyyy", -x, Date() ), Month( Date() ), Day( Date() ) )

This will give you the date that is the same month and day as today but for x years ago.

 

[Pat Hartman]

While I agree that marking the data as archived might be a better solution, the technique for both is the same. One uses a delete query and the other uses an update query. Doc beat me to the details

 

[Cotswold]

One of my systems installed at a medium sized company would after three years get close to the 2GB limit. I supplied a backup and delete that they could run at a weekend, or in downtime. I could see little little point in dragging a huge BE up and down the network, particularly as most of it was never used. Plus, in a business, only the current data is of interest and the older data becomes less and less relevant. Often transactions and invoices three months old have little relevance in day to day operations. A simplified version of the routine worked as follows:

1. Compact the BE
2. Create several backup copies of the BE database
3. Use the utility to create what will be an archive copy of the current BE data, suitably and system named.
4. In the current BE, set to delete all tickets and invoices older than a date set but prevent anything less than 2 years from being deleted
5. Compact and remove the deleted records
6. Open and check the archive BE
7. Open and check the new smaller current database.
8. Any errors restore one of the backups (2) and redo
9. Go to the pub and congratulate yourself

If they needed to access older data then they would select from utilities to disconnect the current BE and connect to the archive BE database required. From there they could access and report from the archive but couldn't make any additions, or amendments.

I will say that despite all the effort, cost and the perceived, or imagined importance, the old archive databases were rarely used. Which is understandable. After all even on a personal level, how often does anyone look at bank, or credit card statements of three years ago? But electronic copies are often kept..........just in case.

 

[adhoustonj]

You can also call a function. I run through a number of tables with this below that gets called when running a daily update procedure each day.

Function removeold()
Dim db As DAO.Database
Dim strSQL As String

Set db = CurrentDb()

'removed tblRecords > 5 yrs old
strSQL = "DELETE tblRecords.*, tblRecords.RDate " _
& "From tblRecords " _
& "WHERE (((tblRecords.RDate)<Date()-1826))"
db.Execute strSQL

Set db = Nothing
End Function

 

[CJ_London]

not sure the time frame yet,

In the UK you are supposed to keep most records for at least 6 years. Admittedly this does not have to be the actual data record, it could be a pdf of an invoice for example. Anything that has potential tax implications is 6 years, 20 if tax avoidance is suspected. Personnel records vary depending on the type of data.

So suggest you check with your authorities as to what their requirements are

 

[The_Doc_Man]

True... the U.S. Navy had a 10-year record retention requirement, but with a loophole. The original records, once technically obsolete, could be converted to another format and moved to an external file until they became legally obsolete. "Legally obsolete" is the point at which old claims and research requests would be denied. "Technically obsolete" meant that the records had reached a point that nothing could be done with them if someone needed a problem corrected. I.e. corrections would be done by hand, but research (of the "what happened" variety) would still have meaning.

 

[jazsriel]

A few points for consideration.
-DELETEs can be so permanent, you might consider a new field in the record indicating "inActive".
That way the data is still available if ever needed. You can adjust current code to deal with "inActive" records.

-do validation/consistency checks in the BeforeUpdate event of your Form. The idea here is to prevent "bad" data from being inputted and accepted by your code. From a different view, those who type numbers into the DB incorrectly have found a weakness in your validation. Your process is allowing bad data to be accepted.

-Do some analysis by checking your current data to see how extensive the issue is and possibly "why". Does the bad data come from 1 user? Is there a pattern to these "unwanted/incomplete" records. Perhaps, additional training of users is in order.

I have been going around and around looking at this post for days. Actually all of the posts. a lot of focus seems to be on the length of time. The length of time I was using as an example was to allow for context was all. I have already checked for our time limits, I am just not authorized to post them into a forum if that helps clear things up some. I know it might have been a bit confusing. When I have a bit more time I will look over the suggestions given here more in depth. Bus basically I am looking for something that will allow the use of a button, once it is clicked (pressed), a pop up window will appear and ask for the date range to delete. But like I said I will look at the suggestions provided and see where I can get with it. Thank you for the posts.

 

[Pat Hartman]

If you are going to delete the data, you use a delete query (very dangerous if you are prompting the user for a value). Otherwise you use an update query to just set the archived date.

Have you considered the possibility of the user entering an invalid date range and deleting active data? I would NEVER run a delete query that prompted for user input. It is simply too risky. If your business rule is three years, then you can easily identify all data > three years old.

Open the QBE and choose the table you want to delete from.
Change the query type to delete.
Field cell add the calculation:

CalcValateDiff("m", YourDateField, Date())

then in the Criteria cell, add the expression to compare it to:
> 36

I used month rather than year because > 3 years will keep an extra 11 months of data. Using months just keeps an extra full month.

ALWAYS back up the database before testing an update query for the first time and NEVER do it on production data unless you have a death wish.

 

[jazsriel]

Well the only person that would be running this delete process would be me. But it would just simplify things considering I have several tables I have to run through.

 

[Pat Hartman]

Then just use a Where clause that references a form field:

Where SomeDate < Forms!yourform!DeleteDate

This will select all rows where some date is earlier than the form date.

 

[jazsriel]

I will keep playing around with the ideas that have been presented here, but I took a look at my database and currently it is not very large just under 8MB, so not really that large yet. I am mostly looking at this for when the DB becomes excessively large and needs to be cleaned up. Who knows maybe I will be retired by then lol. But thank you all for the ideas, direction and help with this problem.