Data source not "sticking"

[Kenneth]

Howdy,

I run Word 2002 SP3 under XP...

I have a number of templates that were created as MailMerge
docs that were then saved as dot files.

The problem is that when I open the templates, I now see an
error telling me that the dot needs a data source.

I then connect it to the data source, and save the template,
but when I next open it, the same error appears. So, saving
the data file association has no effect on preserving it.

This is happening with many similar templates, and all of
them "broke" at the same time.

What might cause this, and how can I get their mailmerge
data file associations to "stick" when I save them?

Many thanks,

 

[Peter Jamieson]

A number of people have reported this kind of problem over the years but
unfortunately I have never got to the bottom of it. There were
definitely problems at one point with some types of data source
disappearing if you specified sort/filter options. Otherwise, assuming
that you have not moved or renamed your data sources, some possible
"explanations" are that
a. there is a new security check (e.g. if your data source is on a
network drive, or even on a local hard disk
b. something truncates the connection string stored in the .dot
c. there is a problem with both .dot and the derived .doc opening the
same data source.

There can also be a problem as follows:
a. you /have/ moved your data source
b. when Word cannot find it, it prompts you to look for it
c. you locate and reconnect
d. you save your mailmerge main document

In some cases Word does not seem to update the connection information
and the same problem then occurs when you next open the document. /That/
problem can sometimes be avoided if you first disconnect the data source
altogether (e.g. by changing the mail merge man document type back to
"Normal Word Document), /then/ connect the data source and save again.
Perhaps worth trying that with one of your .dot files.

But it would be useful to know the details of your data source: what
type of data source, where is it located, have you specified any
query/filter options (also, do your .dots still have separate header
sources?

Peter Jamieson
http://tips.pjmsn.me.uk

 

[Kenneth]

On Wed, 10 Jun 2009 08:25:24 +0100, Peter Jamieson

Hi Peter,

I very much appreciate your comments...

Please see my responses inline below:

A number of people have reported this kind of problem over the years but
unfortunately I have never got to the bottom of it. There were
definitely problems at one point with some types of data source
disappearing if you specified sort/filter options.

I have no sorting, or filtering in place.

Here is the context:

I have all of our data stored in a database ('happens to be
Paradox.)

In order to take advantage of the printing-related
functionality available in Word (but not in Paradox) I have
a variety of applets in Paradox that all operate in the same
general way -

They pull data from our tables, organize them appropriately,
dump to txt files, and then call Word macros to print from
those data. The macro then closes Word, and focus returns to
Paradox.

We use this process for our invoicing, seminar welcome
letters, label printing etc.

Otherwise, assuming
that you have not moved or renamed your data sources, some possible
"explanations" are that

The data have not been moved, or renamed.

a. there is a new security check (e.g. if your data source is on a
network drive, or even on a local hard disk

Our data are on a network drive (but have been all along...)

Hmmm... Things just got more interesting:

I have experimented with this every way I could try, but the
connection to the datasource simply could not be saved.

Then, moments ago, I tried copying the data source to a new
local location.

With that, the data connection to the template could be
saved successfully.

So, should there be no other way, I do now have a
workaround, but it would require my re-writing a significant
amount of code, and I would certainly prefer not to do that,

b. something truncates the connection string stored in the .dot
c. there is a problem with both .dot and the derived .doc opening the
same data source.

There can also be a problem as follows:
a. you /have/ moved your data source
b. when Word cannot find it, it prompts you to look for it
c. you locate and reconnect
d. you save your mailmerge main document

In some cases Word does not seem to update the connection information
and the same problem then occurs when you next open the document. /That/
problem can sometimes be avoided if you first disconnect the data source
altogether (e.g. by changing the mail merge man document type back to
"Normal Word Document), /then/ connect the data source and save again.
Perhaps worth trying that with one of your .dot files.

I tried that, but it did not work (until, as I describe
above, I moved the datasource.)

But it would be useful to know the details of your data source: what
type of data source, where is it located, have you specified any
query/filter options (also, do your .dots still have separate header
sources?

The datasource is always a txt file. When located on the
lan, the connection to the dot fails. When local, it
succeeds. There is no query, or filtering. None of the dots
have a separate header source.

And, I will add one other thing:

I believe that this may be some sort of macro security
related problem because when using the automated procedures
I have described, I started seeing a dialog that said (in
effect) you are about to call a SQL command, do you wish to
continue?"

I would click "OK" and the automated process would move to
completion.

But, to eliminate the interruption, I lowered the macro
security level.

Hmmm (again)... The problem is solved!

I just went in to Word Options | Security to check the level
to which I had set macro security.

I had it set to "Low" as I recalled, but then noticed the
"Trusted Sources" tab for the first time.

In there, I saw that "Trust access to Visual Basic project"
was not checked.

I did check it (though I don't remember ever having seen it
before) and that solved the problem.

I am posting all this in the hope that it might assist
someone else, and again, offer you my sincere thanks,

 

[Kenneth]

On Wed, 10 Jun 2009 08:25:24 +0100, Peter Jamieson

Hi Peter,

I very much appreciate your comments...

Please see my responses inline below:



I have no sorting, or filtering in place.

Here is the context:

I have all of our data stored in a database ('happens to be
Paradox.)

In order to take advantage of the printing-related
functionality available in Word (but not in Paradox) I have
a variety of applets in Paradox that all operate in the same
general way -

They pull data from our tables, organize them appropriately,
dump to txt files, and then call Word macros to print from
those data. The macro then closes Word, and focus returns to
Paradox.

We use this process for our invoicing, seminar welcome
letters, label printing etc.


The data have not been moved, or renamed.



Our data are on a network drive (but have been all along...)

Hmmm... Things just got more interesting:

I have experimented with this every way I could try, but the
connection to the datasource simply could not be saved.

Then, moments ago, I tried copying the data source to a new
local location.

With that, the data connection to the template could be
saved successfully.

So, should there be no other way, I do now have a
workaround, but it would require my re-writing a significant
amount of code, and I would certainly prefer not to do that,



I tried that, but it did not work (until, as I describe
above, I moved the datasource.)


The datasource is always a txt file. When located on the
lan, the connection to the dot fails. When local, it
succeeds. There is no query, or filtering. None of the dots
have a separate header source.

And, I will add one other thing:

I believe that this may be some sort of macro security
related problem because when using the automated procedures
I have described, I started seeing a dialog that said (in
effect) you are about to call a SQL command, do you wish to
continue?"

I would click "OK" and the automated process would move to
completion.

But, to eliminate the interruption, I lowered the macro
security level.

Hmmm (again)... The problem is solved!

I just went in to Word Options | Security to check the level
to which I had set macro security.

I had it set to "Low" as I recalled, but then noticed the
"Trusted Sources" tab for the first time.

In there, I saw that "Trust access to Visual Basic project"
was not checked.

I did check it (though I don't remember ever having seen it
before) and that solved the problem.

I am posting all this in the hope that it might assist
someone else, and again, offer you my sincere thanks,

Hi again,

Well, it appears that I was a bit hasty...

The setting I found indeed has eliminated the problem with
many of the templates, but not all.

There is one that still will not "take" the network
datasource when saved.

Based upon my description, might you have any further
thoughts on this?

Many thanks, as before,

 

[Peter Jamieson]

1. Just out of interest, do you actually have VBA code in these templates?
And if so, does it actually establish the connection to the data source? I
am interested because of the possibility that you have to trust VBA even if
there is no obvious reason to do so.

2. Re. the SQL message, you may not be aware of the following KB article:

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

There is one that still will not "take" the network
datasource when saved.

3. If this template has exactly the same VBA as the others, I'd probably try
to avoid pinning the problem down and
a. create a new template
b. ensure that that connects OK
c. copy/paste the main document content into the new template, fix as
necessary
d. see if that works.

If however, the VBA is different,
a. does it have an OpenDataSource somewher, or set <a document
object>.Mailmerge.Datasource.Querystring?
b. could the code be modified to be the same as the templates that work, at
least in any area to do with Opendatasource etc.
c. or can you post enough code to let us know what the differences are?

Peter Jamieson

 

[Kenneth]

On Thu, 11 Jun 2009 08:46:03 +0100, "Peter Jamieson"

Hi again Peter,

Please see my responses inline below...

1. Just out of interest, do you actually have VBA code in these templates?
And if so, does it actually establish the connection to the data source? I
am interested because of the possibility that you have to trust VBA even if
there is no obvious reason to do so.

No, there is no code in the templates. They were created
primarily for their formatting. So, for example, we might
have one for each of a variety of labels that we print. We
have one for our invoices etc.

The templates are mailmerge dots.

All of the code was in individual macros. And those are
called by the code in the database.

So, for example, we have an invoice template, and an
associated Word macro.

When we fire code in the database, it pulls the appropriate
data from our tables, writes it all to a txt file, launches
Word, and then runs the invoice macro.

That macro creates a new document using the invoice
template. Because the dot is used for mailmerge, the new doc
populates with the appropriate txt file data, and then
prints to the appropriate printer.

2. Re. the SQL message, you may not be aware of the following KB article:

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

I will check that out...

3. If this template has exactly the same VBA as the others, I'd probably try
to avoid pinning the problem down and
a. create a new template
b. ensure that that connects OK
c. copy/paste the main document content into the new template, fix as
necessary
d. see if that works.

The one remaining problem template was like the others in
that it had no VBA code.

But, based on my past experimentation, I have corrected the
problem:

I modified the database code to create the txt file on the
local system rather than on our server.

I then opened the Word dot file, and connected it to the new
local datasource.

With that modification, the process runs properly.

If however, the VBA is different,
a. does it have an OpenDataSource somewher, or set <a document
object>.Mailmerge.Datasource.Querystring?
b. could the code be modified to be the same as the templates that work, at
least in any area to do with Opendatasource etc.
c. or can you post enough code to let us know what the differences are?

Many thanks as before,

 

[Peter Jamieson]

Thanks for the feedback, Kenneth. It's an area that probably deserves a bit
more digging around.

Peter Jamieson

 

[Kenneth]

Thanks for the feedback, Kenneth. It's an area that probably deserves a bit
more digging around.

Hi Peter,

Well, I've been diggin', and it appears that there is
something more to these problems.

I thought I had found a reasonable workaround in that I
simply moved the datafiles to be local to each machine, and
with that, everything seemed to be working fine.

But, I just stumbled into what I believe to be a related
problem, and at this point, I can't seem to resolve it:

I have a DOT file for a type of label. It is a simple
mailmerge file and connects to a TXT file that had
originally been on the lan, but is now local.

Now that it is local, it connects to its datafile as it
should (that's the good news), but the particular label I
need to print has in its datafile an upper ASCII character.

When I try to use my process to print the label a dialog
opens with the heading "File Conversion."

It says "Select the encoding that makes your document
readable."

When I first saw this, I cancelled the procedure, and opened
the offending DOT file.

Again, it opened the dialog I have just described, and I
selected Windows (Default) and clicked OK.

At that point, the text looked as it should in the Preview,
and I could manually print it properly.

I saved the template.

But the settings did not "stick."

When I next open the DOT manually, or next attempt to use it
to create the label using my automated procedure, it goes to
the same File Conversion dialog.

All this seems eerily similar to my earlier problems with
the DOT's defined datasource failing to stick.

At this point I can't find a workaround and I would
certainly welcome any suggestions you might have,

 

[Peter Jamieson]

I have been looking around, but there are a lot of possibilities to test,
and things look different even since the last time I looked.

First, as far as I am aware, when the data source is a text file and you
have to specify the encoding, Word does not store the information about the
encoding when it saves the mail merge main document. Therefore, you will
always be prompted for the encoding /if/ Word has reason to believe that the
text file is not using the default encoding specified on your system
(typically, when you installed Windows) or the encoding specified in a
defaultCPG entry in the registry (although that entry is not documented for
use with Word 2007, it does seem to work). From a dev. perspective, to be
sure that users would not see this dialog I think you would have to be in a
position to specify DefaultCPG in a standard way for all users, specify it
as something that can cope with "everything", (e.g. UTF-8), and generate all
your text files to conform to that standard. (No, I haven't tried it).

Alternatives - other than might be
a. to generate a different type of file whose encoding is "built in". The
only formats likely to do that for you would be Word formats. To generate
them directly, you'd probably have to write .rtf or WordProcessing/Word2007
XML format (i.e. not the sort of simple XML format doucment you might
produce by exporting from Access, but a complete Word document with the data
in delimited or tabular format, built using WordProcessingML or Word2007
XML. Frankly, I wouldn't wish either of those two options on anyone. But you
could also consider
- saving as text in a known encoding such as UTF8
- then opening the file in Word using Automation - in that case you should
be able to specify "encoded text" and UTF8 as the encoding.
Or you could even automate Word and create a document (say, containing a
table) with your data.

b. to use a different method to open the text file that wold recognise the
encoding. Unfortunately, that is also fraught with difficulty. The only way
I know how to do it is to create a .odc file, and ensure that the text file
is listed in a schema.ini file in the same folder as the text file, with the
delimiter character and encoding specified. There are a cople of wys to do
that, but you will find for example that the number of fields is limited to
255/256, stuff such as multiline fields may not work, etc. etc.

(Not sure this is relevant since you are I think using old-style .dots,
but...

Finally, not long ago I had a look at the XML that Word generates when it
saves a mail merge main document in a Word 2007 format. Word can store the
data source info in more than one way, and in some cases you seem to get the
problem with the data source not reconnecting. I haven't got to the bottom
of this yet (and may never do so), but in general things seem to go better
when Word defines a "relationship" element that specifies the data source
file name. Curiously, when connecting with text files via OLE DB, Word
maintains two lots of XML data about the connection ("odso" data (ODSO is
the Office Data Source object used to open many types of data source), and
non-"odso" data). In both cases all the data points to temp files, not the
original file, whose details are stored (twice) in relationship elements. In
the case where the data source /was/ an ODBC data source in an older version
of Word, if you "save as" one of these formats, you may end up with no
relationship element and no other source file information except the info.
embedded in the connection string and SQL query string. Word really doesn't
seem to like that.

)

Peter Jamieson

 

[Kenneth]

I have been looking around, but there are a lot of possibilities to test,
and things look different even since the last time I looked.

First, as far as I am aware, when the data source is a text file and you
have to specify the encoding, Word does not store the information about the
encoding when it saves the mail merge main document. Therefore, you will
always be prompted for the encoding /if/ Word has reason to believe that the
text file is not using the default encoding specified on your system
(typically, when you installed Windows) or the encoding specified in a
defaultCPG entry in the registry (although that entry is not documented for
use with Word 2007, it does seem to work). From a dev. perspective, to be
sure that users would not see this dialog I think you would have to be in a
position to specify DefaultCPG in a standard way for all users, specify it
as something that can cope with "everything", (e.g. UTF-8), and generate all
your text files to conform to that standard. (No, I haven't tried it).

Alternatives - other than might be
a. to generate a different type of file whose encoding is "built in". The
only formats likely to do that for you would be Word formats. To generate
them directly, you'd probably have to write .rtf or WordProcessing/Word2007
XML format (i.e. not the sort of simple XML format doucment you might
produce by exporting from Access, but a complete Word document with the data
in delimited or tabular format, built using WordProcessingML or Word2007
XML. Frankly, I wouldn't wish either of those two options on anyone. But you
could also consider
- saving as text in a known encoding such as UTF8
- then opening the file in Word using Automation - in that case you should
be able to specify "encoded text" and UTF8 as the encoding.
Or you could even automate Word and create a document (say, containing a
table) with your data.

b. to use a different method to open the text file that wold recognise the
encoding. Unfortunately, that is also fraught with difficulty. The only way
I know how to do it is to create a .odc file, and ensure that the text file
is listed in a schema.ini file in the same folder as the text file, with the
delimiter character and encoding specified. There are a cople of wys to do
that, but you will find for example that the number of fields is limited to
255/256, stuff such as multiline fields may not work, etc. etc.

(Not sure this is relevant since you are I think using old-style .dots,
but...

Finally, not long ago I had a look at the XML that Word generates when it
saves a mail merge main document in a Word 2007 format. Word can store the
data source info in more than one way, and in some cases you seem to get the
problem with the data source not reconnecting. I haven't got to the bottom
of this yet (and may never do so), but in general things seem to go better
when Word defines a "relationship" element that specifies the data source
file name. Curiously, when connecting with text files via OLE DB, Word
maintains two lots of XML data about the connection ("odso" data (ODSO is
the Office Data Source object used to open many types of data source), and
non-"odso" data). In both cases all the data points to temp files, not the
original file, whose details are stored (twice) in relationship elements. In
the case where the data source /was/ an ODBC data source in an older version
of Word, if you "save as" one of these formats, you may end up with no
relationship element and no other source file information except the info.
embedded in the connection string and SQL query string. Word really doesn't
seem to like that.

)

Peter Jamieson

Hi Peter,

I appreciate your detailed suggestions...

I strongly suspect that something has changed in my Word
installation, though at the moments, I have no idea what
that might be.

Right now, I'm working on this from "the other end" that is,
from the database side.

It may be possible that there is a driver that would allow
my database to create TXT files that are UTF-8. If so, that
would be an easy next step.

Right now, the TXT files that I create are 'ascii' ANSI.
And, they open properly in Notepad in that they display
accented characters properly.

A few years ago, I had some sort of difficulty with accented
characters (again, printing with Word macros) and I was
advised to use the 'ascii' ANSI language driver. When I used
it, all my problems vanished (Well, not ALL, but at least
those related to printing...)

Until about a week ago, Word was happy with these TXT files,
but now, with no (intended) modification, Word doesn't like
the TXT files, and it is also losing the datasource
associations for files on the lan. Both of these worked
smoothly with no user intervention...

This is a strange one, but I should have my current question
about a new driver for the TXT files answered shortly.

I'll be back when I learn more,

 

[Kenneth]

Hi Peter,

I appreciate your detailed suggestions...

I strongly suspect that something has changed in my Word
installation, though at the moments, I have no idea what
that might be.

Right now, I'm working on this from "the other end" that is,
from the database side.

It may be possible that there is a driver that would allow
my database to create TXT files that are UTF-8. If so, that
would be an easy next step.

Right now, the TXT files that I create are 'ascii' ANSI.
And, they open properly in Notepad in that they display
accented characters properly.

A few years ago, I had some sort of difficulty with accented
characters (again, printing with Word macros) and I was
advised to use the 'ascii' ANSI language driver. When I used
it, all my problems vanished (Well, not ALL, but at least
those related to printing...)

Until about a week ago, Word was happy with these TXT files,
but now, with no (intended) modification, Word doesn't like
the TXT files, and it is also losing the datasource
associations for files on the lan. Both of these worked
smoothly with no user intervention...

This is a strange one, but I should have my current question
about a new driver for the TXT files answered shortly.

I'll be back when I learn more,

Hi again Peter,

I have experimented further, and have learned a bit more.

First, my database cannot produce TXT files that are UTF-8
compatible.

The closest I can come is apparently what Paradox calls
'ascii' ANSI, and, until recently, that has worked just fine
in partnership with Word.

Now, as I have explained, the Word File Conversion dialog
opens, and when it does, it is set to Japanese-JIS. If I
manually reset it to Windows(Default) all looks fine.

Next, I thought to rework the Word macro that I use to do
the printing. I know close to nothing about the Word
programming language but always was able to create the
macros I need by recording, and tweaking the result.

I tried to record a new macro including my setting the File
Conversion choice to Windows (Default) but apparently, that
setting would not be recorded.

So, as of now, I am stuck.

What might you suggest as my next step?

Many thanks, as before,

 

[Kenneth]

Hi again Peter,

I have experimented further, and have learned a bit more.

First, my database cannot produce TXT files that are UTF-8
compatible.

The closest I can come is apparently what Paradox calls
'ascii' ANSI, and, until recently, that has worked just fine
in partnership with Word.

Now, as I have explained, the Word File Conversion dialog
opens, and when it does, it is set to Japanese-JIS. If I
manually reset it to Windows(Default) all looks fine.

Next, I thought to rework the Word macro that I use to do
the printing. I know close to nothing about the Word
programming language but always was able to create the
macros I need by recording, and tweaking the result.

I tried to record a new macro including my setting the File
Conversion choice to Windows (Default) but apparently, that
setting would not be recorded.

So, as of now, I am stuck.

What might you suggest as my next step?

Many thanks, as before,

Hi again,

Some more information that I neglected to include:

The Japanese conversion should have been "Japanese
Shift-JIS" in my previous post.

Next, the particular character I am having trouble with is
Alt-0233, an acute accented lower case e.

Also, when I am in the Word File Conversion dialog, and
choose UTF-8 the accented e does not appear properly: The
accented e simply vanishes (and it is not replaced with a
space.)

When I choose UTF-7, the text looks as it should with the
accented e.

Of course, it would appear that all this could be resolved
if I could find a way to tell Word to always use the Windows
(Default) to convert a TXT file (whether with a setting
somewhere, or with code in the macros.)

As before, thanks for any further help,

 

[Kenneth]

On Sat, 20 Jun 2009 17:13:07 -0400, Kenneth

Hi again Peter,

I found a line of VBA code on the MS site that may lead me
to a way to open the dot file with a particular conversion
of the associated TXT file:

Format:=wdOpenFormatText, Encoding:=msoEncodingUSASCII

I have not experimented with it yet, but will let you know
of any results,

 

[Kenneth]

On Sat, 20 Jun 2009 17:13:07 -0400, Kenneth

Hi again Peter,

I found a line of VBA code on the MS site that may lead me
to a way to open the dot file with a particular conversion
of the associated TXT file:

Format:=wdOpenFormatText, Encoding:=msoEncodingUSASCII

I have not experimented with it yet, but will let you know
of any results,

Hi Peter,

I may have the problem solved... (but, I do remember having
said that already.)

The apparent solution to the file conversion problem is in
MSKB290981.

It is a reg hack that allows setting the default conversion
of TXT files.

Interestingly, in the example provided in the article, it
provides the designation for Windows Latin-1 only.

I tried that conversion manually, and it worked, so I made
the registry modification, and, at least for my initial
testing, all looks fine.

Many thanks for all your help,

 

[Peter Jamieson]

Yes, that's interestring because I mentioend the DefaultCPG setting earlier
but I couldn't actually work out which default and actual encodings you were
using that would cause the specific problem you described and therefore
potentially allow it to be solved.

The main reason I suggested that you use UTF-8 for that setting is that then
you are ready for almost any encoding issue on this front.

Peter Jamieson