[BradH]

Hi,
we have a mail merge template setup that when opened will show the prompt
"Opening this document will run the following SQL command:", and then when
"Yes" is clicked will import the data source for the merge.

We now need to change the name of the data source that it imports, so I've
tried opening the template, changing the data source and then saving as a
template again. But whenever I do this the SQL prompt that should come up
when it's opened no longer appears, and the data source is no longer
imported automatically.

Can someone suggest how I can change the data source correctly without
losing the SQL prompt when the template is opened?

Thanks.

[Peter Jamieson]

1. Which version of Word?

2. What is the data source?

3. Just as a guess, have you tried disconnecting your template from the
data source as a separate step before connecting to the data source you
want? (Word can do something unexpected if you don't)

4. Just in case there is a misunderstanding, changing the data source in
the /template/ won't change the data source in documents that have
already been created from the template - the template and the document
have separate connections to the data source (which may cause other
problems in some cases)


Peter Jamieson

http://tips.pjmsn.me.uk

BradH wrote:
Hi,
we have a mail merge template setup that when opened will show the
prompt "Opening this document will run the following SQL command:", and
then when "Yes" is clicked will import the data source for the merge.

We now need to change the name of the data source that it imports, so
I've tried opening the template, changing the data source and then
saving as a template again. But whenever I do this the SQL prompt that
should come up when it's opened no longer appears, and the data source
is no longer imported automatically.

Can someone suggest how I can change the data source correctly without
losing the SQL prompt when the template is opened?

Thanks.

[BradH]

Hi Peter,

1. Word 2007

2. Data source is a TXT file

3. Just as you posted this I tried doing that and I think that's fixed my
problem. If once opened I change it to a "Normal Word Document", and then
start the step by step mail merge again from scratch, I got it to work with
the files stored locally on my hard drive.
But, in our production environment it needs to be able to pick up the data
file from a mapped network drive. I've tried linking it to the drive where
it needs to pick it up from but it won't work for some reason. I don't get
the SQL prompt and the document opens with no data source. However if I
place the file on a different network drive that one seems to work. Has got
me stumped for the moment, but I guess this is more a problem with our
network instead of with the actual template.

4. Yes I realise this. I just need it changed for future mail merges.

Thanks for you help.


"Peter Jamieson" wrote in message
...
1. Which version of Word?

2. What is the data source?

3. Just as a guess, have you tried disconnecting your template from the
data source as a separate step before connecting to the data source you
want? (Word can do something unexpected if you don't)

4. Just in case there is a misunderstanding, changing the data source in
the /template/ won't change the data source in documents that have already
been created from the template - the template and the document have
separate connections to the data source (which may cause other problems in
some cases)


Peter Jamieson

http://tips.pjmsn.me.uk

BradH wrote:
Hi,
we have a mail merge template setup that when opened will show the prompt
"Opening this document will run the following SQL command:", and then
when "Yes" is clicked will import the data source for the merge.

We now need to change the name of the data source that it imports, so
I've tried opening the template, changing the data source and then saving
as a template again. But whenever I do this the SQL prompt that should
come up when it's opened no longer appears, and the data source is no
longer imported automatically.

Can someone suggest how I can change the data source correctly without
losing the SQL prompt when the template is opened?

Thanks.

[Peter Jamieson]

FWIW, I find Microsoft's approach to file security increasingly
mystifying and have yet to pin down what exactly you have to do, but it
may be worth checking that
a. the share, the underlying folder and the file itself (and perhasp
even the maped drive letter - that would be a twist I haven't
invesitaged yet) are all accessible to the user opening the file
b. you can open the file via IE (which has an additional layer of
security which seems to be applied by some software)

Peter Jamieson

http://tips.pjmsn.me.uk

BradH wrote:
Hi Peter,

1. Word 2007

2. Data source is a TXT file

3. Just as you posted this I tried doing that and I think that's fixed
my problem. If once opened I change it to a "Normal Word Document", and
then start the step by step mail merge again from scratch, I got it to
work with the files stored locally on my hard drive.
But, in our production environment it needs to be able to pick up the
data file from a mapped network drive. I've tried linking it to the
drive where it needs to pick it up from but it won't work for some
reason. I don't get the SQL prompt and the document opens with no data
source. However if I place the file on a different network drive that
one seems to work. Has got me stumped for the moment, but I guess this
is more a problem with our network instead of with the actual template.

4. Yes I realise this. I just need it changed for future mail merges.

Thanks for you help.


"Peter Jamieson" wrote in message
...
1. Which version of Word?

2. What is the data source?

3. Just as a guess, have you tried disconnecting your template from
the data source as a separate step before connecting to the data
source you want? (Word can do something unexpected if you don't)

4. Just in case there is a misunderstanding, changing the data source
in the /template/ won't change the data source in documents that have
already been created from the template - the template and the document
have separate connections to the data source (which may cause other
problems in some cases)


Peter Jamieson

http://tips.pjmsn.me.uk

BradH wrote:
Hi,
we have a mail merge template setup that when opened will show the
prompt "Opening this document will run the following SQL command:",
and then when "Yes" is clicked will import the data source for the
merge.

We now need to change the name of the data source that it imports, so
I've tried opening the template, changing the data source and then
saving as a template again. But whenever I do this the SQL prompt
that should come up when it's opened no longer appears, and the data
source is no longer imported automatically.

Can someone suggest how I can change the data source correctly
without losing the SQL prompt when the template is opened?

Thanks.

[Peter Jamieson]

A little bit more info. not sure whether it will help or simply confuse:

1. When I open a .txt file on a network share as a data source in Word
2007 SP1 (I'm using Vista 32-bit here)
a. even if I specify the "Confirm conversions at open" within Word, I
do not get the "Text converter" option - only OLE DB and ODBC as a
maximum. This is AFAIK standard Word 2007
b. if I open using OLE DB, then use VBA to do this:

With ActiveDocument.MailMerge.DataSource
Debug.Print .Name
Debug.Print .ConnectString
Debug.Print .QueryString
Debug.Print .TableName
End With

..name is the UNC pathname of the file
..connectstring is an ACE OLE DB provider connection string that points
to nmy temp folder
..querystring contains (say) SELECT * FROM `txt48A80.txt`
..tablename contains (say) SELECT * FROM `txt4EC40.txt`

I often wondered why there was a .tablename as well as a .querystring
because in all other circumstances both strings seem to be the same.

When I look at my temp folder, it seems that Word has made a copy of the
file in Name, given it the name in .tablename, and is using that as the
data source.

If I now save and close my mail merge main document and re-open it, I am
given a SQL prompt that references the name in .querystring. If I
accept, Word actually seems to re-download, creates a new temp file, and
puts it in .tablename (i.e. the one in .querystring never actually seems
to be used, but does not change either). Word may also prompt for the
spearator character. It does, however, re-open the file successfully.

If instead I open the file using VBA and try to open it using the text
converter (providing just the name parameter to OpenDataSource may be
enough) then

..name is the UNC pathname of the file
..querystring is SELECT * FROM the UNC pathname of the file
..tablename is the same as .querystring.

All works OK until I save/close/reopen the document. Then I get a
message that quotes some sort of temp name for the Mail Merge Main
Document and says that the data source cannot be found (the correct UNC
name for the text file is given. Locating the data source changes
nothing unless I change the connection method to say (OLE DB).

However, if I add the UNC path name, suitably prefaced by file:// or
whatever, to the list of Trusted locations in Internet Explorer, this
problem goes away.

So although this security option does not appear to affect .txt files
opened as data source using OLE DB (the default), it may be worth
checking whether, for some reason, one of your network paths is listed
in the Trusted Locations and another is not.

Since I only run Word 2007 under Vista I have not been able to determine
to what extent these problems are down to Word and to what extent they
are down to Vista. However
a. it seems to me that Word/Office now has at least three separate
layers of so-called "security" when it comes to opening files - Windows
file security; Office trusted locations;IE Trusted Locations. I expect
someone at Microsoft may understand how it's all supposed to fit
together but it's certainly not obvious to me.
b. I get the impression that Vista has a timing problem with file
locking that may also affect these outcomes. For example, Windows
Explorer does not always appear to be able to "see" recently created or
recently closed files, suggesting that Microsoft has turned to the "lazy
write" approach to updating the disk, or something like that.


Peter Jamieson

http://tips.pjmsn.me.uk

Peter Jamieson wrote:
FWIW, I find Microsoft's approach to file security increasingly
mystifying and have yet to pin down what exactly you have to do, but it
may be worth checking that
a. the share, the underlying folder and the file itself (and perhasp
even the maped drive letter - that would be a twist I haven't
invesitaged yet) are all accessible to the user opening the file
b. you can open the file via IE (which has an additional layer of
security which seems to be applied by some software)

Peter Jamieson

http://tips.pjmsn.me.uk

BradH wrote:
Hi Peter,

1. Word 2007

2. Data source is a TXT file

3. Just as you posted this I tried doing that and I think that's fixed
my problem. If once opened I change it to a "Normal Word Document",
and then start the step by step mail merge again from scratch, I got
it to work with the files stored locally on my hard drive.
But, in our production environment it needs to be able to pick up the
data file from a mapped network drive. I've tried linking it to the
drive where it needs to pick it up from but it won't work for some
reason. I don't get the SQL prompt and the document opens with no
data source. However if I place the file on a different network drive
that one seems to work. Has got me stumped for the moment, but I
guess this is more a problem with our network instead of with the
actual template.

4. Yes I realise this. I just need it changed for future mail merges.

Thanks for you help.


"Peter Jamieson" wrote in message
...
1. Which version of Word?

2. What is the data source?

3. Just as a guess, have you tried disconnecting your template from
the data source as a separate step before connecting to the data
source you want? (Word can do something unexpected if you don't)

4. Just in case there is a misunderstanding, changing the data source
in the /template/ won't change the data source in documents that have
already been created from the template - the template and the
document have separate connections to the data source (which may
cause other problems in some cases)


Peter Jamieson

http://tips.pjmsn.me.uk

BradH wrote:
Hi,
we have a mail merge template setup that when opened will show the
prompt "Opening this document will run the following SQL command:",
and then when "Yes" is clicked will import the data source for the
merge.

We now need to change the name of the data source that it imports,
so I've tried opening the template, changing the data source and
then saving as a template again. But whenever I do this the SQL
prompt that should come up when it's opened no longer appears, and
the data source is no longer imported automatically.

Can someone suggest how I can change the data source correctly
without losing the SQL prompt when the template is opened?

Thanks.