Delete a table if it exists

[mansied]

Hello
I have the below code to delete a table if it exists, but it doesn't work could you help me where is the issue?

If Not IsNull(DLookup("Name", "MSysObjects", "Name='JIC Global'")) Then
DoCmd.DeleteObject acTable, "JIC Global"

Thank you

 

[pbaldy]

I would just delete it and handle the error if it doesn't exist:

On Error Resume Next
DoCmd.DeleteObject acTable, "JIC Global"
On Error GoTo ErrorHandler '(your normal error handler)

 

[gemma-the-husky]

Try the follwoing.
For what its worth I have never used a msys table for anything.
I prefer DAO to deal with tables, fields and other DAO objects.

    Sub delete()
    on error resume next    'it doesn't matter if there is no table to delete
    CurrentDb.TableDefs.delete "tablename"
    CurrentDb.TableDefs.Refresh
    MsgBox "table deleted"

End Sub

 

[mansied]

Thank you, I added error handling but still doesn't delete the table !!!

Try the follwoing.
For what its worth I have never used a msys table for anything.
I prefer DAO to deal with tables, fields and other DAO objects.

    Sub delete()
    on error resume next    'it doesn't matter if there is no table to delete
    CurrentDb.TableDefs.delete "tablename"
    CurrentDb.TableDefs.Refresh
    MsgBox "table deleted"

End Sub

I have 3 tables to be deleted ..with a specific name ..

If (Not IsNull(DLookup("Name", "MSysObjects", "Name=JIC Global"))) Then
DoCmd.DeleteObject acTable, "JIC Global"
On Error GoTo ErrorHandler
ErrorHandler:
MsgBox "Error !"

 

[pbaldy]

Dave may be asleep. Did you call the sub? Given your requirements, I might put a public function in a standard module, building on Dave's code:

 Public Function DeleteTable(strTableName As String)
    on error resume next    'it doesn't matter if there is no table to delete
    CurrentDb.TableDefs.delete strTableName
    CurrentDb.TableDefs.Refresh
End Function

When you need to delete a table you just call it:

DeleteTable "JIC Global"

You can put a message box in there if you want.

 

[The_Doc_Man]

The next question is, when it refuses to delete the table, are there non-empty child tables with established relationships? If so, I don't think you can delete the table (yet). You have other things to do first. If the table is stand-alone, though, you should be able to do that unless there is a permissions issue such that the DB was opened read-only.

 

[arnelgp]

you can also use SQL to delete the table:

If Not IsNull(DLookup("Name", "MSysObjects", "Name='JIC Global'")) Then
Currentdb.Execute "Drop Table [JIC Global];"
End If

 

[gemma-the-husky]

I didn't assume you would get an error, or the error would just be "no table of that name found"
If the table is in use you won't be able to delete it, and you will want to see exactly what the error message, so you need a real error handler. Maybe there is a space in the table name, so the name wasn't correct. The code isn't case sensitive. Note that if the table is a linked table this code - or pretty well any code - will either not delete it, or more likely, delete the link, but not delete the table in the back end.