[Alex Hammerstein]

Hi

We have a number of Word letters that are the templates for Mail mereges
using MS Access as the datasource. However, we have recently had our
database tidied up, with object name being table consistent (tables all now
start tbl etc).

Of course this has upset our mail merges with the Word documents now un able
to find the data sources.

Is there an easier way to change the datasource, other than having to open
up each document in turn and relink, does anyone know?

Many thanks

Alex

[Doug Robbins - Word MVP]

If the name change was strictly consistent - that is just the prepending of
tbl to each table, and no other changes, it should be possible to use vba
code do prepend tbl to the name of the datasource for each of the merged
letters by processing them as a batch.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Alex Hammerstein" wrote in message
...
Hi

We have a number of Word letters that are the templates for Mail mereges
using MS Access as the datasource. However, we have recently had our
database tidied up, with object name being table consistent (tables all
now
start tbl etc).

Of course this has upset our mail merges with the Word documents now un
able
to find the data sources.

Is there an easier way to change the datasource, other than having to open
up each document in turn and relink, does anyone know?

Many thanks

Alex

[Alex Hammerstein]

Hi Doug

Thanks for your post

How do I get to the vba code of the file? Sorry if that seems a dumb
question

Alex



On 21/01/2009 19:39, in article , "Doug
Robbins - Word MVP" wrote:

If the name change was strictly consistent - that is just the prepending of
tbl to each table, and no other changes, it should be possible to use vba
code do prepend tbl to the name of the datasource for each of the merged
letters by processing them as a batch.

[Doug Robbins - Word MVP]

If you place a copy of all of the mailmerge main documents in a folder by
themselves (in the following code, that is assumed to be a folder named
"Test" on the C: drive, and then you run the following macro, it should
modify the file so that the data source would be a table with the same name
as the original with tbl prepended to it:

Sub BatchUpdateDataSource()
Option Explicit

Dim myFile As String
Dim PathToUse As String
Dim myDoc As Document
Dim sourcename As String
Dim sourcetable As String

'Set the path to the folder containing the documents
PathToUse = "C:\Test\"

'Close all open documents before beginning
Documents.Close SaveChanges:=wdPromptToSaveChanges

'Set the directory and type of file to batch process
myFile = Dir$(PathToUse & "*.doc")

While myFile ""
Set myDoc = Documents.Open(PathToUse & myFile)
With myDoc.MailMerge
'Get the name of the existing data source (database path and name)
sourcename = .DataSource.Name
'Get the name of the database table that contains the records
sourcetable = .DataSource.TableName
'Modify the name by prepending tbl to it.
sourcetable = Left(sourcetable, 15) & "tbl" & Mid(sourcetable, 16)
'Attach the modified datasource to the document
.OpenDataSource Name:=sourcename _
, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="",
_
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False,
_
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" &
sourcename & ";Mode=Read; _
Extended Properties="""";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path=""""; _
Jet OLEDB:Engine Type=5;Jet OLEDBatabase Locking Mode=0;Jet
OLEDB:Global Part" _
, SQLStatement:=sourcetable, SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess
End With
'Close the modified document after saving changes
myDoc.Close SaveChanges:=wdSaveChanges
'Next file in folder
myFile = Dir$()
Wend

End Sub

Note that the following section of the above

.OpenDataSource Name:=sourcename _
, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="",
_
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False,
_
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=" &
sourcename & ";Mode=Read; _
Extended Properties="""";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path=""""; _
Jet OLEDB:Engine Type=5;Jet OLEDBatabase Locking Mode=0;Jet
OLEDB:Global Part" _
, SQLStatement:=sourcetable, SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess

is based on the use of Office 2007. If you are using an earlier version of
Office, you should use the macro recorder to record the action of attaching
a table in a data base to a mail merge main document and then substitute the
code so recorder for the above, replacing the path and database name where
is appears in that code with "sourcename" as it appears above replacing the
"SELECT * FROM `tablename`" with sourcetable as it appears above.

If you do not know what to do with the above, see the article "What do I do
with macros sent to me by other newsgroup readers to help me out?" at:

http://www.word.mvps.org/FAQs/Macros...eateAMacro.htm





--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Alex Hammerstein" wrote in message
...
Hi Doug

Thanks for your post

How do I get to the vba code of the file? Sorry if that seems a dumb
question

Alex



On 21/01/2009 19:39, in article ,
"Doug
Robbins - Word MVP" wrote:

If the name change was strictly consistent - that is just the prepending
of
tbl to each table, and no other changes, it should be possible to use vba
code do prepend tbl to the name of the datasource for each of the merged
letters by processing them as a batch.

[Peter Jamieson]

1. Which version(s) of Word/Access?

2. Are all your mail merge main documents in .doc format? Or .docx
format? Or a mix?

3. Do the original data sources still exist? (i.e. does the same
..mdb/.accdb with the original table names still exist). If they do
exist, are they in the same database as the new ones?

4. Are all your data sources Access tables? Are some Access queries? Do
you use other non-Access data sources?

5. Are all your connections to Access the same type (e.g. all OLE DB,
all ODBC, all DDE)? Or is there a mix?

6. Have you applied any filter/sort criteria to the data sources? (Or do
you have mail merge main documents that have been user-created where you
could not possibly know without looking first?

7. All the above potentially have an impact on what would be necessary
to automate changes to your mail merge main documents. For example, if
the original data source no longer exists, you will not be able to open
the exiting mail merge main document without having to respond to one or
more dialog boxes and potentially losing the original data source
information.

8. One thing that might help in some cases (I'm not sure) if you are
using the same .mdb/.accdb file as before would be to create a Query
that has the same name as the original table name (if you have already
removed those old table names).


Peter Jamieson

http://tips.pjmsn.me.uk

Alex Hammerstein wrote:
Hi

We have a number of Word letters that are the templates for Mail mereges
using MS Access as the datasource. However, we have recently had our
database tidied up, with object name being table consistent (tables all now
start tbl etc).

Of course this has upset our mail merges with the Word documents now un able
to find the data sources.

Is there an easier way to change the datasource, other than having to open
up each document in turn and relink, does anyone know?

Many thanks

Alex