Delete table using Where clause?

[Martin]

I would like to write some code that will delete a table if the table name
contains the text "import errors". This is the code I have so far but it
does not work. I think the problem is with the Docmd.Delete line. Can
anyone help me fix this?

Public Function Delete()
Dim db As Database, FileName As String, tbl As TableDef

Set db = CurrentDb()
FileName = "ImportErrors"

For Each tbl In db.TableDefs
If tbl.Name Like FileName Then
MsgBox "Hello"
DoCmd.DeleteObject acTable, FileName
End If
Next

End Function

Thank you,

Martin

 

[John Spencer]

Public Function Delete()
Dim db As Database, FileName As String, tbl As TableDef

Set db = CurrentDb()
FileName = "ImportErrors"

For Each tbl In db.TableDefs
If Instr(1,tbl.Name,FileName) > 0 Then
MsgBox "Hello"
DoCmd.DeleteObject acTable, tbl.Name '<<<<
End If
Next

End Function

This function could fail to delete a table if two tables in the collection
were immediately adjacent and they both contained "Import Errors"

 

[Douglas J Steele]

This function could fail to delete a table if two tables in the collection

were immediately adjacent and they both contained "Import Errors"

To avoid that, you could use:

Public Function Delete()
Dim db As Database, FileName As String, tbl As TableDef
Dim intLoop As Integer

Set db = CurrentDb()
FileName = "ImportErrors"

For intLoop = (db.TableDefs.Count - 1) To 0 Step -1
Set tbl = db.TableDefs(intLoop)
If Instr(1,tbl.Name,FileName) > 0 Then
MsgBox "Hello"
DoCmd.DeleteObject acTable, tbl.Name
End If
Next intLoop

End Function

 

[John Spencer]

Yeah, I knew that. I was just too lazy to rewrite the entire thing.
Thanks for the help.

 

[Martin]

Thank you to you both, very helpful.

Martin

Yeah, I knew that. I was just too lazy to rewrite the entire thing.
Thanks for the help.