none
Word 2010 Mail Merge using Access Queries as a source

    Question

  • Hi,

    Since office 97 I have been using word to mail merge to data extracted using access queries. These access queries are now in access databases that link back to SQL server 2008.

    The office product is 2010 and we generate lots of varaible letters each day based on access 2010 queries. ( to a backend sql database)

    I am having difficulty in getting these to work, previously we always used DDE (yes I've set the option file convert on open). I have the following quetsions.

    1. What is the recommended way of doing this now?

    2. How do I get word to save the document and remember how the merge process took place so that all the steps don't have to be repeated evertime I want to generate letters?

    Any suggestions would be appreciated and I'm told that DDE is being 'depreciated' now.

     

    Regards

    Malcolm

    Thursday, May 19, 2011 8:21 AM

Answers

  • There is definitely and issue with using Parameter Queries, but none that I have come across when using linked tables or simple (non-parameter) queries in an Access front end database where all of the tables are linked.

    The issue with the parameter query is of course that whenever it is accessed, the user is asked for the criteria to be applied.  When mailmerge attempts to obtain data from such a query, there is no opportunity given to the user to provide that query so the attempt to use the data fails.

    You can of course initiate the merge from Access to an existing Word Document that has the appropriate merge fields set up in it.

    The process could also be automated from Word so that the user is asked for the parameter which is then supplied via code to Access so that the data set can be assembled.


    Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "TrilobyteMalcolm" wrote in message news:cb450f61-9b59-4f3a-8833-e795647787fc@communitybridge.codeplex.com...

    Doug,

    I have done a bit more digging and it seem sthat there is a problem in word and access  where the access data is either held as a linked table or else has parameters in it. This issue seems to have been around for a while. So users are constrained to using word / access mail merges only to simple tables /queries located on a local accdb file. Is this your understanding too?

    This would seem to be amajor limitation for everyone out there who is using the 'access front end / sql back end' model.



    Regards



    Malcolm


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    Friday, May 20, 2011 9:12 PM

All replies

  • I do not have any issues using the default OLE DB Database Files method of connecting to an Access 2010 table or query.  Further, if I save the document after attaching the data source to it, then close the document and subsequently re-open it, the document is ready to go without any action being required as far as attaching the data source is concerned.

    --
    Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "TrilobyteMalcolm" wrote in message news:1d38088a-65f4-4074-a394-dfbc0c5efd86@communitybridge.codeplex.com...

    Hi,

    Since office 97 I have been using word to mail merge to data extracted using access queries. These access queries are now in access databases that link back to SQL server 2008.

    The office product is 2010 and we generate lots of varaible letters each day based on access 2010 queries. ( to a backend sql database)

    I am having difficulty in getting these to work, previously we always used DDE (yes I've set the option file convert on open). I have the following quetsions.

    1. What is the recommended way of doing this now?

    2. How do I get word to save the document and remember how the merge process took place so that all the steps don't have to be repeated evertime I want to generate letters?

    Any suggestions would be appreciated and I'm told that DDE is being 'depreciated' now.



    Regards

    Malcolm


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    Friday, May 20, 2011 9:34 AM
  • Doug,

    I have done a bit more digging and it seem sthat there is a problem in word and access  where the access data is either held as a linked table or else has parameters in it. This issue seems to have been around for a while. So users are constrained to using word / access mail merges only to simple tables /queries located on a local accdb file. Is this your understanding too?

    This would seem to be amajor limitation for everyone out there who is using the 'access front end / sql back end' model.

     

    Regards

     

    Malcolm

    Friday, May 20, 2011 10:19 AM
  • There is definitely and issue with using Parameter Queries, but none that I have come across when using linked tables or simple (non-parameter) queries in an Access front end database where all of the tables are linked.

    The issue with the parameter query is of course that whenever it is accessed, the user is asked for the criteria to be applied.  When mailmerge attempts to obtain data from such a query, there is no opportunity given to the user to provide that query so the attempt to use the data fails.

    You can of course initiate the merge from Access to an existing Word Document that has the appropriate merge fields set up in it.

    The process could also be automated from Word so that the user is asked for the parameter which is then supplied via code to Access so that the data set can be assembled.


    Hope this helps.

    Doug Robbins - Word MVP,
    dkr[atsymbol]mvps[dot]org
    Posted via the Community Bridge

    "TrilobyteMalcolm" wrote in message news:cb450f61-9b59-4f3a-8833-e795647787fc@communitybridge.codeplex.com...

    Doug,

    I have done a bit more digging and it seem sthat there is a problem in word and access  where the access data is either held as a linked table or else has parameters in it. This issue seems to have been around for a while. So users are constrained to using word / access mail merges only to simple tables /queries located on a local accdb file. Is this your understanding too?

    This would seem to be amajor limitation for everyone out there who is using the 'access front end / sql back end' model.



    Regards



    Malcolm


    Doug Robbins - Word MVP dkr[atsymbol]mvps[dot]org
    Friday, May 20, 2011 9:12 PM
  • I have wasted countless hours trying to get this to work only to find now it does not in good old Office 2010. I rue the day I ever installed this at our company as it has given me more problems than solutions. We were on Office 97 previously and everthing like this worked easily. I didn't have to spend hours creating a complicated word document or learn a programming language to communicate one piece of software with the other. To be brutely honest it's a joke. Anyway rant over, now for the question.

    1. How do you programtically pass this data from word to access?

    2. Are Microsoft likely to fix this issue?

     

     

     

     

     

    • Proposed as answer by MHGregoire Friday, August 05, 2011 2:59 AM
    • Unproposed as answer by MHGregoire Friday, August 05, 2011 3:02 AM
    • Proposed as answer by MHGregoire Friday, August 05, 2011 3:02 AM
    • Unproposed as answer by MHGregoire Friday, August 05, 2011 3:02 AM
    Monday, June 20, 2011 8:18 AM
  • Sharksguts - I feel your pain believe me!  I've been trying to figure this out for a while -

    Doug - your answer is right, but no one could recognize what you were saying - I've broken it down and it works.

    Here goes -

    1-Likely you already have the Word doc, so leave that out there.

    2-Open the Access db.  Highlight the query.  Select Word Merge from the Ribbon. Follow the prompts - select the Word doc from the MS Access side of the relationship.  It will open the Word document.

    3-Save the document immediately.  Close it.  Reopen it.  It will immediately show you that it is going to open the MS Access query.  yeah!!!

     

     

    Friday, August 05, 2011 3:07 AM
  • We are having same issues as Malcolm above. 

    cannot link MS Word 2010 with a simple (non-parameter) query MS Access 2010 .accdb, though we have worked in 2003 for years.  when merging, if you:

    - confirm data source by selecting the .accdb and click "show all".  It cannot find the data source (keeps looking for the .mdb) event though you select the .accdb. 

    - confirm data source by selecting the .accdb and not click "show all", it only shows Tables not Queries (and yes we have selected "Confirm file format conversion on open"). 

    what has changed? anyone know???

    thank you in advance!

     

    Wednesday, January 11, 2012 8:58 PM
  • I am also having an issue trying to mail merge my query (non parameter) to a word document. It will allow me to mail merge other queries that I have, but not the one I want. The one I want to merge has IIF statements throughout it. I'm not sure if this is what is causing the issue, but my other queries have them also. What would you suggest? or is it pointless to continue to try? I thought about trying to redo my query, but that would take hours maybe days. i'm worried to do all the work and it still not work. Thanks
    Friday, February 01, 2013 8:32 PM
  • I don't think it's the IIF that's causing the problem. It works here, right now - I'm using Office 2010, and sandbox mode is enabled, which means that Access should deny the use of any unsafe functions. So presumably it doesn't regard IIF as unsafe.

    Typically, the one thing that will let you use any type of query is to connect to the database using the old DDE method rather than the current default (OLE DB). That is not ideal, because it requires that Access is running and has the database open, but to do that, you need to check the relevant option (in Word 2010, check File tab->Options->Advanced->General (near the bottom)->Confirm file format conversion on open, then go through the database selection process again. When you see the "Confirm Data Source" dialog, if you're using a .accdb you will need to check the "Show All" button to reveal the DDE option.

    However, if you need to stay with OLE DB, there could be a number of things going on. There are a number of types of query that Word does not list when you try to connect to the data source - parameter queries are only one of them. But in some cases Word can in fact use the query, but you have to connect using Word VBA's OpenDataSource method. In those cases in Word 2010 it would normally be enough to do this:

    Sub connectToDataSOurce()
      ActiveDocument.MailMerge.OpenDataSource Name:="the full path name of the access database", SQLStatement:="SELECT * FROM [the name of the query]"
    End Sub

    In some case you may be able to select a query in Word, but Word retrieves no records even though you know that there are records. That is usually because you have wildcard queries that use the traditional Access wildcards "?" and "*". When connecting using OLE DB you have to use the ANSI SQL standard equivalents, "_" and "%". The easiest way to deal with that if you are in a position to add queries to Access is to copy and rename the query and change all the relevant wildcards.

    In other cases, OLE DB won't run the query because some functions and types of function are disallowed, for one reason or another. I think that also varies from version to version, but for example
     - you cannot use "user-defined functions", i.e. functions defined in Access Basic
     - you cannot use some of the financial functions such as IRR, MIRR.
     - some common functions such as replace() may cause problems.


    Peter Jamieson

    Saturday, February 02, 2013 11:21 AM