i'm trying to drop importerror tables with code, but this isn't working

i am not sure if its to do with unusual characters in the import errors tables ( error tables generated by access when importing spreadsheets etc

any ideas why the code fails

error 3295 - syntax error

'drop errors tables first
tdfdrop = 0
For Each tdf In CurrentDb.TableDefs
If InStr(1, tdf.Name, "ImportErrors") > 0 Then
tdfdrop = tdfdrop + 1
sqlstrg = "drop table " & tdf.Name
DoCmd.RunSQL sqlstrg
End If
Next tdf
If tdfdrop > 1 Then Call MsgBox("Dropped: " & tdfdrop & " importerror tables")

Special characters?

sqlstrg = "drop table [" & tdf.Name & "]"

BTW - looping the collection and removing items from it, is a bit dangerous. Without going into detail, when there are more than one, you might miss some, due to reasons explained here http://www.mvps.org/access/forms/frm0040.htm (for forms, but the same issue), so you probably need to start at the end here, too, and iterate down to 0.

Could you use this method instead of the drop SQL statement?


'drop errors tables first
tdfdrop = 0
For Each tdf In CurrentDb.TableDefs
If InStr(1, tdf.Name, "ImportErrors") > 0 Then
currentdb.tabledefs.delete tdf.name
End If
Next tdf
If tdfdrop > 1 Then Call MsgBox("Dropped: " & tdfdrop & " importerror tables")

thanks chergh i'll try that

roy, i know i might be missing some important stuff, but there is some junk in the spreadsheet that i know wont get imported properly because the data format is incorrect, and i just wanted to delete them as otherwise the number of error tables just keeps on going

i thought it might be the $character in the table name affecting the delete, but this name is generated by Access

eg THUIMP$_ImportErrors

$ is a special character, and was what caused your syntax error. When doing SQL, you can use [brackets] as workaround, as I suggested.

Be careful with that looping!

sorry roy, didnt see the post - yesd that will fix it im sure