Drop Table for each loop

[gammaman]

I am trying to drop multiple tables using a loop. I am getting a syntax error.

Private Sub cleanUp()
  Dim t As TableDef
  For Each t In CurrentDb.TableDefs
    If t.Name <> "VENDOR_TBL" Then
      DoCmd.RunSQL ("DROP TABLE" & t.Name & ";")
    End If
    
  Next
  MsgBox ("DONE")
End Sub

[JoeM]

You need a space after "DROP TABLE", otherwise the table name runs into the word TABLE, i.e.

      DoCmd.RunSQL ("DROP TABLE " & t.Name & ";")

[gammaman]

Thank You. Missed that. However, I am still getting an error. Could it be because access is attempting to drop a tmp table? See attached.

[orange]

Do you know which tables you want to drop?
What about those beginning MSys*??

[JoeM]

Possibly. It might not like the "~" symbol in the name.
Do all the tables you want to delete have anything in common, i.e. a prefix or something, that you can limit your delete statements to ones meeting those conditions?

Here is something that I have that may be helpful. I have some automated processes that import files, and sometimes there are import errors that I do not care about. So in order to keep my database from filling up with a bunch of Import Error tables, I run this code to keep it lean:

'   Delete any old import errors tables
    For Each tblDef In CurrentDb.TableDefs
        If InStr(1, tblDef.Name, "ImportErrors") > 0 Then
            DoCmd.SelectObject acTable, tblDef.Name, True
            DoCmd.DeleteObject acTable, tblDef.Name
        End If
    Next tblDef

This at least shows you some other syntax for deleting tables.

[gammaman]

Want to drop all user defined tables except for what is specified in my code. So all access system and tmp tables should not be deleted. Is there a better way?

Private Sub cleanUp()
  Dim t As TableDef
  For Each t In CurrentDb.TableDefs
    If t.Name <> "VENDOR_TBL" And t.Name Like "~*" = False And t.Name Like "MSy*" = False Then
      MsgBox "DROP TABLE " & t.Name & ";"
      DoCmd.RunSQL ("DROP TABLE " & t.Name)
    End If
    
  Next
  MsgBox ("DONE")
End Sub

[HiTechCoach]

How are you handling the case where 'user defined tables have relationships?

Assuming this is a split database. It may be easier to export the tables you do want to keep to another database and rename the back I would create a new separate front end that only does this task.

If yu do go with thw delete method, I highly recommend that you run a compact a bwefeore the data gets used .

[gammaman]

I am doing both actually. Before deleting I am exporting them. However, I am still getting an error in the drop statement.

Private Sub cleanUp()
  Dim t As TableDef
  Dim SQL As String
  
  
  
 
  For Each t In CurrentDb.TableDefs
  SQL = "DROP TABLE " & t.Name
   'MsgBox SQL
    If t.Name <> "VENDOR_TBL" And t.Name Like "~*" = False And t.Name Like "MSy*" = False And t.Name Like "*$*" = False Then
      MsgBox SQL
    db.Execute SQL
    End If
    
  Next
  MsgBox ("DONE")
End Sub

[JoeM]

Have you tried the code that I provided, that deletes the tables using a different command?