Access query does not populate in Word merge

[MamaGrubb]

The query produces a list in Access, but when you try to use it in Word the
Mail Merge Recipients list comes up blank -- no records.

Is there a setting that controls this? It used to work for a client on her
machine using the same Access mdb. I tried it myself on her PC and could not
get it to populate.

 

[Peter Jamieson]

As a workaround, check Word Tools|Options|General|"Confirm conversion at
open", go through the process of connecting to Access again, and select rhe
DDE option when offered. Assuming you're using Word 2002 or 2003, that is.

The chances are that the query uses "old-style" wildcards for text matching.
These were "*" to match multiple characters, and "?" to match a single
character. However, by default, Word 2002/2003 use the OLEDB option to
connect to Access, and OLEDB only recognises the "new" wildcards, "%" and
"_". it's also possible that your client's .mdb file has been upgraded from
(say) Access 2000 format to Access 2003 format, but I don't think that is
actually the problem in this case.

Peter Jamieson

 

[MamaGrubb]

Thanks so much for your prompt reply -- I will pass the info along to my
client.

 

[simon]

Hi, i am having the same problem but this Database HAS been upgraded
to 2003. How is it affected and is there a workaround?

Many Thanks

 

[Peter Jamieson]

I think it goes something like this:
a. Database 2000 v. database 2003 format is probably a red herring
b. there's a setting in Access Tools|Options|Tables/queries for "SQL Server
Compatible Syntax (ANSI-92)". If you check it, Access has to change the
database a bit (I think it can be undone but I'd back up if you're going to
mess around with this feature). Then when you run queries /in Access/ they
recognise the new wildcards % and _ but not the old ones * and ?. This is a
per-database setting.
c. when you connect to Access using OLEDB (which is what Word
2002/2003/2007 do by default), OLEDB only works with the new wildcards.
d. if you are actually connecting using DDE, it gets Access to run its
queries so it should use whichever syntax is set for the database.
e. upgrading the database does not in itself upgrade the queries.

So...
a. If you have set ANSI-92 syntax I wouldn't expect any queries that use *
and ? to work, however you try to run them. % and _ should work whatever you
do (except maybe in a special case if you're using ODBC)
b. if you have left the old Jet syntax in place, queries need to use * and
? if you want to run them from within Access or via DDE, but % and _ if you
want to run them via OLEDB (and probably ODBC).

Peter Jamieson