We do a lot with mail merge documents. We have over 400 mail merge main
documents created in Word 2000 using MS Access as the data source. The
Access database needs to be moved to a different server. When this happens,
we will need to relink / update the data source for all of these documents.
Is there an automated or faster way to do this?

The prospect of having to manually update the data source for 400+ documents
is not one I am looking forward to.

Thanks in advance!

In theory, you should be able to write some Word VBA to open each document
(assuming they are all in known or easily found locations) and modify the
location of the data source. However, the real problem is that you probably
then have to open every merge document and do at least one test merge to
verify that everything is working as you expect. And of course in all
probability no single person knows what all 400 merges is supposed to do,
and the merge documents may be scattered ver many workstations etc. Also, if
any of the merges relies on VBA/VB etc. then you would need to inspect any
existing code and work out what changes are required.

I would probably approach this as follows:
a. whatever you do, do not remove the old server before this conversion is
performed, manually or otherwise. If you do, Word will display an error
message every time you try to open one of these merge documents.
b. write a Word VBA macro that looks at as many as possible of the Word
documents that use this/these Access database(s) as a dtaa source, and lists
details for each document. The key information comes from

ActiveDocument.MailMerge.DataSource.Name
ActiveDocument.MailMerge.DataSource.ConnectString
ActiveDocument.MailMerge.DataSource.QueryString

In all cases, the name should contain a full path name to the Access
database - my guess is that they are all .mdb files. What the ConnectString
contains depends on how the Word document has been connected to the Access
database. In Word 2000, there are two possibilities:
- DDE (the default) in which case Access is required on the user's
workstation, because Word starts a copy of Access which opens the database.
In this case, the ConnectString will typically contain something like "TABLE
[tablename] or QUERY [queryname]
- ODBC, in which case Access is not required on the Workstation, but an
ODBC DSN (Data Source Name) is required. In this case, the existing DSN is
/probably/ OK, but if someone has created a special DSN for these merge
documents, it, and the ConnectString, may need to be modified to point to
the new server. It is also possble that there is other connection
information such as username, password
c. make a copy of the database (or one of the databases, if there are
several) on the new server. If you can't do that (e.g. because the new
server is not available) all you can probably do is practise, e.g. by using
another existing server if available.
d. take one of the merge documents and try changing the data source to the
new one, manually in the first instance, and programmatically if that goes
OK. This is to check that no new security or networking problems have been
introduced by the change.
e. Programmatically, what you really need to do is grab the existing Name,
ConnectString, and QueryString, modify anything that refers to the old
database so that it refers to the new one, then issue an OpenDataSource
statement to open the new data source.
f. Once you have worked out how to update the data source programmatically
for one document, you can try doing the same thing for all 400. If you are
lucky, all 400 will work (you might be able to remove/rename the /original/
source at some point to verify that the documents are pointing to the new
source). Or maybe 350 will work and the 50 that don't will have to be
converted by hand...

Peter Jamieson

"Elbert H." Elbert wrote in message
...
We do a lot with mail merge documents. We have over 400 mail merge main
documents created in Word 2000 using MS Access as the data source. The
Access database needs to be moved to a different server. When this
happens,
we will need to relink / update the data source for all of these
documents.
Is there an automated or faster way to do this?

The prospect of having to manually update the data source for 400+
documents
is not one I am looking forward to.

Thanks in advance!

Peter:

I was independently coming to the conclusion that your suggestion might be
the only one available to us. I greatly appreciate your detailed response!
You were very thorough in your steps. I will give this a try and post back
on my success or failure.

Again, thank you ...

Elbert


"Peter Jamieson" wrote:

In theory, you should be able to write some Word VBA to open each document
(assuming they are all in known or easily found locations) and modify the
location of the data source.

snip

f. Once you have worked out how to update the data source programmatically
for one document, you can try doing the same thing for all 400. If you are
lucky, all 400 will work (you might be able to remove/rename the /original/
source at some point to verify that the documents are pointing to the new
source). Or maybe 350 will work and the 50 that don't will have to be
converted by hand...

Peter Jamieson

"Elbert H." Elbert wrote in message
...
We do a lot with mail merge documents. We have over 400 mail merge main
documents created in Word 2000 using MS Access as the data source. The
Access database needs to be moved to a different server. When this
happens,
we will need to relink / update the data source for all of these
documents.
Is there an automated or faster way to do this?

The prospect of having to manually update the data source for 400+
documents
is not one I am looking forward to.

Thanks in advance!

I doubt if I was as thorough in my steps as you will need to be :-) but I
wish you luck and if you are able to post your experiences back here I am
sure they will be very useful to other readers.

Best regards,

Peter Jamieson

"Elbert H." Elbert wrote in message
...
Peter:

I was independently coming to the conclusion that your suggestion might be
the only one available to us. I greatly appreciate your detailed
response!
You were very thorough in your steps. I will give this a try and post
back
on my success or failure.

Again, thank you ...

Elbert


"Peter Jamieson" wrote:

In theory, you should be able to write some Word VBA to open each
document
(assuming they are all in known or easily found locations) and modify the
location of the data source.

snip

f. Once you have worked out how to update the data source
programmatically
for one document, you can try doing the same thing for all 400. If you
are
lucky, all 400 will work (you might be able to remove/rename the
/original/
source at some point to verify that the documents are pointing to the new
source). Or maybe 350 will work and the 50 that don't will have to be
converted by hand...

Peter Jamieson

"Elbert H." Elbert wrote in message
...
We do a lot with mail merge documents. We have over 400 mail merge
main
documents created in Word 2000 using MS Access as the data source. The
Access database needs to be moved to a different server. When this
happens,
we will need to relink / update the data source for all of these
documents.
Is there an automated or faster way to do this?

The prospect of having to manually update the data source for 400+
documents
is not one I am looking forward to.

Thanks in advance!