Purging Records Automatically

[EndersG]

I have a table of invoices that are growing too huge for my database to keep and still have it run efficiently. Fortunately, I have found that I am only required to keep a 2-month backlog of invoice for my department's purposes. In other words, I want to purge all records (DeleteQuery) from my Billing table that are [< Date()-60] days. However, I want to create a routine that will automatically do this for me at Startup, if possible (or immediately after the database loads). I do not want to rely on this being done manually. I would also like it to run a CompactDatabase routine of the database once the purge is complete. Is this possible? And if so, under what Event procedure should I code this for the best possible results.

 

[Randi A. Dertzo]

I've done something like this just this week. Here's how I proceeded:

Create a Delete query (Here's mine:
DELETE TbDossiers.* FROM TbDossiers
WHERE NomFamille IS NULL;

I then added to the Form_Load procedure (My form is not linked to any tables. It's just a menu form.)

Private Sub Form_Load()
Dim stDocName As String
DoCmd.SetWarnings False
stDocName = "RqDelNew"
DoCmd.OpenQuery stDocName, acNormal, acEdit
DoCmd.SetWarnings True
end sub

Also include errors handling...

As for compacting, I don't know how to do it in VBA. I use a batch file to do this.

Maybe you could create a batch file to automatically do this as well...

Hope it helps.