Access query merging into Word doesn't work

[JayBuck]

We are using Office 2003 Pro - and I'm having an issue with Access merging
Queries into Word documents. If I use a base letter, and change the query
I've built it with, the new query comes up without any data in it, although
there is data showing in Access. What am I doing wrong? Do I need to build
a completely new letter set? I have Multiple documents I use this way and it
would take much to long to change them all again. HELP!! --
Jay :0)

 

[Peter Jamieson]

Does your Access query use wildcards * and ? ?

If so, the simplest thing to do is probably to make a duplicate of the query
that uses % instead of * and _ instead of ?, and use that as your data
source.

The problem is that by default, Access expects to use * and ?. In Access
2003 you can modify the database so that Access uses % and _ instead (this
conforms to the "SQL-92" standard instead of the older "Jet" syntax).
However, you need to be sure that you know all the consequences of a such a
change and with a well-exploited database that can be hard. I also see that
Access 2007 seems to have moved backwards in this respect.

When you connect to Access from Word 2003, Word uses OLE DB to connect by
default, and OLE DB /always/ uses % and _. You can change the way that Word
connects to DDE (which will recognise the * and ?, but has to start Access
and does not do Unicode characters properly. I don't think changing to ODBC
will help on this one.

Peter Jamieson

 

[JayBuck]

I'll try that - will make a copy of the database to take home this weekend,
and try it there. Thanks for the info! I MISS Word's ability to pull date
directly from the database - where you could do the query right in Word, and
I never had problems like this!! :-( Jay

 

[Peter Jamieson]

<<
I MISS Word's ability to pull date
directly from the database - where you could do the query right in Word, and
I never had problems like this!! :-( Jay
Not sure which facility you are referring to but perhaps enabling the
Database toolbar in Word will bring some of it back.

If it's the ability to modify the query criteria for a merge you've lost,
you can get at the Query Options dialog via the Mail Merge Recipients Dialog
box (which you can reach from the Mail Merge toolbar) - click on one of the
drop-downs in one of the column headings and pick "Advanced...".
Unfortunately, the dialog does not always get the data types right and
stuffs in extra criteria that you didn't specify, so I generally end up
editing the generated query directly in VBA. Unfortunately using
Tools|Customize|Commands|All commands to drag the /old/ Query Options dialog
to a toolbar doesn't seem to work as the criteria you enter there are
completely forgotten when you save the mail merge main document.

Peter Jamieson