Delete Tables

  • Thread starter Thread starter alex
  • Start date Start date
A

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
 
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".
 
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 -
Click to expand...

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
 
Back
Top