MailMerge OpenDataSource SQL Query Length Limitation

[secret....@massey.ac.nz]

Has anyone else encountered this problem using Word 97, Access 97 and
ODBC 3.5? under Windows NT4 (Service Pack 3) and Office Service
Release One.

I have been using the MailMerge.OpenDataSource method to attempt to
connect and pass an SQL string to Access. Often these queries are
greater than 255 characters, which is not a problem to start with. The
Word VBA help states that you put the first 255 characters into the
variant SQLStatement. Any remaining characters should be placed into
the variant SQLStatement1. This is where the problem happens!

If the string being placed into SQLStatement1 is 255 characters or
less the query will execute. However, if SQLStatement1 has more than
255 characters, error 9105, String has more than 255 characters is
returned. In total I can write and execute a query with no more than
510 characters in it, which is not quite enough for some tasks.

Any help would be most welcome.

Regards,
Kerry

[Cindy Meister -WordMVP-]

Hi Kerry

These limitations have been with Word through numerous versions, I'm
afraid. No chance you can predefine a query that would reduce the number
of characters you'd need in the SQL? Or could you use DAO to create a
query, run the merge to that query, then destroy it when the merge is
done? Have you tried combining this with the QueryString property (I
have no idea if this is also limited)?

> I have been using the MailMerge.OpenDataSource method to attempt to
> connect and pass an SQL string to Access. Often these queries are
> greater than 255 characters, which is not a problem to start with. The
> Word VBA help states that you put the first 255 characters into the
> variant SQLStatement. Any remaining characters should be placed into
> the variant SQLStatement1.
>

Cindy Meister
INTER-Solutions, Switzerland
http://ourworld.compuserve.com/homepages/cindymeister

This reply is posted in the Newsgroup; please post any follow-up
question or reply in the newsgroup and not by e-mail :-)

[secret....@massey.ac.nz]

Hi Cindy,

Thanks for the ideas. Unforunately the QueryString property suffers
from the same problem. It returns the error "string parameter too
long" when you try to modify the querystring to one with 701
characters.

Yes, I have considered the option of modifying the query definition to
shorten the length. I have found in the past Access and ODBC can be a
little troublesome when the table name is not specified in front of
the each field in the SELECT statement. I really would like to err on
the side of caution in this particular situation.

DAO was an alternative, however, I ran into a couple of issues that
would have impacted on the users machine after the query was created.
When I specified in the Connection property of OpenDataSource, Query
<QueryName> I found that I would end up with several copies of Access
being activated by DDE. The first copy was opened when the Logon Box
appeared, then another copy was opened when the data was retrieved.
(Not to mention if the user edited the query, another copy appeared.)

I could be doing something wrong here though!. Any thoughts?.

A couple of possibilities would be to export the data from Access to
an Excel file and merge the data. My other thought would be to export
the data via query from the server to a local DB file on the users
machines. Network traffic is not a major issue. The Word VBA in this
case could look at the local tables of exported data, or regenerate
the data as required. I'm not sure of the limits that Word and Access
can be pushed here....

>These limitations have been with Word through numerous versions, I'm
>afraid. No chance you can predefine a query that would reduce the number
>of characters you'd need in the SQL? Or could you use DAO to create a
>query, run the merge to that query, then destroy it when the merge is
>done? Have you tried combining this with the QueryString property (I
>have no idea if this is also limited)?

Regards,
Kerry:)
Kerry Walker
Massey University Instiute for Executive Development-New Zealand
http://www.massey.ac.nz/~ied
http://ied.massey.ac.nz

[Joshua Sacco]

hi,

not sure why you get multiple instances of Access97, but here's some
code that i use that only results in one instance of access...maybe
it'll help your situation. i read on support.microsoft.com that there is
a 255-charachter SQL limit, but i frequently run queries used as Word97
mailmerge data sources with way over 255 charachters and have had no
problems.

hope this helps some,

josh

Dim dbThisDb As Database
Dim WordDoc As Word.Document, wordApp As Word.Application
Dim strPath2 As String
dim rs as recordset
dim qd as querydef
Dim strSaveAs As String
Set dbThisDb = CurrentDb

Set qd = dbThisDb.QueryDefs("QryRevDecInfo")
qd.Parameters("[forms]![frmdecisionlogin]![combo3]") = Me![Combo3]
qd.Parameters("[forms]![frmdecisionlogin]![combo132]") = Me!Combo132
Set rs = qd.OpenRecordset()
If rs.RecordCount <> 0 Then

strPath2 = CurrentDBDir & "Ltrtorev.doc" 'returns document in
database's directory
If WordRunning = False Then 'function to see if word is running
Set wordApp = CreateObject("Word.Application")
Else
Set wordApp = GetObject(, "Word.Application")
End If
Set WordDoc = wordApp.Documents.Open(strPath2)
WordDoc.Application.Visible = True
WordDoc.MailMerge.OpenDataSource Name:=dbThisDb.Name, _
linktosource:=True, Connection:="QUERY QryRevDecLtrInfo"
With WordDoc.MailMerge
.Destination = wdSendToNewDocument
.Execute
End With
WordDoc.Close
end sub

[Cindy Meister -WordMVP-]

Hi Kerry

> When I specified in the Connection property of OpenDataSource, Query
> <QueryName> I found that I would end up with several copies of Access
> being activated by DDE. The first copy was opened when the Logon Box
> appeared, then another copy was opened when the data was retrieved.
>

Coincidentally, I've been discussing this with a couple of people. I
originally thought Access97 was creating a "replica", but we have come
to the conclusion that it's a fault in the DDE management. The number
of duplicates created is reduced if the CAPTION in the Access window
Title Bar is the default (i.e. "Microsoft Access") and has not been
changed in the Startup options. Using VBA to temporarily reset the
caption did help.

> I have found in the past Access and ODBC can be a
> little troublesome when the table name is not specified in front of
> the each field in the SELECT statement.
>

Yes, there are some very odd problems with the Office97 ODBC drivers -
I can't recommend them at all!

Actually, when I mentioned DAO, I meant to use it to send the data
directly to Word, creating each document separately, from a template -
thus avoiding the mail merge problems altogether...

> A couple of possibilities would be to export the data from Access to
> an Excel file and merge the data. My other thought would be to export
> the data via query from the server to a local DB file on the users
> machines. Network traffic is not a major issue. The Word VBA in this
> case could look at the local tables of exported data, or regenerate
> the data as required. I'm not sure of the limits that Word and Access
> can be pushed here....
>

These also sound like viable possibilities :-) The Excel file format
option should work fine (less overhead & faster than creating or
managing a DB locally, I should think) - I'd try for that, first, if
you decide to go this way.

[Kerry Walker]

Hi Cindy,

>Coincidentally, I've been discussing this with a couple of people. I
>originally thought Access97 was creating a "replica", but we have come
>to the conclusion that it's a fault in the DDE management. The number
>of duplicates created is reduced if the CAPTION in the Access window
>Title Bar is the default (i.e. "Microsoft Access") and has not been
>changed in the Startup options. Using VBA to temporarily reset the
>caption did help.

Thanks for that piece of information, I have changed the Title Bar
option on the front end MDB file, but I was calling to the back end
which I haven't changed. I will try the DDE method again to see what
difference it makes by resetting the caption on the fly

>Actually, when I mentioned DAO, I meant to use it to send the data
>directly to Word, creating each document separately, from a template -
>thus avoiding the mail merge problems altogether...

Sorry my mistake. Coincidentally, that was my first stab at a "Mail
Merge" from Access. I didn't get too far though, before I changed my
mind and thought there must be another way of doing this! The method
would offer a considerable amount of control and flexibility given
some time and patience. It shouldn't be too hard to modify the
template and code to add new fields either. I might attack this method
now that I a better understanding where the potential problems will
be.

>These also sound like viable possibilities :-) The Excel file format
>option should work fine (less overhead & faster than creating or
>managing a DB locally, I should think) - I'd try for that, first, if
>you decide to go this way.

I agree on the overhead issue, Excel would be much better. Though the
current system has a scheduled task list which manages background
tasks for the user while working in Access. It shouldn't be too hard
to add a couple of procedures to compact, repair and clean up a local
DB file (unless someone moves it:).

Many thanks for all the ideas, they have been great help!

Reagards,
Kerry