All the methods of importing and exporting data described in this chapter work well, but they aren’t the only ways to share information with other programs.
Sometimes the quickest and easiest way to get information into or out of a database is to just copy it and paste it where you want it. This technique works particularly well for getting data out of an Access table and into Word or Excel. If you paste into Word, the data becomes a Word table, complete with a header row containing the field captions as column headings. If you paste into Excel, the data is displayed in the normal row-and-column format on the worksheet.
Getting data into an Access table by using this technique is a little more complicated. The data you are pasting must meet all the criteria for entering it by hand (input mask, validation rules, field size, and so on), and you have to have the correct cells selected when you use the Paste command. If Access encounters a problem when you attempt to paste a group of records, it displays an error message and pastes the problem records into a Paste Errors table. You can then troubleshoot the problem in that table, fix whatever is wrong, and try copying and pasting again.
Tip
You can also copy an entire table from one Access database into another. Simply open both databases, copy the table from the source database to the Clipboard, and then paste it in the destination database. Access prompts you to give the new table a name, and you can choose to paste the table structure only, paste the structure and data, or append the data to an existing table.
Another quick way to share the information in an Access database with Word or Excel is through the OfficeLinks button on the toolbar. Clicking the down arrow to the right of this button displays a menu of three commands you can use to merge the data in the table with a Word mail merge document, to publish the table in a Word document, or to instantly export the table to an Excel worksheet.
In this exercise, you will experiment with copying records.
USE the GardenCo database in the practice file folder for this topic. This practice file is located in the My DocumentsMicrosoft PressAccess 2003 SBSImportingOfficeLink folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Access 2003 Step by Step.
OPEN the GardenCo database and acknowledge the safety warning, if necessary.
Open the Customers table.
Select about six records by pointing to the row selector of the first record you want to select (the pointer changes to a right arrow), holding down the left mouse button and dragging to the last record you want to select. Then on the toolbar, click the Copy button.
Start Excel, and with cell A1 of a new blank worksheet selected, click the Paste button on Excel’s toolbar.
If the Paste button is not visible, click the Toolbar Options button to display a palette of additional buttons, and then click the Paste button on the palette.
The records are copied in Excel, complete with the same column headings. (You will have to widen the columns to see all the data.)
Select a block of cells in the middle of the table by moving the pointer over the left edge of the first one you want to select, and when the pointer changes to a thick cross, dragging until you have selected all the desired cells.
Click the Copy button (the Clipboard task pane and the Office Assistant might appear), press
to move back to Excel, click a cell below the records you pasted previously, and then click the Paste button.Excel pastes in the new selection, again with column headings.
The data you copied is still on the Clipboard, so start Word, and on Word’s toolbar, click the Paste button.
The selection is pasted into a nicely formatted table with the title Customers, reflecting the name of the table from which this data came.
Quit Word and Excel without saving your changes.
Close the Clipboard task pane, and then close the Customers table.
CLOSE the GardenCo database and quit Access.