Syntax Error In Drop Table Code. 2

[lars7]

Hi Guys,
I would like to delete an import error report table after my spreadsheet is imported but I am getting an syntax error message, can anyone see why?



If TableExists("'Progress to Assim$'_ImportErrors") Then

DoCmd.RunSQL "DROP TABLE 'Progress to Assim$'_ImportErrors; "

Else

'Do Nothing

End If


thanks

tom.

 

[Golom]

It's the single quotes. You need to rename the table to get rid of the error

If TableExists("'Progress to Assim$'_ImportErrors") Then
    Dim tdf As DAO.TableDef
    Set tdf = Currentdb.Tabledefs("'Progress to Assim$'_ImportErrors") 
    tdf.Name = "TableToDelete"
    Set tdf = Nothing
    DoCmd.RunSQL "DROP TABLE TableToDelete; "
End If

 

[lars7]

Hi Golom,
I got an "Object invalid or no longer set" error message with the code but thanks for the explanation as I used the module below and it works fine.



Function RenameToDelete()
Dim db As DAO.Database


Set db = CurrentDb

Debug.Print

If TableExists("'Progress to Assim$'_ImportErrors") Then

db.TableDefs("'Progress to Assim$'_ImportErrors").Name = "TableToDelete"

DoCmd.RunSQL "DROP TABLE TableToDelete; "

End If


Set db = Nothing

End Function

 

[PHV]

Why not simply this ?

If TableExists("'Progress to Assim$'_ImportErrors") Then
  db.TableDefs.Delete "'Progress to Assim$'_ImportErrors"
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lars7]

Thanks PH,

that's much simpler