Delete linked table if no records

[UHNSTrust]

I have just created an ODBC and linked over 1000 tables. However for ease of use I want to be able to delete all of the tables where there are no records (100s). Can someone advise me of how to do this with code in a module?

Thanks in advance
Jonathan

 

[PWise]

Dim mydb As Database

Dim tbdefs As TableDefs
Dim tbdef As TableDef
Set mydb = CurrentDb
Set tbdefs = mydb.TableDefs

For Each tbdef In tbdefs
    If DCount("*", tbdef.Name) = 0 Then
        mydb.Execute "drop table " & "tbdef.Name"
    End If
Next

 

[UHNSTrust]

Hi pwise,

Worked perfectly with a bit of playing for timeouts to the DCount.

Thanks
Jonathan