Using the Clipboard to Import Data
If another Windows application generates the data you want to import, you can use the Windows Clipboard to transfer the data without creating a file. This technique is useful for making corrections to a single record with many fields or for appending new records to a table. This process requires a table with the proper field structure so that you can paste the data copied to the Clipboard into the other application. Pasting rows from an Excel spreadsheet, for example, requires a table with fields containing data types that correspond to those of each column that you copy to the Clipboard. Other Windows applications that can copy tabular data to the Clipboard use similar techniques.
Pasting New Records to a Table
To import data from the Clipboard and then append the data to an existing table or table structure, use the following procedure:
1. | Open the application you are using to copy the data to the Clipboard—in this case, Microsoft Excel. Then, open the file that contains the data. |
2. | Select the range to be appended to the table (see Figure 7.52). The Excel columns you select must start with the column that corresponds to the first field of your Access table. You do not, however, need to copy all the columns that correspond to fields in your table. Access supplies blank values in the columns of your appended records that are not included in your Excel range. Remember that if any of the columns you select contain formulas, the values must be frozen. Figure 7.52. Selecting cells in Excel to be appended to an Access table by copying to the Clipboard.
|
3. | To copy the selected cells to the Clipboard, press Ctrl+C or choose Edit, Copy. |
4. | Launch Access (if necessary) and open the table to which you are appending the records in Datasheet view. |
5. | Choose Edit, Paste Append in Access. If no errors occur during the pasting process, a message box reports how many new records you are about to add (see Figure 7.53). Click Yes. The records are appended to the bottom of your Access table (see Figure 7.54). Choose Records, Remove Filter/Sort to place the appended records in the correct order. |
Note
The fields you add to a table by using Paste Append must correspond (from left to right) to the fields in the table you are pasting the data into. You cannot, therefore, paste append records into a table that has an AutoNumber field, unless the records you are appending have already been assigned unique numeric values greater than the highest AutoNumber value currently existing in the table you are appending the records to. Otherwise, the fields of the pasted data don't match the left-to-right order of the fields in the table you're pasting into, and all the pasted records will end up in the Paste Errors table.
Figure 7.53. The message that appears when Access successfully appends records pasted from the Clipboard.
Figure 7.54. Records appended from the Clipboard to an Access table.
Troubleshooting Tip
Paste errors occur when I paste spreadsheet cells into a table.
Errors usually occur during the Paste/Append process for one of two reasons—the data types in the Excel cells don't match those in the corresponding fields of your Access table, or you attempted to paste records with data that duplicates information in key fields of the table. Both error types result in Access creating a Paste Errors table that contains information on the records with the errors. The Paste Errors table for field-type mismatches is similar in purpose and appearance to the Import Errors table described earlier.
Errors caused by duplicate primary-key violations result in the following series of cascading message boxes:
1. | Figure 7.55 shows the first message you receive that indicates a primary-key violation. Click OK. Figure 7.55. The message informing you that a pasted record duplicates a primary key value.
|
2. | A message box appears enabling you to suppress further error messages (see Figure 7.56). To cancel the append operation, click Cancel. Otherwise, click Yes to try to paste the remaining records without reporting further errors. If you want to see which errors occur as they are encountered, click No. Figure 7.56. Choosing whether to view error messages for each pasted record.
|
3. | A message box reports where the records that couldn't be pasted were placed (see Figure 7.57). Click OK. Figure 7.57. The message reporting that some records could not be pasted.
|
Figure 7.58 illustrates the result of this Pandora's box of messages. The set of four records copied to the Clipboard from Excel had all been previously pasted into the Access table; all four records duplicated key-field values in the existing table. Access pasted records without problems into the table and inserted the four records with duplicate key values into a Paste Errors table.
Figure 7.58. The Paste Errors table showing records that Access couldn't paste from the Clipboard.
If you specified one or more primary-key fields for your table, records that duplicate key field values are not appended. Tables without primary-key fields do not preclude adding duplicate records. The capability to index a non-key field with the condition "no duplicates allowed" is useful when you make new entries into a spreadsheet or word-processing document and want to append the new entries as records to a table. You preserve the uniqueness of the records by preventing the addition of records that duplicate records already in your table.
Note
When pasting or importing large numbers of records to a table, you must specify primary-key fields or a no-duplicates-allowed index for fields that later may become the primary key before you import any data. If you import the data before you create the primary-key fields index, you might find many duplicate records in the table. Then, when Access tries to create a no-duplicates index on the key fields, you see the message Can't have duplicate key. You must manually review all the added records for duplicates because Access doesn't create an Errors table in this case.
If, however, the data you are importing contains redundant information that you ultimately will remove to one or more secondary tables, you must import every record. Do not assign key fields or no-duplicates indexes in this case. The later section Deleting Redundant Fields in Imported Tables discusses the requirement to import every record when records contain one-to-many relations.
Tip
If you encounter the Can't have duplicate key error message when trying to establish a primary key, you can quickly find the duplicates by opening a new query and selecting the Find Duplicates Query Wizard. This wizard creates a query that you can use to find exactly where the duplicates are, without having to search record by record through your data.
Replacing Records by Pasting from the Clipboard
You can replace existing records in an Access table by pasting data in the Clipboard over the records. This process is useful when you are updating records with data from another Windows application. The data you paste must have a one-to-one column-to-field correspondence and must begin with the first field of the table. You need not, however, paste new data in all the fields. If no data is pasted in a field that is not included in the copied data's range, that field retains its original values.
To use data in the Clipboard to replace existing records in a table, follow this procedure:
1. | Select and copy the data from the other application that you want to paste to the Clipboard, using the method previously described for appending records from Clipboard data. If you choose more than one row of data in Excel, for example, the rows must have a one-to-one correspondence with the records to be replaced in the Access table. The one-to-one correspondence is likely to occur only if the table is indexed and the source data you are copying is sorted in the same order as the index. You can paste only contiguous rows from Excel. |
2. | Open your Access table. To select the records to be replaced by the Clipboard data, click the selection button for a single record or drag the mouse across the buttons for multiple records. If you are replacing multiple records, the number of records you select must be equal to or exceed the number of rows you copied to the Clipboard. If the number of records selected is less than the number of rows, the remaining rows are ignored. If you are replacing records in a table with key fields or a no-duplicates index, the columns of the replacement data corresponding to the key or indexed fields of the table must match exactly the key fields of the selected records. Otherwise, you see the key duplication error message sequence. |
3. | Choose Edit, Paste in Access. In this case, the contents of the existing records are overwritten rather than appended, and you see a dialog that tells you how many records will be replaced. |
When you use Paste for a replacement record rather than Paste Append for a new record with an identical key field value, Access suppresses the key violation error messages.
Note
If you do not select one or more records to be replaced by the Pasting operation, and the caret is located within a data cell of one of your records or a data cell is selected, Access attempts to paste all the data in the Clipboard to this one cell, rather than to the records. If the data doesn't create a mismatch type error or exceed 255 characters (if the caret is in a Text field), you do not receive a warning message. If you notice unexpected data values in the cell, Access pasted all the data to a single cell. Press Esc before selecting another record; Access restores the original value of the data cell.