How to maintain the link between Word and Access after a merge?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

mljames - 25 Feb 2004 00:53 GMT

I have a document that needs to be able to re-query it's merge
information without losing any changes that may have been made to th
non-merged parts. The initial merge is driven from Access with th
following code fragment:

Code
-------------------

With .ActiveDocument.MailMerge
.OpenDataSource Name:=CurrentDb.Name, ConfirmConversions:=False, _
ReadOnly:=True, linkToSource:=True, Connection:=strSource, _ ' strSource = "DSN=DSNName"
SQLStatement:=strDocQuery ' strDocQuery = "SELECT * FROM qryWordMerge WHERE ID = 5
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
.Execute
End With

-------------------

I need Word (both 2000 and XP, with an option on 2003) to be able t
maintain the information about the link to Access so that when a use
re-opens the document, they will be prompted (or automatically) b
allowed to re-merge the data without going back to the template. Also
the solution needs to be flexible enough that I can set it up once an
efficiently pass it around the office to everyone else without havin
to do something on each machine.

If anyone has any ideas, I'd be very grateful!

Thanks,
Matt Jame

Reply to this Message

Peter Jamieson - 26 Feb 2004 10:03 GMT

First, all your MERGEFIELD fields (and useful stuff such as bookmarks) are
always going to be removed by the Merge. So you probably need to think about
how you're going to get any merge fields back inthere if a second merge is
going to be useful to anyone.

If all you want to do is to set up exactly the same data source in the
merged document as you are setting up for the original mail merge main
document, then you would need something like:

Dim oMMMD As Word.Document
Set oMMMD = .ActiveDocument
With oMMMD.MailMerge
.OpenDataSource _
Name:=CurrentDb.Name, _
ConfirmConversions:=False, _
ReadOnly:=True,
linkToSource:=True,
Connection:=strSource, _
SQLStatement:=strDocQuery
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
.Execute
End With
oMMMD.Close SaveChanges:=wdDoNotSaveChanges
Set oMMMD = Nothing
' At this point the newly created document is the Active document.
With .ActiveDocument.MailMerge
.OpenDataSource _
Name:=CurrentDb.Name, _
ConfirmConversions:=False, _
ReadOnly:=True,
linkToSource:=True,
Connection:=strSource, _
SQLStatement:=strDocQuery
End With

However,
a. I'm not sure I have understood exactly what you do want.
b. You may already have one thing (the DSN) that needs to be copied to each
machine, i.e. would undermine...

> Also,
> the solution needs to be flexible enough that I can set it up once and
> efficiently pass it around the office to everyone else without having
> to do something on each machine.

If the user needs to be able to use macros in Word after the merge, you
could either
a. distribute a template with the macros instead of a .doc, and create a
new .doc based on the template in your Access macro. In that case, you would
probably need to ensure that the template is not actually attached to a data
source before you distribute it. Or
b. put any macros you want in a module in the document, e.g. called
"mymacros", and use the following bit of code before you close oMMMD:

Application.OrganizerCopy _
Source:=oMMMD.FullName, _
Destination:=ActiveDocument.FullName, _
Name:="mymacros", _
Object:=wdOrganizerObjectProjectItems

(I don't know which versions of Word that works in).

--
Peter Jamieson - Word MVP
Word MVP web site http://word.mvps.org/

> I have a document that needs to be able to re-query it's merged
> information without losing any changes that may have been made to the
[quoted text clipped - 30 lines]
> ------------------------------------------------
> ~~ Message posted from http://www.WordForums.com/

Reply to this Message