Here is the scenario: All table entries have a date field rr_date and is populated when the entry is first written. This date is the current date plus 7 years. It is the date when the table entry is purge.
The rr_date is defined to SQL as:
rr_date datetime
Here is the code that does the write:
Expand|Select|Wrap|Line Numbers
- 'set error handler
- On Error GoTo ErrorHandler
- 'set current date plus seven years
- Dim datex As Date
- datex = DateAdd("yyyy", 7, Date)
- Dim rs As DAO.Recordset
- Dim db As Database
- Dim strSQL As String
- Set db = CurrentDb
- Set rs = db.OpenRecordset("dbo_SignIn", dbOpenDynaset, dbSeeChanges)
- With rs
- .AddNew
- .Fields("FName") = "John"
- .Fields("LName") = "Doe"
- .Fields("rr_date") = datex
- .Update
- End With
- MsgBox "Success- Your record was added to table dbo_SignIn.", vbInformation, gblDB
- rs.Close
- db.Close
- Set rs = Nothing
- Set db = Nothing
Here the code that purges:
Expand|Select|Wrap|Line Numbers
- 'set message record retention puge is starting'
- Me.frmMsg.Value = Now() & " Starting Record Retention purge ..."
- Forms![dboSignIn].Form.Refresh
- ' See how many records need to be purged
- Dim rs As DAO.Recordset
- Dim db As Database
- Dim strSQL As String
- Set db = CurrentDb
- strSQL = "Select * FROM dbo_SignIN WHERE rr_date < date()" ';"
- 'Debug.Print strSQL
- Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
- Me.frmMsg.Value = Me.frmMsg.Value & vbNewLine & Now() & " Records to be purged = " & rs.RecordCount & "."
- Forms![dboSignIn].Form.Refresh