Why do I get an empty table from an access query in Word

[Roy Lehmann]

I am using Office 2003 under XP Professional. I use the following procedure
in Word:
1. From the menu bar I select Insert.Field
2. In the field window under field names: I select Database and under field
properties I click the Insert database... button
3. In the Databse window I click the Get Data button
4. My Access database opens properly and I get the select table window.

My question: Why do some of the queries listed in the select table window
return empty tables to Word? All the queries work OK in Access.

 

[Peter Jamieson]

The chances are that the queries use wildcards, and use the older Jet
convention where "*" stands for a number of characters and "?" stands for a
single character. Access/Jet can be in one of two modes (see ANSI SQL Qeury
mode" in Access Help for more info): Jet mode (as above) and ANSI mode,
where the equuivalent wildcard characters are % and _. Unfortuantely, when
you get data via the Jet OLEDB provider (which is what Word 2003 does by
default), you have to use the ANSI wildcards. if you try to get data from a
query that uses the Jet wildcards, nothing is retrieved unless the database
has been converted to ANSI Query mode (and since making that change has
consequences for everything that works with an Access database, it needs to
be thoroughly planned and tested).

When you connect to Access data for use as a mail merge data source in Word
2003, you can connect using DDE, which opens Access and uses Access to
execute the queries (I think). However, that option is not available, as far
as I know, when you get data using a DATABASE field (you can select a DDE
option but the resulting field fails). So as far as I know the only
workarounds are:
a. change your database format and all the queries with wildcards
b. in Access, duplicate all the queries with wildcards, and modify the
duplicates so that they use the ANSI wildcards. Use the original queries
when in Access and the duplicates in Word (but of course, if your queries
reference other queries, it's a bit more complicated...)

Peter Jamieson

 

[Roy Lehmann]

Thank you for your quick response. That is exactly the problem. I will
update to ANSI SQL Query mode. There are not that many queries involved.