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. Information that
you paste into a Word document becomes a Word table, complete with a
heading row containing the field captions as column headings.
Information that you paste into an Excel worksheet appears in the
normal row-and-column format.
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 must have the correct table 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
To paste an entire table from one Access database to another,
open both databases, copy the table from the source database to the
Clipboard, and then paste it in the destination database. You can
paste the table data and/or table structure as a new table or append
the data to an existing table.
In this exercise, you’ll copy and paste records between an
Access database table, an Excel worksheet, and a Word document.
Set Up
You need the GardenCompany10 database you worked with in the
preceding exercise and the Shippers workbook located in the Example10 practice file folder to complete this exercise. Open the
GardenCompany10 database, open the Customers table in Datasheet
view, and then follow the steps.
-
Point to the row selector of the first record in the table,
and when the pointer changes to a right arrow, hold down the mouse
button and drag through six records.
-
On the Home tab, in the
Clipboard group, click the
Copy button.
Keyboard Shortcut
Press Ctrl+C to copy a selection to the Clipboard.
-
Start Excel.
-
With cell A1
selected in the blank worksheet, on the Excel Home tab, in the Clipboard group, click the Paste button.
Keyboard Shortcut
Press Ctrl+V to paste a cut or copied item from the
Clipboard.
Excel pastes the records into the worksheet.
-
On the Windows Taskbar, click the Access button to switch back to
Access.
Now let’s copy and paste only a few fields of a few
records.
-
In the FirstName field,
point to the left border of the value Ted, and when the pointer changes to a
thick cross, hold down the mouse button, and drag down and to the
right until the FirstName
through Region fields are
selected for six records.
-
In the Clipboard group,
click the Copy button.
-
Switch back to Excel, click cell A9, and then in the Excel Clipboard group, click the Paste button.
Excel pastes in the new selection, again with column
headings. The copied data remains on the Office Clipboard
-
Start Word. Then on the
Home tab, in the Clipboard group, click the Paste button.
Word pastes the selection as a nicely formatted
table.
-
Exit Word and Excel, without saving your changes. Then close
the Customers table.
Now let’s copy the data from an Excel worksheet and paste it
into a new table in the current database.
-
Start Windows Explorer, navigate to your Exmaple10 practice file folder, and
double-click the Shippers Excel
workbook.
Excel starts and opens the Shippers workbook.
-
Select cells A1:H6, and
on the Home tab, in the
Clipboard group, click the
Copy button.
-
Switch back to Access, right-click in the Tables area of the Navigation pane, and click Paste.
Access asks whether the first row of data contains column
headings.
-
Click Yes.
-
When Access notifies you that the import process was
successful, click OK.
The new Shippers table is added to the Tables area of the
Navigation pane.
-
Double-click the Shippers
table to open it, and verify that all records were successfully
copied.
Clean Up
Exit Excel. Then close the Shippers table and the
GardenCompany10 database.
