Copy and delete tables

[Moxy1]

I'm using Access 2000. I am making a copy of a table before making modifications to it with;

dim varDate as Date
varDate = Now()

docmd.copyobject "", "table" & varDate, acTable, "table"

What I would like to do is have a second part of code that will look at the varDate part of the table name and delete the object if it is 3 months old. Can this be done?

Thanks,
Moxy

 

[RichD]

Moxy,

I would use tableDefs, something like this:
lets assume your tablename is tblX01012004 where 01012004 represents the date

Public Sub DeleteOldTable ()
dim db as databse, Mytable as tabledef, tableName as string

set db = currentDB

For Each Mytable In db.TableDefs
tablename = mytable.Name
'the next line formats the date protion of the tablename and if it is more than 92 days before todays date deletes the table from the tabledefs collection
if format(right(tablename,8),&quot;mmddyyyy&quot < (date()- 92) Then
db.TableDefs.Delete tablename
end if
Next Mytable

Set db = Nothing

End Sub

I have not tested the above so it may be a bit buggy, please come back with any problems.

HTH

Rich


Bespoke and off-the-shelf Access solutions

http://www.blsoftware.co.uk