19.2. Using the Clipboard
Anyone who's spent much time using a Windows computer is familiar with the clipboarda behind-the-scenes container that temporarily stores information so you can transfer it from one program to another. Using the clipboard, you can copy a snippet of text in a Word document, and then paste it into a field in an Access table, or vice versa. That much is easy. But you probably don't realize that you can copy an entire table of information.
Tip: Almost all Windows programs respect the same shortcut keys for the clipboard. Use Ctrl+C to copy information, Ctrl+X to cut it (copy and delete it), and Ctrl+V to paste it.
Before you try this trick out, you need to understand two key facts about the clipboard:
The clipboard can store many different types of information . Most of the time, you're using it to copy plain text. However, depending on the program you're using, you could also copy shapes , pictures, tables, and more.
Some types of information can convert themselves to other types . If you copy a selection of cells in Excel, then you can paste it as a formatted table in a word processing program like Word or WordPerfect. Of, if you copy a diagram in Visio, then you can paste it as a picture in Paint. In both examples, you copy a specialized type of object (Excel cells or a Visio diagram) to the Windows clip-board. However, this object can downgrade itself when it needs to. You can paste a full-fledged copy of the object in the original program without losing anything, or you can paste and convert it to something simpler in a less powerful program.
This flexibility's the secret to transferring data to and from Access. The following sections explain how it works.
Note: The clipboard approach is simpler than the import and export features in Access. As a result, it's a faster choice (with fewer steps). Of course, it also gives you fewer choices and doesn't work with all programs.
19.2.1. Copying a Table from Access to Somewhere Else
Access lets you copy a selection of rows or an entire table to another program, without going through the hassle of the Export wizard. Access copies these rows to the clipboard as an intelligent object that can convert itself into a variety of software-friendly formats. You can paste them as Excel cells, HTML text (the formatting language of the Web), or RichText (a formatting standard created by Microsoft and supported by all major Word processors). Since HTML and Rich-Text are so widely supported, you'll almost never have a problem copying your rows into another program when you use this technique.
Here's how to try it out:
If you want to copy an entire table, then, in the navigation pane, select the table. If you want to copy only a few rows, then select them in the Datasheet view, as shown in Figure 19-1 .
You're not limited to copying tables. You can also copy a query's results. Just select the query in the navigation pane. You can't copy reports or forms, however.
When you copy rows or an entire table, Access takes your column hiding settings (Section 3.1.4) into account. If you've hidden a column so it doesn't appear in the datasheet (by selecting it, and then choosing Home Records More Hide Columns), Access doesnt copy it to the clipboard. This technique helps you leave out information you don't want to copy.
| || |
Figure 19-1. When selecting rows in the datasheet, click the gray margin just to the left of the first row you want to select. Then, drag down to select as many rows as you want. If you don't want to take your hand off the mouse, then you can copy these rows by holding down the Ctrl key, and right-clicking one of them. Then, from the pop-up menu, choose Copy.
Note: You can copy only a contiguous selection of rows, which is a fancy way of saying you can copy only rows that are right next to each other. If you have 10 rows in a table, then you can copy rows three to six, but you can't copy just the first and last rows. (Of course, you can use several smaller copy operations to get the stragglers.)
Hit Ctrl+C to copy your selection .
This action places the records on the Windows clipboard. You can now paste it inside Access or in another program.
Switch to the program where you want to paste your information .
If you're just trying this feature out for the first time, then take a whirl with Excel or Word (shown in Figure 19-2).
Hit Ctrl+V to paste your selection (see Figure 19-2) .
Access pastes the rows from your selection, complete with column headers. If you've applied formatting to the datasheet (Section 3.1), then most of that formatting comes along.
Depending on the program where you paste your records, you might see a smart tag icon appear at your newly pasted content's right-hand corner. In Office applications, you can use this smart tag to change options about how the data's pasted (for example, with or without formatting).
| || |
Figure 19-2. Using cut and paste, you can transform a database table into a table in a Word document (shown here). Once you've pasted the content, you may need to fiddle with column widths to make sure it all looks right.
Note: Copying text, numbers , and dates is easy. However, some data types don't make the transition as well. If you copy an attachment field, then the pasted content shows the number of attachment fields, but the files themselves are left out.
| TIMESAVING TIP |
Copying from One Database to Another
You can also use the copying trick described in Section 19.2.1 to copy data from one Access database to another Access database that's open in a separate window. However, it works only if you're copying a complete table (or other object), not a selection of rows.
To try it out, right-click the object you want in the navigation pane, and then choose Copy. Then, switch to the second Access database, right-click in the empty space in the navigation pane, and then choose Paste. Access asks you what you want to name the pasted table, and gives you three pasting options:
Structure creates the table structure, but leaves it empty.
Structure and Data creates an exact duplicate of the table, with all the data.
Append Data to Existing Table doesn't create a new tableinstead, it adds the data to the table that you specify. For this to work, the table must have the same structure as the one you've copied .
This trick also lets you create a duplicate copy of a table (or other object) in the same database.
19.2.2. Copying Cells from Excel into Access
You can copy information from Access into another program easily enough, but you probably don't expect to be able to do the reverse. After all, a database is a strict, rigorously structured collection of information. If you try to copy a table from a Word processing program, then you'll lack vital information, like the data types of each column. For that reason, Access doesn't allow it.
However, Access makes a special exception for everyone's favorite spreadsheet program, Excel. You can copy a selection of cells in Excel, and then paste them into Access to create a new table. This procedure works because Excel does distinguish between different types of data (although it isn't nearly as picky as Access). For example, Excel treats numbers, dates, text, and TRUE/FALSE values differently.
Here's how to use this feature:
In Excel, select the cells you want to copy .
If your spreadsheet includes column titles, then include those headers in the selection. Access can use the titles as field names .
Note: It doesn't matter what version of Excel you havethis trick works with them all.
Hit Ctrl+C to copy your selection .
Click anywhere in the navigation pane, and then press Ctrl+V .
Access notices that you're trying to paste a group of Excel cells, and it tries to transform them into a table. First, it asks if the first row in your selection includes column titles.
If you selected the column titles in step 1, then choose Yes. Otherwise, choose No .
If you choose Yes, then Access doesn't need to create random field namesinstead, it can use your headers.
Access creates a new table to deal with the new data. This table's named after the Excel sheet. If your sheet's named Sheet1 (as so many are in Excel), you now have a Sheet1 table.
Once Access finishes the paste, it shows a confirmation message to let you know everything's finished successfully.
Click OK .
Now you can refine your table to make sure the data types and field names are exactly what you want.