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.

 

[Pat Hartman]

"It doesn't work" is insufficient information. have you stepped through the code so you know that the delete is being executed? Are you getting an error message? Make sure that you don't have warning messages turned off. And as Doc suggested, if you have relationships, you need to delete tables from the "bottom" up.

In the real world, it is very rare indeed to delete a table on the fly. You might want to explain why you are doing this. We might be able to offer an alternative.

Working with temp tables causes database bloat unless you do it correctly. For example, in apps where I need to import sets of data from other applications where for whatever reason, I can't link to the remote tables, I keep all the temp tables in a separate template database. I make the template with empty tables, compact it and save it. Then when I need to import new data, I copy the template to the active directory which overlays any existing version. The name of the template didn't change so my table links are still intact. Then I just run append queries to fill the template.