Counting Tables and Finding Create Date

[TJBernard]

I am trying to write code that will count how many tables exist in a database. If there are more than 5 tables, I would like to delete out the oldest table (according to the table create date property).

Here is the code I have tried...

Counter = 0
I = 999
Do While I > 5
I = db2.TableDefs.Count
If I > 5 Then
If Counter = 0 Then
stName = db2.TableDefs(Eval(IntX)).DateCreated
Counter = 1
Else
stName2 = db2.TableDefs(Eval(IntX)).DateCreated
If stName > stName2 Then
stName = stName2
End If
End If
End If
I = db2.TableDefs.Count
Loop

I get an error at stName = db2.TableDefs(Eval(IntX)).DateCreated that states "The expersion you returned contains invalid syntax". The InxX evalutes = Empty.

If anyone has tried this before let me know.

Thank you for your time,

T.J.

 

[Mile-O]

Don't understand why you'd want to delete tables - I don't even dare to ask.

Anyway, rather than write code you can do it with queries.

There is a hidden system table in Access called MSySObjects which stores the names of all high-level database objects in your database.

This table holds information such as object type (i.e table, form, etc), date created, date updated, and more.

 

[TJBernard]

Thank you very much for your help, using the System table allowed me to figure out a way to accomplish what the users requested. Thank you again.

T.J.