Modifying Mail Merge Code

[Chaplain Doug]

Word 2003. I have some mailmerge documents that point to Access database
queries. How can I get at the actual code in the Word document that points
to the database and query (the SQL statement)? The location of the database
has changed and I do not want to have to go through the entire "Find data
source" routine to get the Word document pointed at the database and query in
its new location. I suspect there must be a way, although I have tried
looking at the code behind these Word documents and find nothing there.
Where is this SQL being stored for the document? Thanks for the help.

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?Q2hhcGxhaW4gRG91Zw==?=,

Word 2003. I have some mailmerge documents that point to Access database
queries. How can I get at the actual code in the Word document that points
to the database and query (the SQL statement)? The location of the database
has changed and I do not want to have to go through the entire "Find data
source" routine to get the Word document pointed at the database and query in
its new location. I suspect there must be a way, although I have tried
looking at the code behind these Word documents and find nothing there.
Where is this SQL being stored for the document?

It's stored deep in the binary file format. There's no way to get at it "from
the outside" unless the file has been saved in Word's XML format or round-trip
HTML.

Theoretically, you should be able to record a macro to re-attach these documents
to a datasource, but the data source needs to be attached in order to view the
connection and query information. Catch-22. If you don't know it, and the
documents are saved as *.doc in Word's binary file format, then you have to open
the documents to get at it.

If you open the documents and the data source can't be found, you have to start
over and the SQL is lost.

The only thing that can work would be to duplicate the database in its original
location, then make the changes to the new location. After that, the duplicate
can be removed.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
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

 

[nate]

One way you can do it is to do a "select all" on your page.
and copy and paste that into a new document.
Effectively keeping the mail merge fields but removing the connecting info.

Then in your VBA code within the Document.open() function put something like
this:
objWordDoc.MailMerge.OpenDataSource _
Name:=Me.Path & "\yourDBname.mdb", _
LinkToSource:=True, _
Connection:="QUERY qryPresentationsSummary", _
SQLStatement:="SELECT * FROM [qryPresentationsSummary]"
' Execute the mail merge.
Me.MailMerge.Destination = 0
Me.MailMerge.Execute


I am now having issues with a nested situation of that which I will post
into another topic as well.
when you do an insert -> DATABASE and use a mailmerge field inside of the
text that you use for the DATABASE connection. I need to use the me.path in
that as well but don't know how to replace that text.
I know I can access that DATABASE text via:
ActiveDocument.Fields(2).Code
For example if I make a button and in the click event
put a msgbox(ActiveDocument.Fields(2).Code)
it will show me what is being used in the DATABASE query.
But when I try to fill a string var with the correct code and stick that
string into the ActiveDocument.Fields(2).Code object via:
ActiveDocument.Fields(2).Code = tempstr
it errors. maybe because I am running it inside the document.open() func.
if not here then where do I run it? I can't do it manually via buttons etc.
this is all suppose to be an automated process.