Deleting tables via a macro

[PeteCan]

I need to delete close to 6000 tables from an access database and obviously don't want to do it manually.

Can anyone suggest any macro code for doing this?

 

[jebry]

Hi!

You can loop through the table defs in reverse as long as you can identify the tables you want to delete:

Dim tbl As Table
Dim dbs As DAO.Database
Dim intCount As Integer

Set dbs = CurrentDb
For intCount = dbs.Tabledefs.Count - 1 To 0 Step -1
Set tbl = dbs.TableDefs(intCount)
If tbl.Name IN (Deletable Tables) Then
dbs.TableDefs.Delete tbl.Name
End If
Set tbl = Nothing
Next intCount

hth


Jeff Bridgham
bridgham@purdue.edu

 

[PeteCan]

I'm a little confused where I need to enter the table numbers etc.
My tables are called ILCommentx (where x=1 to 6000+)

 

[jebry]

Hi!

That actually makes it easier:

Dim intCount As Integer

For intCount = 1 to 6000
CurrentDb.TableDefs.Delete "ILComment" & Format(inCount)
Next intCount

hth


Jeff Bridgham
bridgham@purdue.edu

 

[jebry]

Hi!

Quick caution, back this up before deleting these tables!!

hth


Jeff Bridgham
bridgham@purdue.edu