Automated mail merge from a stored procedure

[Peter Carpenter]

Hello everyone!

I'm trying to automate a mail merge into a Word 2000 document from
within Access 2000. The code I'm using is:

Dim oWord As Word.Application
Dim oDoc As Word.Document
Dim oSel As Word.Selection
Dim appTitle As String
Dim setAccessCaption As Boolean

'Start Word and open the document template.
Set oWord = CreateObject("Word.Application")
Set oDoc = oWord.Documents.Add
oDoc.Activate
oWord.Visible = True

MM.OpenDataSource Name:="", _
Connection:="DSN=CHdatabase;DATABASE=CHdata;", _
SQLStatement:=sqlstr

oDoc.MailMerge.Destination = wdSendToNewDocument
Set MailMerge2000 = oDoc.MailMerge

The value of sqlstr is "execute sp_groupIndHouseData 9, 1875" and this
format works fine for a number of other stored procedures I've got.
However, I have one stored procedure (which admittedly is a lot more
complicated and larger - about 160 columns / varying numbers of rows)
and everytime I try to run it I get an error message from 'Microsoft
Word' stating 'Word was unable to open the data source.'

The interesting thing is that I can open this stored procedure (again
using the same syntax) from msquery within a word document, and the
data shows beautifully. However, when I press return data to word,
the same error comes back.

My questions are, if anyone has any thoughts they'd like to share, are
there limitations on the number of columns allowed? Why would Msquery
be able to access the data but not word? What are the differences?

Thanks HEAPS in advance!!


Peter Carpenter

 

[Cindy M -WordMVP-]

Hi Peter,

My questions are, if anyone has any thoughts they'd like to share, are
there limitations on the number of columns allowed? Why would Msquery
be able to access the data but not word? What are the differences

Word does have an internal limit of 63 columns (fields) for its own
tables, and any created by converters. Normally, though, this wouldn't
come into play for anything coming across an ODBC link.

The other limiting factor, however, is 255 characters, max., for the SQL
Word requires to link to the data source. You don't show us the content
of sqlstr for the datasource in question, but I'm guessing this could be
where the problem is coming from.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.word.mvps.org

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

 

[Peter Carpenter]

Hi Cindy,

Thankyou VERY much for your response and sorry I've taken so long to
get back to you.

The sqlstr that I'm sending (and use sucessfully in MSQuery) is simply
"Execute sp_groupIndHouseData 9" I've also tried using "{CALL
sp_groupIndHouseData(9)}" with the same results.

The strange thing is that this works find with other (admittedly less
complex) stored procedures using the same calling syntax! Why would
the results of the stored procedure make a difference if there's no
specific column limitation?

Regards,

Peter Carpenter.

 

[Peter Carpenter]

Hi everyone,

I found what was causing my problem & thought I'd post in case anyone
experiences the same problem.

My stored procedure had a "Print" statement in it that I'd been using
for debugging. When I commented this line out, it all worked
beautifully!!!

Regards,

Peter Carpenter.

 

[Cindy M -WordMVP-]

Hi Peter,

Thanks for getting back to us

I found what was causing my problem & thought I'd post in case anyone
experiences the same problem.

My stored procedure had a "Print" statement in it that I'd been using
for debugging. When I commented this line out, it all worked
beautifully!!!

<<original error message: error message from 'Microsoft
Word' stating 'Word was unable to open the data source.'>>

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.word.mvps.org

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