Copying, Renaming and Deleting Tables in Microsoft Access 2013

3 Basic Techniques Every Access User Should Know

Copy.jpg

Tables are the foundation for all of the data saved in Microsoft Access 2013. Like an Excel worksheet, tables can be large or small; contain names, numbers, and addresses; and they even include many of the same functions used by Microsoft Excel (except for calculations). The data are flat, but the more tables within a database, the more complex the data structures become.

Good database administrators curate their databases, in part, by copying, renaming and deleting tables.

Copying Tables in Microsoft Access

Database developers use the copy-tables functionality in Access to support three different use cases. One method simply copies an empty structure, without the data, useful for building a new table using an existing table's settings. Another method functions like a true "copy" -- it carries forward both structure and data. The third option concatenates similarly structured tables by inserting the records in one table into an existing table. All three options follow a similar procedure:

  1. Right-click the table name in the Navigation Pane, then select Copy. If the table will be copied into another database or project, switch to that database or project now.
  2. Right-click again in the Navigation Pane and select Paste.
  3. Name the table in the new window. Pick from one of three choices: Structure Only (copies only the structure, including conditions and primary keys), Structure and Data (copies the complete table) or Append Data to Existing Table (copies the data from one table to another one and requires both tables have the same fields).

    Renaming Tables in Microsoft Access

    Renaming a table follows from a single, straightforward process:

    1. Right-click the name of the table to be renamed and select Rename.
    2. Enter the desired name.
    3. Press Enter.

    You may need to inspect assets like queries, forms and other objects to ensure that the name change has correctly spread throughout the database.

    Access updates the database for you, but hard-coded queries, for example, might not automatically adjust to the new name.

    Deleting Tables in Microsoft Access

    Remove a table using one of two methods:

    • Right-click the name of the table and select Delete.
    • Select the name of the table to be deleted, then press the Delete key.

    To practice these actions without damaging existing tables, download some sample databases and experiment until you're comfortable manipulating the tables in a database that's important to you.

    Considerations

    Microsoft Access is not a forgiving environment for end-user mistakes. Consider making a copy of the whole database before you manipulate its table structure, so you can "restore" the original if you make an unrecoverable error.

    When you delete a table, the information associated with that table is removed from the database. Depending on the various table-level constraints you've set, you may inadvertently break other database objects (like forms, queries or reports) that depend on the table you've changed.