delete all tables of an access DB

[Cesar Romani]

I'm trying to delete all the tables of an access database and have written
the following subroutine but it deletes only half the number of tables. How
I should modify the subroutine in order to delete all the tables?

--------------------------------------------
Sub deleteTables()
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table

cat.ActiveConnection = CurrentProject.Connection

For Each tbl In cat.Tables
If tbl.Type = "TABLE" Then
cat.Tables.Delete tbl.Name
End If
Next

Set tbl = Nothing: Set cat = Nothing
End Sub

 

[Stefan Hoffmann]

hi Cesar,

I'm trying to delete all the tables of an access database and have written
the following subroutine but it deletes only half the number of tables. How
I should modify the subroutine in order to delete all the tables?

Dim cat As New ADOX.Catalog

Don't use the New. It's not necessary.

For Each tbl In cat.Tables
Next

Use

For Count = cat.Tables.Count - 1 to 0 Step -1
Next Count

instead For-Each.


mfG
--> stefan <--

 

[Cesar Romani]

hi Cesar,



Don't use the New. It's not necessary.

Use

For Count = cat.Tables.Count - 1 to 0 Step -1
Next Count

instead For-Each.


mfG
--> stefan <--

Thanks Stefan,
it works with the For Count - Next Count but why doesn't it work with the
For-Each?
If I use the following code in DAO it works with the For-Each

---------------------------------------------------
Sub removetables()
Dim db As Database
Dim tdf As TableDef
Dim tdfs As TableDefs
Set db = CurrentDb()
Set tdfs = db.TableDefs
For Each tdf In tdfs
If Left(tdf.Name, 4) <> "Msys" Then
DoCmd.DeleteObject acTable, tdf.Name
End If
Next tdf
tdfs.Refresh
db.Close
Set tdfs = Nothing
Set db = Nothing
End Sub

 

[Stefan Hoffmann]

hi Cesar,

it works with the For Count - Next Count but why doesn't it work with the
For-Each?

The core problem is easily demonstrated:

Use

For Count = 0 To cat.Tables.Count - 1
'Delete element
Next Count

After deleting a element in a collection, it is reorganized. This may
result in a wrong index/count as the upper loop shows.
Depending on the implementation of the enumerator for a For-Each this
may also bail out.

You may also use to delete:

Do While cat.Tables.Count > 0
'Delete element at index 0
Loop

mfG
--> stefan <--

 

[Robert Morley]

Or loop through it backwards with...

For Count = cat.Tables.Count - 1 To 0 Step -1
'Delete Element
Next


Rob

 

[Robert Morley]

Woops...wasn't paying attention to the chain. Sorry for duplicating what
someone else already said!


Rob

 

[Stefan Hoffmann [MVP]]

hi Robert,

Woops...wasn't paying attention to the chain. Sorry for duplicating what
someone else already said!

Yeah, that was my post


mfG
--> stefan <--

 

[Aaron Kempf]

I've never ever ever ever ever ever ever had a need for ADOX

 

[Aaron Kempf]

Dim tbl As AccessObject

For each tbl in CurrentData.AllTables
Docmd.DeleteObject yadda, yadda, yadd

Next tbl