Almost effortless collaboration is a key selling point for Microsoft Office: If the data already exists in one format, why re-enter it in another. Usually Office delivers nicely on its promise. Word is troublesome, however, because it doesn't offer an import feature. Using the File Open feature, you can specify a number of file formats, but Microsoft Access is missing. When you think about it, that makes good sense — the two formats just aren't compatible. However, the Access table construct is similar enough to a Word table, that you might expect Word to handle a simpler transfer from table to Word document. Unfortunately, there's no intuitive way to get Access data into a Word table, but that doesn't mean you can't do it — you just need to know where to look for help.
This blog post is also available in PDF form in a TechRepublic download.
Use the Clipboard for a quick fix
You can copy data from an Access table or query into a Word document using the Clipboard. When taking this shortcut, you have three choices:
- Use the resulting table, although you might find you don't like it.
- Convert the resulting table into text and then insert a Word table.
- Convert the resulting table into text and leave it that way.
It's a waste of time to try to find a way to copy Access data into a Word document without dragging along Access' record and column format. There's no silver bullet for the process — but you can reduce the amount of effort by copying only the data you actually need. In other words, you might want to work from a query instead of a table. That won't always be the case, but seldom will you want to copy an entire table.
The Clipboard method has a few pros and cons. On the downside, you must have permission to open the database and view the data and you must know how to create a query or at least know how to view data in a table. If you're familiar with Access, you can limit the data instead of copying the entire table to Word and dealing with extraneous data there.Figure A shows a query in Datasheet view that returns only three columns of data from the Orders table in Northwind (the sample database that comes with Access). You could also limit the records and sort the data using the query, before it ever reaches Word.
You can copy data from an Access table or query, but a query makes it easy to limit the data you copy.
To copy data from Access to a Word table, using the Clipboard do the following:
- In Access, select all the data in the resulting query (or table).
- Select all the records by clicking the intersecting header cell at the top-left corner (circled in Figure A), or by pressing [Ctrl]+[A].
- Press [Ctrl]+[C] to copy the selected data to the Clipboard.
- Open a new Word document.
- Choose Paste Special from the Edit menu. In Word 2007, choose Paste Special from the Paste command's dropdown list. You'll find the Paste command in the Clipboard group on the Home tab.
- In the resulting Paste Special dialog box, click Unformatted Text, as shown in Figure B, and then click OK. Figure C shows the Access data in columnar format.
- Now that you have the data in Word, you can quickly insert a Word table. In the Word document, select all the data by pressing [Ctrl]+[A].
- Choose Convert from the Table menu and then select Text to Table from the resulting submenu. In Word 2007, you'll find this command in the Table command's dropdown list. The Table command is in the Tables group on the Insert tab. Word does a good job of anticipating the table's requirements, as you can see in Figure D.
- Click OK to insert the table shown in Figure E.
Paste Special offers a new of ways to paste data from the Clipboard into a Word document.
Paste just the data in columnar format.
Use Word's Table commands.
Once you have the data in a Word table, you can format the table and sort the data (using the Table menu's Sort command).Figure C shows the results of copying just the data, although Word retains a columnar format. If you press [Ctrl]+[V] to paste the data into Word instead of using Paste Special from the Edit menu, Word copies the data and a table, of sorts, as shown in Figure F. You can reformat the table or convert it to just text. If you can live with this table, [Ctrl]+[V] saves a click or two.
You can save a few clicks by pressing Ctrl+V to copy the Access data into Word.
Use Word's Database feature for more flexibility
The Clipboard method is quick and easy, unless you want dynamic data. You might have noticed that the Paste Link option is disabled when copying directly from a table or query (Figure B). To link data or to avoid using Access altogether, use Word's Database toolbar instead of the Clipboard as follows:
- Open a blank Word document and display the Database toolbar by choosing Toolbars from The View menu and then selecting Database. (There's no Database toolbar in Word 2007.)
- Click Insert Database on the Database toolbar.
Word 2007 users must add this command to the Quick Access toolbar as follows:
- Click the Office button and click Word options.
- In the resulting window, click Customize.
- Choose All Commands from the Choose Commands From control's Dropdown list.
- Select Insert Database and then click OK.
- Click Get Data in the resulting Database dialog box.
- Use the Select Data Source dialog to locate the Access database (Northwind in this case). Double-click the database or select it from the list and click Open.
- In the Select Table pane, choose the table or query that contains the data you want in your Word table and click OK. Figure G shows the Orders table from Northwind selected.
- Click Query Options in the Database dialog box to limit the data.
- Click the Select Fields tab. By default, Word selects all of the fields in the source. Use the two controls between the two field lists to select and remove fields as needed. Figure H shows only three columns selected: OrderID, CustomerID, and OrderDate.
- Click OK.
- Click Insert Data.
- In the resulting dialog box, check the Insert Data As Fields option, and click OK. This magic bullet links the Word data to its source. Figure H shows the resulting linked fields. Word 2007 might force you to acknowledge missing fields by clicking Remove Field. The truth is, Word 2007's feature is buggy. I hope that a subsequent patch or a service pack improves it.
Select a table or query.
Limit the data by specifying only the columns you need.
Most likely, you'll want to format the resulting table a bit. I recommend that you convert the resulting table to text and then reinsert a Word table.
Word's Insert Database method only seems like it takes more effort than the Clipboard because you're creating the query on the fly from inside Word. The alternative is to open Access and create a query, which you might not want to do. To update the Word table data, click Update Field on the Database toolbar. The Access table must be closed for the link to work.
Easy collaboration, if you know where to look for it
Word can import Access data, but finding the right method can take a little effort. First, there's always the Clipboard, but there's no link between the copied data in Word and the Access data source. To keep a dynamic connection between Word and Access, use Word's Database toolbar.
Susan Sales Harkins is an independent consultant and the author of several articles and books on database technologies. Her most recent book is "Mastering Microsoft SQL Server 2005 Express," with Mike Gunderloy, published by Sybex. Other collaborations with Mike Gunderloy are "Automating Microsoft Access 2003 with VBA," "Upgrader's Guide to Microsoft Office System 2003," "ICDL Exam Cram 2," and "Absolute Beginner's Guide to Microsoft Access 2003" all by Que. Currently, Susan volunteers as the Publications Director for Database Advisors at http://www.databaseadvisors.com. You can reach Susan at email@example.com.
Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.