locked
Useing a parameter with ms query and mail merge 2010 RRS feed

  • Question

  • I have built a Query using an ODBC driver supplied by our accounting package. I want to use the query in a mail merge document.

    What I would like to do is have the document open and prompt for the record desired.

    I have built the word document with all the fields I wish to be included. I can filter the source but it requires that I use MS Query to enter the criteria and then return the results to word.

    I have been able to create a parameter while in MS query but when I try to use the parameter query and attach it to word I receive the message that “parameters are not allowed in this query”.

    I don’t understand that message when I know that it is included in the query file.

    After it fails another message pops that Word cannot reestablish the DDEconnection.

    Why can’t I do what I wish to do?

    I have looked on the web and have seen messages about not being able to create parameters when the query can’t be rendered graphically but I don’t understand the answers. I don’t know if this query is one of that type, or how to figure that out. I am not skilled in SQL language to follow the instructions.

    Is there a way to do what I want? If there is can it be explained in, “For Dummy” terms. Smile

    Thanks for any help.

    I was trying to do this to save time but so far it has done anything but save time.

    Shannon

    Friday, April 12, 2013 2:18 PM

Answers

  • As far as I know, Word has never worked with parameter queries created via MS Query, even for simple query types that do allow parameters. If it did, on Windows, it was before Word 97. 

    In other words, the problem may not be to do with your query, but to do with constraints in Microsoft Word. In the end, whatever you do in MS Query, Word ends up using ODBC directly to get data from the source. MS Query isn't actually involved.

    The problem is that there is no simple alternative. Some possible routes forward:

     a. Word VBA to prompt for the record identifier, then construct the appropriate SQL query and use Word VBA's OpenDataSource method to open the data source directly. 

     b. do a two-phase merge. Step one retrieves the data in a form that Word can easily consume. Step two does the merge. For example, as a general rule, Excel has much better facilities for retrieving data than Word does. Excel is far more likely to work with a parameterised query than Word is. But using Excel as a data source introduces a whole bunch of other uncertainties into mailmerge.

     c. If your users have Access, create an Access database with a table that is linked to your data source. Then either use that linked table as your data source, or use a query that gets data from the linked table as the data source.


    Peter Jamieson

    • Marked as answer by Rex Zhang Thursday, April 25, 2013 8:30 AM
    Friday, April 12, 2013 7:03 PM