Copying Tables in Microsoft Access

Copying tables in Microsoft Access can be tricky, not because it is particularly difficult, but because there are a number of things you need to consider before you begin. Naturally, there are a lot of things you need to avoid as well, because having two copies of the same table will significantly skew your data. Last summer we went over some of the basics of copying, renaming, and deleting tables

, but those were just the basics of working with tables and only a bit of information about copying. In this article we will spend more time looking at what to consider, things you need to know before you get started, as well as how to copy tables within a single database and how to copy tables into a different database than the source.

Before you get started, you need to take the time to become familiar with all of the existing tables within the destination database so that you do not create duplicate records or tables. A simple query can show you what is already available. You also need to understand how the different tables are linked, especially if you plan to replace a table. This will ensure that you don’t create reporting issues or other problems that will skew your data.

What to Consider before Copying

Spend some time evaluating the need to copy an entire table within a single database. There are always risks when you do. If you intend to copy the table in its entirety, stop to ask

yourself why this is necessary. What do you hope to get from a new version of the same table? If you are only planning on adding a few new columns, it would be better to make a new table and link the two together for queries and reports. If you want to create a duplicate table for experiments (so that updates don’t interfere and that your changes aren’t permanent), make sure you record which is the duplicate and delete it when you are done. Otherwise, you could create duplicates, errors, and problems for queries and reports.   

If there are only certain physical or style components of the table that you want to copy into the same database, you can do that without worrying about duplicating the data on the table. Access makes it very easy to reuse a format you like by copying the table format.

If you are copying a table into another database (whether it is just the format or the entire table), you should still think about the process before beginning. What do you need from the current table: everything, a few columns and rows, or just the appearance? If you are moving data over too, make sure that data is not already available in the database. While it is far less likely when copying between databases, it is still possible that you end up duplicating information, or worse, overwriting data.

Different Table Elements to Copy

You don’t have to copy everything about a table. Access lets you choose what parts of the table you want to duplicate, which can help reduce the risk of creating data inconsistencies and problems.

  • Copy the complete table, including data
  • Copy the format, including structure and headings
  • Append a table to an existing table

The first two are relatively straightforward. The third option is for times when you have two tables that already have the same fields, and it allows you to merge the data. If the fields for both tables are not identical, the copy won’t work.

Copying Tables within a Single Database

Once you have thought about what you are about to do, it won’t take very long to complete the task.

  1. Right click on the table name in the Navigation Pane, then select Copy.
  2. Right click again in the Navigation Pane and select Paste.
  3. Name the table in the new window and decide which one of the three methods of copying you want to use:
  • Structure Only
  • Structure and Data
  • Append Data to Existing Table

If you are using the duplicate table for experiments, make sure that you give the table a name that will make it easy to identify as the experimental table. That way when it is time to delete the table you don’t accidently delete the wrong one.

Copying Tables into a Different Database

The steps are the same to copy a table into a different database, but they are repeated here to let you know when you need to switch databases (mostly as a reminder because it is too easy to run through the steps and realize too late that you did it in the wrong database).

  1. Right click on the table name in the Navigation Pane, then select Copy.
  2. Switch to the database where you want to copy the table.
  3. Right click again in the Navigation Pane and select Paste.
  4. Name the table in the new window and decide which one of the three methods of copying you want to use:
  • Structure Only
  • Structure and Data
  • Append Data to Existing Table

Replace Older Tables with the Copied Tables in Queries

If you are replacing tables with a new, copied table, you will need to make sure that you update your queries. This is extremely important as data entry will be going into the new tables, so failing to update queries will mean that your data will not be complete. Keep in mind that this is a little more advanced, so if you are not familiar with SQL, either ask someone else who is familiar with SQL to update it for you or ask that person to show you how to update it. The instructions are pretty simple, but if you make any mistakes and you don’t know SQL you can do some serious damage to your data.

  1. Open the query in Access.
  2. Copy the complete query (all lines).
  3. Paste the query into a program where you can edit it (it can be Word, Notepad, Notepad++, Eclipse, whatever you prefer).
  4. Run the Find function for all of the old table names and replace them with the new table name.
  5. Copy the complete code and paste it back into the Access query.
  6. Save the changes.

You will need to do this for every table that you wish to replace. This will possibly mean you will need to dedicate an hour or two to verifying that your queries and reports still work, but ultimately it is worth the effort if the new table is simpler and cleaner than the one it is replacing.

Final Cleanup

Once you have copied a table, whether into the same database or a new one, you will need to review a few functions to make sure things are working well. If you are replacing a table, you need to make sure to update queries and reports. If you aren’t sure what queries and reports use a particular table, you will need to dedicate time to reviewing all of the queries and reports in the database. This could be as simple as running the reports and queries (all of them if you have the time, otherwise prioritize them based on when they are run), or if you have the time and knowledge you can check out the code to find them.

You will need to coordinate with other divisions and groups that use the same databases to make sure that they are aware of the changes. The best time to do this is before you make the change, then let them know when it is done. They will have their own queries and reports that use the now obsolete tables, or they may really benefit from the new table, too. If you coordinate, you will reduce duplicate effort and problems.