Mail Merge Error

[Jerry B]

When using MSQuery to pull in data from an IBM AS400 to
mail merge into a word document, I received the error
message "Word was unable to open the data source". I tried
looking at the Microsoft support web information but did
not have time to read the 10 articles I had to download.
Wondering in someone here had a quick answer?

 

[Peter Jamieson]

I don't have an AS400 to experiment with here, but precisely what happens
when you connect using MS Query depends on how you are using it - if you are
"inserting a database" and opting not to insert it as a field, generally
speaking, things will work. If you are inserting the data as a DATABASE
field, or using it as a data source, then the problem is that MS Query might
be able to get the data, but then Word basically has to be able to get it
again, and a number of things may prevent it from doing so, e.g.
a. MS Query may return a DSN-less ODBC Connection string (i.e. starting
something like "DRIVER=" rather than "DSN=") which Word cannot use. Word
really only works with connection strings that specify a machine (system or
user) or file DSN.
b. the SQL is too long (either 256 or 512 bytes is probably too long).
c. the SQL MS Query generates is not valid for the data source (I have
heard that there is, or was, a specific issue with AS 400 SQL to do with the
quoting of multi-part names (e.g. where MS Query would use SELECT x FROM
'a'.b'.'c' and AS400 required SELECT x FROM 'a.b.c'. Or vice versa..
d. the ODBC DSN used to connect does not store the necessary authentication
information, and MS Query does not return the full connection string, with
authenticaiton info, to Word.
e. The connection string returned by MS Query is longer than 255 bytes and
is truncated in a way that makes it fail.

In the case of (b) or (c), you might be able to fix using the SQL button in
MS Query to edit the SQL source to reduce its length or correct its syntax
(if you can work out what is required).

In the case of (d) or (e) you /may/ be able to fix the problem by creating a
suitable DSN that stores the authentication information. Whether that is
possible depends on the ODBC driver (for example, off the top of my head I
don't think the SQL Server driver lets you save the authentication info,
although it looks as if it does).

Otherwise, what you probably need to do is create
a. a suitable ODBC DSN
b. a VBA macro containing an OpenDataSource method call with a Connection
string that works, and a SQLStatement (and if necessary, SQLStatement1)
containing a SQL string that is accepted by AS400. Typically you ought at
least to be able to use MS Query to give you a starting point chunk of SQL
that you could adapt for such a call.

Pity about all this techie stuff but I don't think it's easily avoided.
Another way you /might/ be able to make things easier would be to use an
intermediate database that knows how to connect to AS400 and which is a
better-behaved Word data source. Could be Access or even SQL Server, but
beyond that I can't say.

 

[JerryB]

Thanks for all the info. I am going to try each step
again. I am probably going to recreate my file DSN. The
driver I was using was the Client Access Express ODBC DSN
Driver. Client Access Express is the IBM's PC connection
software for the AS/400.

Best Regards,
Jerry L. Birch

 

[Peter Jamieson]

If you are using a /file DSN/ and you are trying to use OpenDataSOurce to
make a connection, use the following general approach:

OpenDataSource _
Name:="the full path name of the DSN file", _
Connection:="FILEDSN=the full path name f the DSN
file;parameter1=value1;parameter2=value2;", _
SQLStatement:="your SQL statement"

where parameter1=value1 etc. are whatever parameter settings are required by
the connection.

This is slightly different from the situation where you use a User or System
DSN, which both need:

OpenDataSource _
Name:="", _
Connection:="DSN=dsnname;parameter1=value1;parameter2=value2;", _
SQLStatement:="your SQL statement"

In the latter case, and probably the fromer case, in Word 2002 and later you
also need to use the parameter

Subtype:=wdMergeSubtypeWord2000