You are currently offline, waiting for your internet to reconnect

How to Link a Microsoft Access Query in a Word Document

This article was previously published under Q100931
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
SUMMARY
This article describes how to create a link in Word for Windows to aMicrosoft Access query. You can link a Microsoft Access query or table toyour Word document, but you cannot link a Microsoft Access form or report.
MORE INFORMATION

Microsoft Word for Windows 2.x and Microsoft Access 1.x

Microsoft Access copies a query to the Clipboard in unformatted textformat; so, when you link a Microsoft Access query to your Word document,it appears in tab-delimited text format.

An automatic link to a Microsoft Access query will not be updated. Afteryou create an automatic link, you must change it to a manual link using theLinks command on the Edit menu. Then you can use the Links Update command(on the Edit menu) or press F9 to update the Access information.

For more information on editing and updating links, see pages 686-690 inthe Word for Windows, version 2.0, "User's Guide."

It is only possible to link an entire Microsoft Access query in a clientapplication. It is not possible to link a portion of a Microsoft Accessquery to your Word document. If you copy part of the Microsoft Access queryto link to your Word document, the Paste Link button is unavailable, andthe Source is Unknown in the Paste Special dialog box in Word.

NOTES

In Word versions 6.0 and 7.0 and Microsoft Access versions 2.0 and 7.0, theDDEAUTO field won't change to DDE after you click the Edit Links commandand select Manual. You have to edit the field and remove AUTO.

In Word 7.0 and Microsoft Access 7.0, by default the DDEAUTO field isinserted with an \r switch instead of the \t switch. The \r switch preventsuser from changing the specified item, like changing "All" to "Data." Ifyou do change an item to "Data," you will receive the "Word Cannot ObtainData from ....." error message. Remove the \r and it will work properlylike Word 6.0. Also, in Word 6.0, the \t for unformatted text is includedby default; in Word 7.0 unformatted text is pasted as formatted text.

Creating the Link

  1. In Microsoft Access, activate the Database window.
  2. Choose the Query button (or from the View menu choose Queries.)
  3. Select the icon representing the desired query to copy. From theEdit menu, choose Copy. (Note: Do not open the query and chooseSelect All Records from the Edit menu.)
  4. Switch to Word for Windows.
  5. On the Edit menu, click Paste Special.
  6. In the Data Type box, select Unformatted Text and then choose thePaste Link button.
Note: Unformatted Text is the only selection that appears in theData Type box.

The above steps create a DDEAUTO field in the document that can bedisplayed by turning on Field Codes from the View menu. The DDEAUTOfield will appear as follows:

{DDEAUTO MSAccess "database.mdb;QUERY queryname" All \* mergeformat \t}

To change the link to manual so that it will be updated correctly, fromthe Edit menu choose Links, select the desired link, and then chooseManual under Update. This will change the DDEAUTO field to a DDE fieldthat can be manually updated by pressing the F9 key.

This field can also be created manually by typing in the above fieldcode (press CTRL+F9 to create the field brackets) and then pressing F9to update the field and establish the link. The database must be opento successfully create and update the link.

The DDE field linking a Microsoft Access query can also be edited tospecify different items in the query. "All" is the item requested bydefault when copying and pasting the query in the above example. However,the following items can be used in the place of "All" in the DDEAUTOfieldcode to link different items in the query:
   Item          Result   -------------------------------------------------------------------   All           All the data in the table including the column names.   Data          All rows of data without the column names.   FieldNames    A list of Columns.   NextRow       Only the next row in the table/query. When the                 conversation is first started, NextRow returns the                 first row. If the current row is the last record and                 NextRow is executed, the request will fail.   PrevRow       Only the previous row. If PrevRow is the first                 request ever, a new channel the last row of the                 table/query is returned. If the current row is the                 first record, the request for PrevRow will fail.   FirstRow      Data in the first row of the table/query.   LastRow       Data in the last row of the table/query.   FieldCount    Count of columns in the table/query.   MacroName     You may also send the name if a macro to be executed.				
For example, the following field creates a link to the field names inthe Customer List query in NWIND.MDB database:

{DDE MSAccess "C:\\ACCESS\\NWIND.MDB;Query Customer List" FieldNamesmergeformat \t}
REFERENCES
Word for Windows "User's Guide," version 2.0, pages 686-690
2.0 2.0a 2.0a-cd 2.0b 2.0c 2.0c-cd, 6.0, 6.0a, 6.0c, 7.0, 7.0a winword2 winword
Properties

Article ID: 100931 - Last Review: 08/16/2005 18:49:08 - Revision: 2.2

  • Microsoft Word 2.0 Standard Edition
  • Microsoft Word 2.0a
  • Microsoft Word for Windows 2.0b
  • Microsoft Word 2.0c
  • Microsoft Word 6.0 Standard Edition
  • Microsoft Word 6.0a
  • Microsoft Word 6.0c
  • Microsoft Word 95 Standard Edition
  • Microsoft Word 95a
  • Microsoft Access 1.0 Standard Edition
  • Microsoft Access 1.1 Standard Edition
  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 95 Standard Edition
  • KB100931
Feedback