Access Hacks

Hack #14. Copy Data Between Tables Without an Append Query

Use Paste Append to easily copy data across tables.

Access users often use an Append query to append records from one table to another. In a production environment in which data is always being shuffled around, using Append queries can become tedious. Each time you design one, you have to match the fields of the destination table with the fields of the source table. This is easy when the fields have the same name, but it takes manual intervention when the field names differ.

If you have designed and saved an Append query definition, and the source and destination tables never change in name or structure, all is well for you. However, if even a single extra character is misplaced or is missing in the field names, the query either bombs or asks you to fill in the value for the unidentifiable field. Neither is an option you can live with.

How can you deal with these accidents waiting to happen? Fortunately, you can copy data between tables in another way: use Paste Append.

Appending Across Tables

A paste method unique to Access, Paste Append appends the contents of the clipboard to a database table. The data has to match the table in structure, but it does not need to have matching field names. That right there improves on the tedious data entry involved when using the query grid. To be fair to Append queries, they do have an advantage of their own: an Append query can use criteria to append filtered sets of records. Paste Append, ...