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.

2.3.1. 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, on the other hand, just appends everything. However, if the need to apply criteria isn't an issue, Paste Append has the advantage.

Figure 2-5 shows two tables: one contains existing customers, and the other contains a list of leads that have to be added to the list of existing customers. The records in the tblLeads table need to be added to the tblCustomers table. The field names aren't the same, although the field types and purposes match.

The simplest thing to do is to select all the records in tblLeads (Ctrl-A). Copy the records, go to the tblCustomers table, and use the Edit Paste Append menu to enable Paste Append, as shown in Figure 2-6.

Figure 2-5. Appending similar data from one table to another

Figure 2-6. Using Paste Append

Note that the records are appended without concern for the field names. However, an alternative method is available that is easier still: the table with the records to be appended (tblLeads in this example) doesn't even have to be open! Just select and copy the table while it is closed. Do this directly in the database window. Then, open the table that receives the records (tblCustomers in this example), and use the Paste Append menu item as before.

This method has an issue, though. When the field names are the same, the method works like a charm. However, if at least one field name is different, the method still works, but the field names of the table being copied from might be inserted as a record!

Of course, by nature, field names are text-based, so if the table receiving the append contains fields that aren't text-based, Paste Append won't paste the field names. You might get an error about the datatype being wrong, but this is OK. Strange but true!

Finally, even when you know a record will appear that contains field names instead of data, the Paste Append method still might be preferable to creating an Append query because it is usually much easier to delete a single record from a table than it is to design a new query from scratch.

2.3.2. Appending Across Databases

The techniques in this hack work not just within a single database application, but also across databases. In other words, you can select and copy records from a table in one database and then append these records to a table in a different database. However, both databases must be open to make this possible.

Figure 2-7 shows two databases, side by side on the desktop.

Figure 2-7. Appending data across databases

The tblLeads table, in the database on the left, is simply being dragged over to the open tblCustomers table in the database on the right. The tblLeads table is effectively being copied, not moved; the original stays in the first database. Letting go of the mouse button completes the append operation.