Delete Tables

[alex]

Delete Tables

In VBA, I’d like to delete a group of tables (both static and linked)
when the db closes…

I have this function:

Function DeleteTables() 'when mde closes, delete all tables with
suffix of 'Delete'

'If Right(CurrentDb.Name, 3) = "MDE" Then 'if current file is an mde
then

Dim db As Database
Dim tdf As TableDef
Dim lngCnt As Long

Set db = CurrentDb
For lngCnt = db.TableDefs.Count - 1 To 0 Step -1
Set tdf = db.TableDefs(lngCnt)
If Right(tdf.Name, 6) = "Delete" Then 'delete any table with
'Delete' as suffix
db.TableDefs.Delete tdf.Name
'Debug.Print (tdf.Name)
End If
Next lngCnt

Set tdf = Nothing
Set db = Nothing

'End If

End Function

I then call the function (Call DeleteTables) from the close event of a
form that remains open in my db (until the entire db is closed)

It appears that sometimes the code works, and sometimes it does not;
i.e., some tables are deleted while others are not. Also, sometimes
the tables appear to be present, but once you click the object, it
disappears. Can anyone suggest what I may be doing wrong?

Thanks,
alex

 

[Dorian]

You could just delete the contents and leave the tables there for next time.
They wont take up much space if empty (you'll need to set 'compact on close'
option).
CurrentProject.Connection.Execute "DELETE * FROM mytablename"
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[alex]

You could just delete the contents and leave the tables there for next time.
They wont take up much space if empty (you'll need to set 'compact on close'
option).
CurrentProject.Connection.Execute "DELETE * FROM mytablename"
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

















- Show quoted text -

Hi Dorian,
I think I found my problem; my code was trying to execute before all
of the forms had released the applicable tables as data sources.
alex