error messages when connecting to an MS Access query

[Paul]

I'm attempting to create a Mail Merge document using a Select query in MS
Access 2003, and I get error messages saying things like "Word was unable to
open the data source" or "Record 1 contained too few data fields." The
thing that puzzles me about this, however, is that I have no problem
connecting to a table in the same Access database. The problem only occurs
when I try to connect to a query.

Because of the data I need to merge into my document, however, I need to use
a query. Is there something I need to do when I'm trying to merge with data
in an Access query so that I can avoid these problems?

Thanks in advance,

Paul

 

[Peter Jamieson]

You will probably have difficulty in Word 2002 or later if your query has
any of the following:
a. runtime parameters (but then it probably would not be listed)
b. user-defined functions (i.e. functions written in Access VBA)
c. some of the financial series functions, and one or two other functions
such as "replace"
d. wildcards, e.g. used in a LIKE clause
e. references to data outside the Access database (this is a bit
hit-and-miss)

In most cases, you should be able to get around this by using the old DDE
method to connect - check Word Tools|Options|General|COnfirm conversions at
open, go through the connection process again, and select the DDE option
when it is offered.

 

[Paul Ponzelli]

You're right, Peter, I have a user-defined function in my query.

I tried using a DDE connection, but I wasn't able to get it to work.
Instead, I got messages saying it couldn't make the connection. One
alternative is to run a make-table query that creates a temporary table from
the single record to mail merge. I'll probably do something like that.

Thanks for your help with this.

Paul

 

[Peter Jamieson]

I tried using a DDE connection, but I wasn't able to get it to work.

Instead, I got messages saying it couldn't make the connection.

When you try to make a DDE connection, if the database is not already open,
you can get problems because DDE starts Access, Access tries to open the
database, and may display one or more dialog boxes (e.g. the security dialog
box). They may not be displayed in front of the Word document, so you do not
know that you have to respond to something. Then they time out, and the DDE
connection fails. So it may be worth using Alt-tab to cycle through the
various Windows.

AFAIK with queries that contain UDFs, you either have to get DDE to work,
one way or another, or find a workaround that avoids the UDF. Only the
Access programme "understands" UDFs - no other method of getting at the
Access data really knows what they are.

 

[James]

I'm actually opening the Word document from VBA in the Access database, so
it's already open. It's actually easy to run a Make Table query in Access,
so I'm going to try that to bypass the problem with the UDF.

Thanks, Peter