Can't use query as a data source

[Tony H]

I can effectively merge from Access 2000 into Word 2003 using a table. I have
also set up queries in Access that I would like to merge with. When I try to
use them they are visible for selection, however when I go to merge, I am
told that "data records are empty". I know this is not the case because I can
use the queries in Access. Why can I not merge directly with Queries rather
than Tables?

 

[Lorri F]

Tony, Up until last week, we used queries to create numerous mail merges.
Something changed last week and I was hoping someone out there could help us
figure out what.

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?VG9ueSBI?=,

I can effectively merge from Access 2000 into Word 2003 using a table. I have
also set up queries in Access that I would like to merge with. When I try to
use them they are visible for selection, however when I go to merge, I am
told that "data records are empty". I know this is not the case because I can
use the queries in Access. Why can I not merge directly with Queries rather
than Tables?

To a certain extent, it depends on the type of query. Are these simple select
queries? Or parameter queries? Or do they do any "grouping" (Sum, Average,
etc.)?

May I assume you're using the standard Word 2003 connection method? This would
be OLE DB, and that works with ANSI-92 SQL, instead of the old ANSI-89 used in
Access 2002 and earlier. This could be what's interfering.

If you activate "Confirm conversions on open" in Tools/Options/General, then
link to the mdb, you should get a list of connection methods. If you choose ODBC
or DDE do you get any different result?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
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

 

[Tony H]

Hi Cindy,

Thanks for your suggestion. I tried the suggestion but same result.

OLE gave me the list of queries but still came up blank on the merge. DDE
did not list the queries at all only the table.

As for the queries they are a simple "Like *lead*" type search placed in
area-alhapbetical order. Just used to separate exisiting and prospective
customers etc.

When I set them up I know that they worked for awhile but now won't. Any
ideas what may be causing the conflict??

Tony

 

[Doug Robbins - Word MVP]

In Access, check that the queries are returning something.

I'd be a bit suspicious of your "Like *lead*"

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Tony H]

Hi Doug,

Sorry it's a typo. The Queries do work in Access. I do a Like "*lead*" and
it works fine in Access. Just won't merge with word as a query.

Scratching my head.

Tony

 

[Doug Robbins - Word MVP]

I gather that "lead" appears somewhere in a field and that is the reason for
the use of the wildcards either side of it. I don't have a lot of faith in
the use of wildcards when working across applications and would always
design a table or query so that its use is not necessary when needing to
work across applications.

You could however try displaying the result of the query in Access and then
use Mailmerge from the Access Tools>Office Links menu item.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Peter Jamieson]

Despite your experience so far, I am pretty sure Cindy's analysis is the
correct one:

a. Obvious question, but when you tried opening using DDE, did you click on
the separate "Queries" tab in the dialog box? (FWIW, when opening using
ODBC, you also need to click "Options" and check all the boxes before any
queries will be listed).
b. Are you using any kind of security in your database, especially Workgroup
security?
c. If you copy the query that contains the "*" wildcards and replace the "*"
by "%" signs, when you open that query in Word (using the default OLEDB or
the ODBC method - I would not expect it to make any difference), do you then
see the records?

If when you say "from Access 2000" you really mean "from Access 2000" and
not "from an Access 2000 format database opened in Access 2003" then AFAIK
the only solution is either to
a. use DDE with the "*" version, or use OLEDB/ODBC with the "%" version
(and you need _ instead of ? for single-character escapes). if you are using
Workgroup security you probably won't be able to do that, or it will be
inconvenient
b. use % versions of the queries from Word but * versions in Access.

If you are using Access 2003 you can make a permanent change to the database
itself (open the database in Access and look at
Tools|Options|Tables/Queries) that will make Access use the % and _
wildcards instead. You'd need to change all the queries too. However, I
wouldn't make that change without ensuring that /everything/ was going to
work.

Peter Jamieson