Delete all tables

[peljo]

I have a function that deletes all the tables in the database.However i do not want to delete the tables TblSpecial1 and TblSpecial2.How can i rewrite the code ?
Public Function DeleteTables()
Dim dbs As DAO.Database
Dim i As Integer
Set dbs = CurrentDb
' Loop backwards through relations
For i = dbs.Relations.Count - 1 To 0 Step -1
dbs.Relations.Delete dbs.Relations(i).Name
Next i
' Loop backwards through tabledefs
For i = dbs.TableDefs.Count - 1 To 0 Step -1
If Left(dbs.TableDefs(i).Name, 4) <> "MSys" Then
dbs.TableDefs.Delete dbs.TableDefs(i).Name
End If
Next i
Set dbs = Nothing
End Function

 

[MarkK]

With CurrentDb.TableDefs(i)
  If Left(.Name, 4) <> "MSys" _
  AND .Name <> "tblSpecial1" _
  AND .Name <> "tblSpecial2" Then
    ...
  End If
End With

 

[peljo]

With CurrentDb.TableDefs(i)
  If Left(.Name, 4) <> "MSys" _
  AND .Name <> "tblSpecial1" _
  AND .Name <> "tblSpecial2" Then
    ...
  End If
End With

Thank you for the reply.I get the message "unqualified or invalid reference" :
Dim dbs As DAO.Database
Dim i As Integer
Set dbs = CurrentDb
' Loop backwards through relations
For i = dbs.Relations.Count - 1 To 0 Step -1
dbs.Relations.Delete dbs.Relations(i).Name
Next i
' Loop backwards through tabledefs
For i = dbs.TableDefs.Count - 1 To 0 Step -1
If Left(dbs.TableDefs(i).Name, 4) <> "MSys" And .Name <> "tblSpecial1" And .Name <> "tblSpecial2" Then
dbs.TableDefs.Delete dbs.TableDefs(i).Name
End If
Next i
Set dbs = Nothing

 

[pbaldy]

If you're going to omit the With statement that lagbolt used, you'll need to use the full reference, rather than just

.Name

 

[peljo]

If you're going to omit the With statement that lagbolt used, you'll need to use the full reference, rather than just

.Name

But why do i then get the mesage invalid object ?

Public Function DeleteTables()
Dim dbs As DAO.Database
Dim i As Integer
Set dbs = CurrentDb
' Loop backwards through relations
For i = dbs.Relations.Count - 1 To 0 Step -1
dbs.Relations.Delete dbs.Relations(i).Name
Next i
' Loop backwards through tabledefs
For i = dbs.TableDefs.Count - 1 To 0 Step -1
With CurrentDb.TableDefs(i)
If Left(dbs.TableDefs(i).Name, 4) <> "MSys" And .Name <> "tblSpecial1 And .Name <> tblSpecial2" Then
dbs.TableDefs.Delete dbs.TableDefs(i).Name
End If
End With
Next i
Set dbs = Nothing
End Function

 

[MarkK]

Try this...

Public Function DeleteTables()
  Dim dbs As DAO.Database
  Dim i As Integer
  Set dbs = CurrentDb
  ' Loop backwards through relations
  For i = dbs.Relations.Count - 1 To 0 Step -1
    dbs.Relations.Delete dbs.Relations(i).Name
  Next i
  ' Loop backwards through tabledefs
  For i = dbs.TableDefs.Count - 1 To 0 Step -1
    If Left(dbs.TableDefs(i).Name, 4) <> "MSys" _
    And dbs.TableDefs(i).Name <> "tblSpecial1" _
    And dbs.TableDefs(i).Name <> "tblSpecial2" Then
      dbs.TableDefs.Delete dbs.TableDefs(i).Name
    End If
  Next i
  Set dbs = Nothing
End Function