Automatically delete tables that are > 120 days. 1

[chanman525]

Hello all. I searched tek tips and couldn't find anything on this. I have some tables that are automatically created at the beginning of the month when new information is imported in. The tables get very large in size so I only like to keep the previous 4 months in the database. As of right now, we have to manually go in and delete the tables and compact the database.

So does anyone have some code examples that I could look at that would show me how to delete tables that are older than 120 days? Any help is very much appreciated.

Thanks again

Chanman525

 

[JoeAtWork]

Dim td As TableDef
    
For Each td In CurrentDb.TableDefs
    [COLOR=green]'Don't delete any system tables[/color]
    If Left(td.Name, 4) <> "MSys" Then
       If td.DateCreated < CDate("January 1, 2007") Then
           CurrentDb.TableDefs.Delete td.Name
       End If
    End If
Next

 

[chanman525]

That's good stuff Joe. Thanks a lot.