Deleting historical records after set time spans

[roars]

I am relatively new to programming and have quite a few Questions, but for the moment I would like to delete historical data logged in Access after a set time span. Is there anyone who knows how this can be done?

[Negative0]

The way I would approach this would be to save a date with the data when it is made historical. Somewhere in your program, likely when it closes, check the date of the historical data versus the current date. If it is older than the timespan you need to keep it for, you can then delete it.

You can use some SQL code to do this, it may look something like this:

dim x as Date
x = DateAdd("m", -3, Now)   '3 Months OLD
cSQL = "DELETE tblName where dbdDate <='" & x & "'"
db.execute cSQL

Hope this Helps

[JasonGS]

Very similar; the code I use frequently...

Public Function DeleteRecordsOlderThan(tTable As String, tDateField As String, iDays As Integer)
    Dim DaysAgo As Date
    DaysAgo = DateAdd("d", iDays, Now)
    adoConnection.Execute "DELETE FROM " & tTable & " WHERE " & tDateField & " < #" & DaysAgo & "#"
End Function

Private Sub Command1_Click()
    ' Delete records from myTable that have not been
    ' updated in the last 90 days.
    DeleteRecordsOlderThan "myTable", "LastUpdated", 90
End Sub

[Chris]

You need to compact the database after you delete the history records. This is to keep the database size small. Because normally, Access is not really goto delete all the records but instead just remark as DELETED. So the Compact database procedure for DAO is just like below:

Option Explicit
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Public Sub Main()
Screen.MousePointer = vbHourglass
Dim Db As DAO.Database
Set Db = DBEngine.Workspaces(0).OpenDatabase(App.Path & "\Biblio.mdb", False, False)

'Purge the unused record.
Db.Execute "DELETE * FROM [Title Author];"
Db.Close
Set Db = Nothing
DBEngine.CompactDatabase App.Path & "\Biblio.mdb", App.Path & "\Biblio1.mdb", dbLangGeneral

'Delay 3sec for the computer to catch up.
Sleep 3000

'Delete the current database
If Dir(App.Path & "\Biblio.mdb") <> "" Then Kill App.Path & "\Biblio.mdb"
'Rename the temp databse to the original database name.
Name App.Path & "\Biblio1.mdb" As App.Path & "\Biblio.mdb"
Screen.MousePointer = vbDefault
End Sub