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!

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).

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.