error messages when connecting to an MS Access query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Paul - 19 Feb 2008 02:43 GMT

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

Reply to this Message

Peter Jamieson - 19 Feb 2008 09:20 GMT

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.

Signature

Peter Jamieson
http://tips.pjmsn.me.uk

> 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
[quoted text clipped - 10 lines]
>
> Paul

Reply to this Message

Paul Ponzelli - 20 Feb 2008 19:16 GMT

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

> You will probably have difficulty in Word 2002 or later if your query has
> any of the following:
[quoted text clipped - 25 lines]
>>
>> Paul

Reply to this Message

Peter Jamieson - 20 Feb 2008 19:40 GMT

> 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.

Signature

Peter Jamieson
http://tips.pjmsn.me.uk

> You're right, Peter, I have a user-defined function in my query.
>
[quoted text clipped - 37 lines]
>>>
>>> Paul

Reply to this Message

James - 23 Feb 2008 00:43 GMT

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

>> 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.
[quoted text clipped - 53 lines]
>>>>
>>>> Paul

Reply to this Message