MailMerge cannot open Access QUERY data source

[Chuck Hartman]

I have a Word 2000 mail merge document that works fine when connecting to an
Access 2000 QUERY as its data source. However, when I try to open the (same
or even a new) document with Word 2003 and use an Access QUERY as its data
source, Word 2003 says it cannot open the data source. I have tried both
Access 2000 and Access 2003, but it seems to complain that there are either
no records or too few fields. Word 2003 does seem to be able to open an
Access TABLE as a data source, but not a QUERY.

Is there a different method that I can use to sucessfully use an Access
QUERY as a MailMerge data source? Thanks.

Chuck Hartman

 

[Cindy M -WordMVP-]

Hi Chuck,

I have a Word 2000 mail merge document that works fine when connecting to an
Access 2000 QUERY as its data source. However, when I try to open the (same
or even a new) document with Word 2003 and use an Access QUERY as its data
source, Word 2003 says it cannot open the data source. I have tried both
Access 2000 and Access 2003, but it seems to complain that there are either
no records or too few fields. Word 2003 does seem to be able to open an
Access TABLE as a data source, but not a QUERY.

Mail merge can work with queries just fine, but not with all queries. Could
you go into the SQL View of a (relatively simple) query that mail merge can't
link to and copy/paste the SQL into your reply, please?

Also, you could try using a different connection method (Word 2003 changed the
default way it connects to data). There are instructions for this in the Word
2002 section of my website's mail merge FAQ.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[Chuck Hartman]

Cindy,

The querries are fairly complex with multiple inner joins, however my guess
is that the problem relates to the use of a couple of user defined functions
that are defined in an Access Module. Those functions worked fine when
merge talked to Access via DDE, but not via OLE DB.

This application is over eight years old and uses a number of fairly complex
querries. It would probbly make sense for me to port the application to
SQLServer some day. However, since I only need to run this merge report once
per year, I really can't justify the effort to do that at this time. To
work around the problem this year, I have changed the select querries to
make table querries and now the mail merge document datasources use those
tables directly.

Chuck Hartman

 

[Cindy M -WordMVP-]

Hi Chuck,

The querries are fairly complex with multiple inner joins, however my guess
is that the problem relates to the use of a couple of user defined functions
that are defined in an Access Module. Those functions worked fine when
merge talked to Access via DDE, but not via OLE DB.

Yep, that explains it. User-defined functions and some VBA functions are only
available in DDE mode (because they require the user interface to execute;
other functions are "native" to the ODBC driver and will work outside the UI).

Besides the work-around you've discovered, you'd also have the option of
continuing to use the DDE interface. You'll find instructions on how to change
the connection method in the Word 2002 section of my website's mail merge FAQ.

This application is over eight years old and uses a number of fairly complex
querries. It would probbly make sense for me to port the application to
SQLServer some day. However, since I only need to run this merge report once
per year, I really can't justify the effort to do that at this time. To
work around the problem this year, I have changed the select querries to
make table querries and now the mail merge document datasources use those
tables directly.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail