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".


"alex" wrote:

> 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
> .
>

On Jan 11, 5:29*pm, Dorian <Dor...@discussions.microsoft.com> wrote:
> 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" wrote:
> > 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
> > .- Hide quoted text -
>
> - 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