Deleting records.

  • Thread starter Thread starter Belle
  • Start date Start date
B

Belle

Is there any way that I can have my database delete my
records after they are there for 30 days.
Can anyone please help.
thanks,
Belle
 
Belle,

First you should add a date field to your table.
When you create a record you can place todays date in that
field.
The easiest way to do that is by writing =Date() as
default value for that field.

Secondly you can write a Function DeleteOld

Public Function DeleteOld() As Boolean
Dim booOK As Boolean
Dim strSQL As String

On Error GoTo Err_DeleteOld

booOK = True
strSQL = "DELETE * FROM tblTable " & _
"WHERE creationDate + 30 <= Date();"
DoCmd.RunSQL strSQL
DoEvents

Exit_DeleteOld:
DeleteOld = booOK
Exit Function

Err_DeleteOld:
MsgBox Err.Number & " - " & Err.Description,
vbExclamation, "Deleting older records"
booOK = False
Resume Exit_DeleteOld
End Function

In the SQL statement is "tblTable" your table name
and "creationDate" is the field that contains the creation
date.

Then you should trigger that function on the moment you
open your datbase, or the main form or when you close your
database or main form.
e.g. You can make a macro Autoexec that contains the
command: RunCode with function name DeleteOld().

Hope you can figure that out.

Success

R.W.
 
add a field "date entered" into your records and set its
default to date() (todays date)
then create a "delete" query that has the "date entered"
field's criteria set to "< (date()-30"
 
Hi,
You can use a Delete query for this:
DELETE yourTable.* From yourTable
Where [DateEntered] <= DateAdd("d",-30,Date());

Of course you must have a field in each table containing a
field that states when the record was entered. If you don't
have this, there is no way of knowing how long the records
have been there.

Also, you must delete child record first.
 
Back
Top