Persistant Data Source in reusable documents

[kit]

How do I create a reusable mail merge document which remembers the file name
of the data source and automatically opens the data source file whenever the
document is opened?

 

[Doug Robbins - Word MVP]

Save the mail merge main document with the data source attached to it. If
you do that, when you open the document, you may then get a "
"Opening This Will Run the Following SQL Command" Message.

You can just click on OK or Yes and continue, or, if you do not want the
message to appear, you can follow the work around that is contained in the
Knowledge Base article on the following webpage

http://support.microsoft.com?kbid=825765


Note, the data source will not be "opened" when you open the mail merge main
document, but it will be attached to that document, so that you can
immediately execute the merge.

Having said that, there are some issues with the data source not being
retained, particularly when the data source is located on a server with a
lengthy file path.

If you are having that problem, here is some information provided by a
colleague, Peter Jamieson who has done a fair bit of research into the
problem:

the main reasons why Word may have difficulty /re-/opening
a data source are
a. In Word 2002 there is a problem where the data source is lost if you
apply a filter or sort criteria to your data source. This may have been
fixed in a later SP, and I believe was fixed in Word 2003.
b. the connection information saved by Word when you close a working mail
merge main document is truncated in such a way that Word loses essential
information about the location of the data source

For example, by default Word 2002 and later use OLE DB providers to open as
many types of data source as possible, including Excel worksheets, Access
databases and plain text files. The OLE DB provider typically divides the
location of a data source into a "database" and a "table". So for example,
if the data source is...
a. ...an Access table, the "database" is the Access .mdb file that contains
all the data, and the table is a table or query within that .mdb
b. ...a text file, the "database" is the Windows folder that contains the
text file, and the .txt file itself is the "table"
c. ...an Excel worksheet, the "database" is the Excel workbook, and the
"table" is a worksheet, a named range, or perhaps a range specified in R1C1
format.


An application such as Word that uses OLE DB to get data usually specifies
the database part of the data source's locatoin in a /Connection string/,
and specifies the "table" part either simply by naming the table, or
specifying a SQL query that names the table.


So what goes wrong in Word? Well, Word constructs a connection string
containing whatever path name is required to specify the "database", and
uses it to open the document. But then when you save the Word document, it
truncates the connection string to 255 characters long. If the pathname of
the database file or folder is so long that it spans that 255 boundary, Word
in effect forgets where the database it.


What can you do about it? Well, unfortunately, you cannot shorten the
connection string by leaving out unnecessary information. Word always
includes certain properties even when they are not strictly necessary. So
the only things you can do are
a. use another method to make the connection (and every method has its
drawbacks - see e.g. http://tips.pjmsn.me.uk/t0003.htm for a discussion of
some of the issues surrounding connections to Excel files, for example)
b. give your data source a shorter name, or put it in a folder with a
shorter pathname, depending on exactly what typ eof data source it is.


There can in theory be other problems that would cause this problem, but in
most of those cases you would be unlikely to connect at all.



--
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

 

[kit]

Save the mail merge main document with the data source attached to it. If
you do that, when you open the document, you may then get a "
"Opening This Will Run the Following SQL Command" Message.

You can just click on OK or Yes and continue, or, if you do not want the
message to appear, you can follow the work around that is contained in the
Knowledge Base article on the following webpage

I case I didn't mention it, I'm using Word 2007 with SP1 MSO installed.

When I do that I get:-

BE7A6A82.docx is a mail merge main document. Word cannot find its data
source, Y:\xxxx_data.csv

and when I re-locate document (it is there) and select, it comes back with
the above message. The data source filename will be exactly that length each
and every time.

http://support.microsoft.com?kbid=825765

Can this be done automatically as part of the document? Otherwise, I have to
apply this to each and every users computer and some of them are located at
remote locations and they're not technical.

 

[Peter Jamieson]

There are a couple of other things to look out for in this area (I think
they are mentioned in the same thread as the message you quoted):

1. Some people have the problem described in

http://support.microsoft.com/kb/834699

2. What happens in some cases is this:
a. you open the Word document
b. Word cannot find the data source, so it prompts you (find it, or the
data source)
c. you find the data source.
d. you probably save the mail merge main document to try to save the new
data source location.

Unfortunately, (d) does not always work as you would expect. Word seems
to retain the original daa source information and to treat the new data
source as "temporary".

One way to save the document with the correct data source is to detach
it from its data source, e.g. using the mail merge toolbar in Word 2003
to select the mail merge document type "Normal Word Document" - in 2007,
it's in one of the dropdowns in the Mailings tab. Then reconnect. You
will lose sorts and filters, and you have obviously lost the document
type, but you do not lose the field codes. Set up the data source how
you want it, then save the mail merge main document. Awkward, but I
believe it works. (You can also use VBA
Activedocument.mailMerge.DataSource.Close in Word 2003/2007 to achieve a
similar effect without losing the document type).