You can easily send Access records to a word processor (most notably, Microsoft Word) or a text editor. Sometimes you want the records in a file type such as RTF or DOC, to preserve table formatting. Other times you want the records to be in TXT files, which contain no formatting so they can be easily manipulated or re-exported to another program or computing environment.
Perhaps the most important way Access and Word are used together is in a mail merge to create form letters. (The term form letter is often used pejoratively, but here I simply mean letters with similar content sent to a number of recipients.) The increasingly automated mail-merge tool in Microsoft Word has made form letters easier than ever to create. But the process is still not completely intuitive, so I have provided an extended exercise on mail merges in the Case Example at the end of the chapter.
In this section, I focus on the various ways you can move Access records to word-processing and text files.
Cut and Paste
Sometimes you want to move just a few records from Access to Word. An easy way to do that is copy and paste. If you paste records into Word, you can do a plain-vanilla paste or use the Paste Special command to modify the formatting.
Let's try a plain-vanilla copy and paste first.
You could have accomplished the same task by using drag and drop. Open Word and then select the records in the tblCustomers table. Move the mouse directly below any column selector. When the mouse pointer becomes a white arrow (see Figure 13.2), drag the rows to the Word button on the Windows taskbar. Windows switches to Word, and you then can drop the selected records into the document. Alternatively, you can tile Access and Word windows, and then drag the records from Access to Word. Overall, I think copying and pasting is easier.
Figure 13.2. To drag and drop records, look for the white arrow pointer.
Now let's take a look at the choices offered by the Paste Special command in Word.
In Microsoft Office 2002 and 2003, you can also use the formatting commands on the Paste Options button, shown in Figure 13.4. (If the Paste Options icon is not displayed after you paste, in Microsoft Word, choose Tools, Options, Edit tab and select the Show Paste Options Buttons check box.) Unraveling their mysteries is beyond my scope, but you can experiment with them to see if they help you deal with Word formatting issues.
Figure 13.4. The Paste Options button offers additional choices for formatting pastes of Access records.
A quick and easy way to export records to Word is to use the Office Links command. The records are exported into a new RTF file, which maintains the formatting of the original file.
Let's try an example that exports an Access table.
By default, the file is stored in your My Documents folder.
The most robust tool for exporting Access data is, naturally enough, the Export command, available from the File menu in the Database window. When you issue the command, it initiates a wizard that takes you through the export process.
As with Office Links, you export an Access table to Word as an RTF file instead of as a DOC file (a Word document). But that's not really a problem: You can always save the RTF file as a DOC file. Notably, many word processors can handle documents with the more flexible RTF format.
New computer users quickly learn to distinguish between rich-text (RTF) files and plain-text (TXT) files. A rich-text file keeps the formatting included in an Access table; a plain-text file strips it away. Sometimes you might find this to be a distinction without a difference; you merely want to get Access values into some kind of word processor or text editor. Afterward, you can change the formatting to fit your needs.
But exporting to a rich-text file and exporting to a text file are two quite different things because, as you'll see, their purposes are different. Let's export a table, first as an RTF file and then as a TXT file.
Export a Table as an RTF file
To export a table as an RTF file, simply do the following:
The RTF file you created gives you the same results obtained using Office Links. When you use the Export command, however, the file doesn't open automatically.
You can use the same basic steps to export to an HTML file. In step 4, open the drop-down list and choose HTML Documents (*html;*htm,).
Export a Table as a TXT File
You usually export an Access table as a text file so that the records can then be easily imported into some other program or environment. The absence of formatting in text files eliminates one headache in importing because the content contains no formatting characters or instructions to deal with. On the other hand, the absence of formatting means you must use some other means to distinguish records and fields from another.
Distinguishing one record from another is handled easily enough: A different record appears on each line. To separate columns, you either include a delimiter or choose fixed-length columns. Delimited text files are more common (and I think easier to work with), so I focus on them.
What is a delimiter? It's an uncommon word for some common bit of punctuation, such as a comma or semicolon. In an Access table with fields for last name, first name, address, and so on, the punctuation mark separates last name from first name, first name from street address, and so on (see Figure 13.5).
Figure 13.5. In this delimited text file, the commas separate one value from another, and the text qualifier of quotation marks encloses each value.
What happens if that bit of punctuation, such as a comma, appears within the values themselves? In other words, how do you distinguish between a comma that separates street addresses from cities and a comma such as the one in the book title Captain Newman, MD? For the commas in values, you need a text qualifier (such as quotation marks) that maintains the value as a single text string (as in "Captain Newman, MD"). The text qualifier also keeps the entire value together, spaces and all.
My discussion makes delimited text files sound more complicated than they are. An example will you show much more readily how they work.