mail merge to Access via DDE is a problem

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Tim - 30 Jun 2004 16:41 GMT

I'm using microsoft Word 97 and Access 97 and trying to
setup mail merges from multiple Word documents to one
query in an Access database. When I setup a mail merge
using DDE, it opens a copy of Access for each merged
document that is open. Sometimes it is necessary to open
many merged documents at the same time and aside from
cluttering up the task bar with Access windows, it also
sometimes causes errors saying that there are too many DDE
connections.

I've also tried setting up the merges via Microsoft query
but because the SQL statements that Query creates are
longer than 255 characters, Word 97 can't use them.

Please tell me that there is another way that I can link
my mail merge documents with an Access query. Any help
would be appreciated.

Reply to this Message

Peter Jamieson - 02 Jul 2004 00:06 GMT

> I've also tried setting up the merges via Microsoft query
> but because the SQL statements that Query creates are
> longer than 255 characters, Word 97 can't use them.

There is an option in MS Query to edit the SQL that the package
generates. Typically, MS Query generates alias names for every table in
the query (even when they are not essential) and when it does so, it
just uses the table name as the alias name. MS Query also tends to list
all the column names in the table/query when "*" would usually do in a
simple case. So for example, MS Query might generate

SELECT mytable.myfield1, mytable.myfield2 FROM mytable mytable

when the following would probably work just as well:

SELECT m.myfield1, m.myfield2 FROM mytable m

or possibly

SELECT m.* FROM mytable m

or perhaps

SELECT * FROM mytable

If you find that MS Query simply regenerates its own lengthy code when
you try to change it, your best bet is probably to set up the link using
a bit of VBA that just calls the OpenDataSource method - as long as
you can work out what file name and connection string are required, you
can insert up to about 255/511 characters of SQL using the SQLStatement
and SQLStatement1 parameters.

If you actually need to use SQL with more than 255/511 characters, I
don't know of a way to set up the link, unless of course you can set up
the SQL as a query in Access.

Peter Jamieson

Reply to this Message