What is the difference between DoCmd.DeleteObject acTable Vs. DROP TABLE

Question

Details:

I have an MS-Access Database procedure where I create tables locally in the database. However, I want to ensure that the tables I create are tested for and if the test fails I need to delete/drop the other tables that have been created. Basically a rollback procedure I guess.

Question:

I came across the two methods to delete tables but cannot figure out if one has more pro than cons etc...

Can someone tell me what the difference is?

Many Thanks!

Lodicule

Answer 1

DoCmd.DeleteObject acTable, "aaaTest"

...and...

Dim cdb As DAO.Database
Set cdb = CurrentDb
cdb.Execute "DROP TABLE [aaaTest]", dbFailOnError

...and...

Dim cdb As DAO.Database
Set cdb = CurrentDb
cdb.TableDefs.Delete "aaaTest"

...are all just different ways of accomplishing the same thing. They delete the local TableDef object with that name (either an actual local table, or a table link).

Colloquialism

I see so we are really just saying that there isnt an actual difference. That's fine. I just wanted to make sure I wasnt missing out on some big difference! Thanks! – Lodicule

We had been trying to delete an ImportError table that had apostrophes in the title and the second method above (DROP TABLE) did not work. But the first method above (DeleteObject) worked just fine. Thanks! – Intransigent

Answer 2

@gordthompson did a concise job of explaining three ways to delete tables. In testing his methods I noticed one difference. I'm working offline and have linked tables in a back-end that point to Access tables on the client network. When I try to delete the linked tables using the Access UI it can take over 30 seconds for each table. It's annoying.

Based on Gord's examples I have discovered that...

DoCmd.DeleteObject acTable, "aaaTest" ' is very slow, just like the Access UI.

CurrentDb.Execute "DROP TABLE [aaaTest]", dbFailOnError ' is immediate.

CurrentDb.TableDefs.Delete "[aaaTest]" ' is also immediate

If you have a table name that contains a dash or other special character, wrapping the name in [square brackets] should solve the problem.

Anthocyanin

Answer 3

I just found out that DropTable doesn't like table names with a dash in them. I had quite a few, so I used DoCmd.Rename to rename the table right before I used droptable to erase them. This was the easiest fix for me because I had 15 table with dashes in them and 60 all together that I delete upon exit to clean up temporary files.

Thaddeus