OpenDataSource fails if SQLStatement1 is not empty

[Robert]

I have a mailmerge document in Word 2002 that uses a SQL query against
a SQL Server database. The relevant code is as follows:

With ActiveDocument.MailMerge
.MainDocumentType = wdFormLetters

.OpenDataSource Name:=strConnectString, _
SQLStatement:=strSQL

.Destination = wdSendToNewDocument
.Execute Pause:=False
End With

The strConnectString variable is the full path to an .ODC file (thanks
to Peter Jamieson for that).

The mail merge was running fine until I wanted to add an ORDER BY
clause to the SQL string. That pushed it over the 255 character limit,
so I put the clause in strSQL1 and changed the code to this:

.OpenDataSource Name:=strConnectString, _
SQLStatement:=strSQL, SQLStatement1:=strSQL1

Unfortunately the code would no longer run. No matter how I construct
the two SQL segments, if I use the SQLStatement1 parameter for
anything I always get the error message "Word could not open the data
source."

I've tested the combined SQL statement to be sure it's valid, so my
only conclusion is that for some reason you can't break up your SQL
into two pieces the way the documentation claims you can.

Does anyone dealt with this issue at all?

Robert

 

[Peter Jamieson]

I've had, and reported, problems with OLEDB connections over 255 character
long.

For your own satisfaction it may be worth taking a simple, short query and
splitting it up into SQLStatment and SQLStatement1 to verify that the
problem is not solely a result of using SQLStatement1. I'll check again if
you're still having trouble.

(FWIW the main problem I've had with splitting the query into 2 - other than
overall length - is forgetting to ensure that there is a space at the end of
part 1 or the beginning of part 2 if it is syntactically necessary.)

I'd guess you've probably made the query as short as possible, but you
started with a query generated by MS Query there are usually a few things
you can do to reduce its length, including
a. shorten (and in some cases remove altogether) any table alias names
b. remove any quotes that are not syntactically essential (e.g. when alias
names contain spaces, more quoting may be needed)
c. using SELECT * rather than SELECT fieldname1, fieldname2 etc.

If that isn't enough, the best way forward is probably to create a view in
SQL Server if that is feasible in your situation.

 

[Robert]

I did some further testing and found, first, that with OLEDB I could
take a query with less than 255 characters and break it into 2 parts
and it still worked, but increasing it to over 255 caused it to fail.
So the problem is with the combined length, not with using the
SQLStatement1 parameter per se.

Second, as you indicate, the problem is with using OLEDB. The same
255+ length query works if I use a FileDSN connection.

Seems to be a definite bug in Word's integration with OLEDB.

Robert