Friday 9 October 2015

The fastest way to delete a table programmatically

Ever needed to delete a table in code? Maybe you created a temporary table and now you need to get rid of it in the neatest way possible. The most obvious way is to use DoCmd.DeleteObject, thus:

DoCmd.DeleteObject acTable, "tblTemp" 'tblTemp is the name of the table to be deleted

This does the job, but Access 2000 and above requires you to have the database open exclusively to do this. Why not make use of the fact that Access has a database engine (no kidding!) and try this slightly faster method:

DBEngine(0)(0).Execute ("DROP TABLE tblTemp;") 'tblTemp is the name of the table to be deleted

In either case you can run into problems trying to delete a table that is the parent in a join to another table, as this could lead to a referential integrity violation. Presumably you'll want to delete the related table too, so rather than worry about having to delete joins first, just delete the child table before the parent. There are no problems using the second method shown above to do this.

No comments:

Post a Comment