Delete a table if it exists (1 Viewer)

mansied

Member
Local time
Yesterday, 21:27
Joined
Oct 15, 2020
Messages
44
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

Wino Moderator
Staff member
Local time
Yesterday, 18:27
Joined
Aug 30, 2003
Messages
35,019
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

Super Moderator
Staff member
Local time
Today, 02:27
Joined
Sep 12, 2006
Messages
14,413
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.

Code:
    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

Member
Local time
Yesterday, 21:27
Joined
Oct 15, 2020
Messages
44
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.

Code:
    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

Wino Moderator
Staff member
Local time
Yesterday, 18:27
Joined
Aug 30, 2003
Messages
35,019
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:

PHP:
 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

Immoderate Moderator
Staff member
Local time
Yesterday, 20:27
Joined
Feb 28, 2001
Messages
19,384
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

error reading drive A:
Local time
Today, 09:27
Joined
May 7, 2009
Messages
12,011
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

Super Moderator
Staff member
Local time
Today, 02:27
Joined
Sep 12, 2006
Messages
14,413
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

Super Moderator
Staff member
Local time
Yesterday, 21:27
Joined
Feb 19, 2002
Messages
31,482
"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.
 

Users who are viewing this thread

Top Bottom