Software

How do I... Fill a Word table with Access data?

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. 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.

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.

Figure A

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:

  1. In Access, select all the data in the resulting query (or table).
  2. Select all the records by clicking the intersecting header cell at the top-left corner (circled in Figure A), or by pressing [Ctrl]+[A].
  3. Press [Ctrl]+[C] to copy the selected data to the Clipboard.
  4. Open a new Word document.
  5. 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.
  6. 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.
  7. 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].
  8. 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.
  9. Click OK to insert the table shown in Figure E.

Figure B

Paste Special offers a new of ways to paste data from the Clipboard into a Word document.

Figure C

Paste just the data in columnar format.

Figure D

Use Word's Table commands.

Figure E

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.

Figure F

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:

  1. 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.)
  2. Click Insert Database on the Database toolbar.

Word 2007 users must add this command to the Quick Access toolbar as follows:

  1. Click the Office button and click Word options.
  2. In the resulting window, click Customize.
  3. Choose All Commands from the Choose Commands From control's Dropdown list.
  4. Select Insert Database and then click OK.

  1. Click Get Data in the resulting Database dialog box.
  2. 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.
  3. 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.
  4. Click Query Options in the Database dialog box to limit the data.
  5. 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.
  6. Click OK.
  7. Click Insert Data.
  8. 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.

Figure G

Select a table or query.

Figure H

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 ssharkins@gmail.com.

About

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.

2 comments
message.mc
message.mc

Hi, I was wondering if Conditional Expressions be used in the MS Word Doc template to Filter Imported Data from MS Access, so that the populated data can be controlled and streamlined accordingly. I have a document that I only really need a single template for, but can be used for multiple Audiences who each have different interests. I am trying to use Access to collate all the data, and then use MS word to publich the relevant data to the particular genre of interest for a select group of readers. This keep the document short, and relevant for the target audience, without myself having to make any custom changes to multple documents Then of course, if I change the overall template once, it will apply to all the groups who receive the published document.

leve1fm-22925174607167741352276958795218
leve1fm-22925174607167741352276958795218

Susan, thank you for the article; It was precise and easy to follow. I have been tasked to populate partially filled tables (~60 pages) in Word doc with data from different Access db files (test result in a pre-defined format). Is there an efficient way of accomplishing this? In the past, I had done a similar task for Access->Excel using VBA; Unfortunately Word tables are indexed instead of bookmarked and changes to Word tables will break the VBA code. I looked into using mail merge in Word 2003, but it requires distinct tags for EACH table cell (a daunting task); According to a colleague, mailmerge in Word97 took a tab delimited file without any need for tags! Thank you.

Editor's Picks