Deleting a Table

If you happen to have a table you don't need anymore in your database, you can remove it. Once again, don't remove a table if you have any doubt. It is better to have a useless table whose role is not clear than to delete a table in doubt. The reason is that, if you delete a table by mistake and then later on find out that you need it, you would have to recreate it completely. If you remove a table that is involved in an expression or a relationship, the expression or the relationship would be broken and this would result in unpredictable results. Fortunately, if you start deleting a table without using code, you would be warned. If the table is involved in a relationship, Microsoft Access would warn and may even prevent you from deleting it.

To visually remove a table, in the Navigation Pane, you can right-click the table and click Delete. As stated already, before the operation is carried out, you would be warned.

After reading the message, if you want to change your mind, you can click No. If you still want to delete the table, you can click Yes.

To programmatically delete a table, you can use the DoCmd object that is equipped with the DeleteObject() method. The syntax to use is:

DoCmd.DeleteObject acTable, [objectname]

The acTable argument indicates that you want to delete a table. If you select a table in the Database window when this method is called, you can omit the second argument and the selected table would be deleted. Otherwise, to specify the table you want to delete, pass its name as the second argument of the method.

Here is an example:

Private Sub cmdDeleteTable_Click()
    DoCmd.DeleteObject acTable, "Members"
End Sub

When this code executes, Microsoft Access would look for a table named Members. If it finds it, it would remove it from the database.

To delete a table in DAO, pass the name of the undesired table to the Detele() method of the TableDefs property of the database. Here is an example:

Private Sub cmdDeleteTable_Click()
    Dim curDatabase As DAO.Database

    Set curDatabase = CurrentDb

    curDatabase.TableDefs.Delete "Books"
End Sub