Updating SQL Server 2K DB after MailMerge

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Derek Nishino - 28 Dec 2003 05:36 GMT

Hi there, I need to update my SQL Server 2000 DB after a MailMerge
completes to mark the records in the DB as merged and printed. I'm
having problems figuring out how to do this programatically. In
particular I can't find anything in the Word 2003 Object Model that
let's me execute an update to the DB. I am using Visual Studio .Net
2003 with the Visual Studio Tools for Office 2003 and have written the
rest of the automation in C#.

I'm not sure if this will matter but the way I set up the MailMerge was
by creating a new document and running through the MailMerge Wizard (via
the Task Pane) and then saving the document as a template. I then wrote
code in VS .Net 2003 to show the MailMerge Wizard and set the
WizardState to 6. Now I'm stuck on how to update the DB.

In the meantime I've written code to step through the MailMerge records
and display their unique ID's in a MessageBox so we can manually update
the DB via another C# app I threw together. This method works but is
not particularly slick since the only way I could find to manipulate
each record was by setting ThisDocument.MailMerge.DataSource.
ActiveRecord = Word.wdMailMergeActiveRecord.wdNextRecord in a do while
loop which ends up taking a long time even with only a few records since
Word appears to refresh the MailMerge every time I set the ActiveRecord.

I'm hoping that someone out there can help me out since I've been
banging my head against this thing for a while now and it's driving me
crazy.

Thanks,

Derek Nishino

P.S. To reply to me via email remove the 'NOSPAM' string from the
address.

Reply to this Message

Peter Jamieson - 28 Dec 2003 09:52 GMT

As far as I know you cannot use the Mailmerge object to update the DB. You
need to open a separate connection using e.g. ADO and do the updates using
that. You could either do the updates altogether at the end of the merge or
possibly within an appropriate Mail Merge event, depending on what VST
permits and what makes sense from a data integrity/consistency point of
view. The main reason for updating the data source record-by-record would (I
suppose) be in the case where you want to allow a user to select/deselect
individual records for merge. Otherwise, I would have thought you could use
the same query as the Mailmerge to retrieve and update the set of records
used for the merge.

--
Peter Jamieson - Word MVP

> Hi there, I need to update my SQL Server 2000 DB after a MailMerge
> completes to mark the records in the DB as merged and printed. I'm
[quoted text clipped - 29 lines]
> P.S. To reply to me via email remove the 'NOSPAM' string from the
> address.

Reply to this Message

Derek Nishino - 29 Dec 2003 05:17 GMT

Thanks for the info Peter! I had tried to do this before without success.
After I saw this post I went back and figured out why I was having problems
making a separate connection to the DB. I was using SqlConnection when the
connect string from the mail merge was using OleDBConnection. So once I
realized that, I was able to make the new connection without any problems.
Thank you very much for your help!

I have one other question. I'm using a do while loop to step through the
records using ThisDocument.MailMerge.DataSource.ActiveRecord = Word.
WdMailMergeActiveRecord.WdNextRecord in order to update the DB. The problem
with this method is that every time I reassign .ActivRecord the mail merge
query refreshes which takes a second or two. This causes increasing delays
as the data set I'm working with grows. Do you know if there is another way
to access the mail merge records without using .ActiveRecord?

Thanks!

Derek Nishino

>As far as I know you cannot use the Mailmerge object to update the DB. You
>need to open a separate connection using e.g. ADO and do the updates using
[quoted text clipped - 43 lines]
>> P.S. To reply to me via email remove the 'NOSPAM' string from the
>> address.

Reply to this Message

Peter Jamieson - 29 Dec 2003 09:34 GMT

> I have one other question. I'm using a do while loop to step through the
> records using ThisDocument.MailMerge.DataSource.ActiveRecord = Word.
[quoted text clipped - 3 lines]
> as the data set I'm working with grows. Do you know if there is another way
> to access the mail merge records without using .ActiveRecord?

Only the one I suggsted earlier - if you were using VBA there's a Word
MailMerge event that will fire either before or after each record is merged.
I imagine these events are available when you are using the VS, but I don't
know off the top of my head. The main problem with this approach is that if
multiple records are being consumed for each "pass" of the merge (e.g. if
you are using { NEXT } fields, doing label merges (same thing) etc.) you
will probably still need to have a way to identify which records to mark.

--
Peter Jamieson - Word MVP

> Thanks for the info Peter! I had tried to do this before without success.
> After I saw this post I went back and figured out why I was having problems
[quoted text clipped - 62 lines]
> >> P.S. To reply to me via email remove the 'NOSPAM' string from the
> >> address.

Reply to this Message

Derek Nishino - 30 Dec 2003 23:55 GMT

Thanks Peter! I'll give that a shot.

Derek Nishino

>> I have one other question. I'm using a do while loop to step through the
>> records using ThisDocument.MailMerge.DataSource.ActiveRecord = Word.
[quoted text clipped - 102 lines]
>> >> P.S. To reply to me via email remove the 'NOSPAM' string from the
>> >> address.

Reply to this Message

Rate this thread: