Deleting old tables

[crowegreg]

When I import a spreadsheet into a table, I'm saving off the existing table as an archived table. When I save the table, the table name has the date within it. Ex: PM_Archive_130802. My archived tables are bloating the DB, so I'd like to write code that deletes the oldest dated table.



I'm unclear on the most efficient way of doing this. I know I need to loop through the tables, and somehow determine which of the archived tables is the oldest date, then delete this file.

I can change the format of the date within the table name.


Thank you in advance!!

[crowegreg]

Here's what I came up with:

Dim lTbl As Long
Dim dBase As Database
Dim dteOldDate As Date
Dim strTableName As String

'Set current database to a variable
Set dBase = CurrentDb

'Confirms that tables exist
On Error Resume Next

'Assign today's date to variable dteOldDate for a starting point
dteOldDate = Date

'Loops through all tables, and locates PM_Archive tables
For lTbl = 0 To dBase.TableDefs.Count
If Left(dBase.TableDefs(lTbl).Name, 11) = "PM_Archive_" Then
'Determines if date within name of archive table is less than variable dteOldDate
If Mid(dBase.TableDefs(lTbl).Name, 14, 2) & "/" & Mid(dBase.TableDefs(lTbl).Name, 16, 2) & "/" & Mid(dBase.TableDefs(lTbl).Name, 12, 2) < dteOldDate Then

'Assign new dteOldDate
dteOldDate = Mid(dBase.TableDefs(lTbl).Name, 14, 2) & "/" & Mid(dBase.TableDefs(lTbl).Name, 16, 2) & "/" & Mid(dBase.TableDefs(lTbl).Name, 12, 2)
'Assign variable name with name of determined table
strTableName = (dBase.TableDefs(lTbl).Name)

End If
End If
Next lTbl

'Delete table strTableName
DoCmd.DeleteObject acTable, strTableName


'Release database object from memory
Set dBase = Nothing