[SJMac]

Hello,

I'm an app programmer looking at a Word and Access Mail Merge issue - lost
and confused!

I've been asked to look at an Access DB application that has a custom input
form with a big table behind it. Related to that are a collection of Word
templates for form letters with lots of Merge Fields in.

The templates were originally created and used with Word 2000, but the
current environment is Office XP/Word 2002. I've been told that there used to
be a button in the toolbar called "Merge" that prompted the user to type in a
case number to identify the DB row to merge, but that is missing without
trace right now.

When I open the Word templates I get warnings that some SQL is about to be
executed - that's OK, but I can't find that SQL in the VBA editor, or any of
the document properties for the templates. Where should I be looking? I'd
like to change the SQL, or delete it -- how can I do that?

No data is inserted in to the templates after I open them. Why is SQL being
executed for the template? Surely the data normally needs to be retrieved for
the actual documents, or when I do the merge?

Additionally, the SQL described in the warning is a search for a particular
row from Access. If I click the "Merge to new document" button while editing
the template, then every entry in the table is merged, so what was the point
of the SQL?

When I create a new document based on a template, there does not appear to
be any data source defined, and the Merge toolbar is hidden, so I need to do
a few clicks to get that on the screen -- how can I keep that on the screen
by default?

My goal is to allow the user to create a form letter based on a single entry
in the DB with as few clicks as possible. I think that I need to start by
getting some good defaults in the template (like the correct data source),
carrying that default over to the docs created from the template, and also
having the Merge toolbar visible to the user when they are looking at a Merge
document.

Any clues will be gratefully recieved ...

Steven

[Peter Jamieson]

I suggest you use Google groups to have a good look through this newsgroup -
some of the answers to your questions are repeated many times (but others
are not).

be a button in the toolbar called "Merge" that prompted the user to type
in a

In which application? Access or Word?

When I open the Word templates I get warnings that some SQL is about to be
executed - that's OK, but I can't find that SQL in the VBA editor, or any
of
the document properties for the templates. Where should I be looking? I'd
like to change the SQL, or delete it -- how can I do that?

This prompt is displayed every time you open a Mail Merge Main document
connected to a data source (well, there may be circumstances in which it is
not displayed, but they are oddities). The prompt can be suppressed by
changing the Windows registry - see

http://support.microsoft.com/default...b;en-us;825765

The "SQL" is created when the user connects the mail merge main document to
a data source, and is saved with the document and re-issued when it is
opened, along with a suitable connection string etc. You can't really delete
it, since there is always some "SQL" - that's simply how Word codifies its
queries. The connection may have been made programmatically using Word's
OpenDataSource method, and the SQL may have been modified by changing
ActiveDocument.MailMerge.DataSource.QueryString. You can find out (roughly)
what Word is trying to issue, after it has successfully opened the data
source, by printing the values of

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

No data is inserted in to the templates after I open them. Why is SQL
being
executed for the template? Surely the data normally needs to be retrieved
for
the actual documents, or when I do the merge?

When you create a new document based on a template that has a data source
attached, Word tries to connect tot he data source for both the template and
the new document. It is AFAIK trying to check the connection information,
but is also trying to retrieve data to be used in the merge preview and to
let the user select records using Word's Select Recipients dialog box.

As far as I can tell, in Word 2000 and earlier, Word did not try to open one
connection for the document and another for the attached template, but in
Word 2002/2003 it generally does so, and that can cause problems if the data
source is not a multi-user data source, or is opened using a method that
insists on exclusive access. Frankly, you don't get much control over the
exclusivity, read-only-ness of a Word connection to a data source using
OpenDataSource and its connection string - in some cases, Word appears just
to use whatever settings it likes in that area.

Additionally, the SQL described in the warning is a search for a
particular
row from Access. If I click the "Merge to new document" button while
editing
the template, then every entry in the table is merged, so what was the
point
of the SQL?

It sounds like you are in efect creating a new mailmerge main document with
a new data source at this point.

My goal is to allow the user to create a form letter based on a single
entry
in the DB with as few clicks as possible. I think that I need to start by
getting some good defaults in the template (like the correct data source),
carrying that default over to the docs created from the template, and also
having the Merge toolbar visible to the user when they are looking at a
Merge
document.

If your application is fairly simple, i.e. you just need to stuff a few
values into Word from a selected recod in Access, I would avoid MailMerge
altogether as it's far too complicated. it's probably better to follow e.g.

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

(although I've just encountered someone with problems implementing that so
cannot be sure it will work for you)

Personally I do not have a good "pattern" you could follow, so assuming that
is what you need, I suggest you post again eithe rhere or in a suitable
Access conf. and ask for precisely that.

Peter Jamieson


"SJMac" wrote in message
...
Hello,

I'm an app programmer looking at a Word and Access Mail Merge issue - lost
and confused!

I've been asked to look at an Access DB application that has a custom
input
form with a big table behind it. Related to that are a collection of Word
templates for form letters with lots of Merge Fields in.

The templates were originally created and used with Word 2000, but the
current environment is Office XP/Word 2002. I've been told that there used
to
be a button in the toolbar called "Merge" that prompted the user to type
in a
case number to identify the DB row to merge, but that is missing without
trace right now.

When I open the Word templates I get warnings that some SQL is about to be
executed - that's OK, but I can't find that SQL in the VBA editor, or any
of
the document properties for the templates. Where should I be looking? I'd
like to change the SQL, or delete it -- how can I do that?

No data is inserted in to the templates after I open them. Why is SQL
being
executed for the template? Surely the data normally needs to be retrieved
for
the actual documents, or when I do the merge?

Additionally, the SQL described in the warning is a search for a
particular
row from Access. If I click the "Merge to new document" button while
editing
the template, then every entry in the table is merged, so what was the
point
of the SQL?

When I create a new document based on a template, there does not appear to
be any data source defined, and the Merge toolbar is hidden, so I need to
do
a few clicks to get that on the screen -- how can I keep that on the
screen
by default?

My goal is to allow the user to create a form letter based on a single
entry
in the DB with as few clicks as possible. I think that I need to start by
getting some good defaults in the template (like the correct data source),
carrying that default over to the docs created from the template, and also
having the Merge toolbar visible to the user when they are looking at a
Merge
document.

Any clues will be gratefully recieved ...

Steven

[SJMac]

"Peter Jamieson" wrote:

I suggest you use Google groups to have a good look through this newsgroup -

I had been doing that, but remained confused until I started recognising and
understanding the term "main merge document", and discovered that "detach
data source" corresponds with the Merge toolbar button "Main Document
SetupNormal Word Document".

The simple stuff :-(

be a button in the toolbar called "Merge" that prompted the user to type
in a
In which application? Access or Word?

Word - but I see that you suggest it's easier from the Access side of the
fence (below).

When I open the Word templates I get warnings that some SQL is about to be
executed - that's OK,
This prompt is displayed every time you open a Mail Merge Main document
connected to a data source (well, there may be circumstances in which it is
not displayed, but they are oddities). The prompt can be suppressed by
changing the Windows registry - see
http://support.microsoft.com/default...b;en-us;825765

Yup, I'd seen that, but was happy with the prompt temporarilly so that I
could tell that when there was still some embeded "automation".

Regarding the warning's security purpose, I regard the effects of data being
inserted into a document as harmless -- Merge can't insert malicious code can
it? Is everyone here happy to disable that warning?

The "SQL" is created when the user connects the mail merge main document to
a data source, and is saved with the document and re-issued when it is
opened, along with a suitable connection string etc. You can't really delete
it, since there is always some "SQL" - that's simply how Word codifies its
queries. The connection may have been made programmatically using Word's
OpenDataSource method, and the SQL may have been modified by changing
ActiveDocument.MailMerge.DataSource.QueryString. You can find out (roughly)
what Word is trying to issue, after it has successfully opened the data
source, by printing the values of
ActiveDocument.MailMerge.DataSource.Name
ActiveDocument.MailMerge.DataSource.ConnectString
ActiveDocument.MailMerge.DataSource.QueryString

V helpful, thanks. Right now, I'm imagining that some code that pops up an
input to allow the user to enter a row id, alters the QueryString, then does
the merge shouldn't be hard to do, and will hide the slightly tedious
MailMergeRecipients dialog from the user. I'll try ... is there a problem
you'd expect me to see?

No data is inserted in to the templates after I open them. Why is SQL
being
executed for the template? Surely the data normally needs to be retrieved
for
the actual documents, or when I do the merge?

When you create a new document based on a template that has a data source
attached, Word tries to connect tot he data source for both the template and
the new document. It is AFAIK trying to check the connection information,
but is also trying to retrieve data to be used in the merge preview and to
let the user select records using Word's Select Recipients dialog box.

OK, that makes sense.

As far as I can tell, in Word 2000 and earlier, Word did not try to open one
connection for the document and another for the attached template, but in
Word 2002/2003 it generally does so, and that can cause problems if the data
source is not a multi-user data source, or is opened using a method that
insists on exclusive access. Frankly, you don't get much control over the
exclusivity, read-only-ness of a Word connection to a data source using
OpenDataSource and its connection string - in some cases, Word appears just
to use whatever settings it likes in that area.

Interesting.

Additionally, the SQL described in the warning is a search for a
particular
row from Access. If I click the "Merge to new document" button while
editing
the template, then every entry in the table is merged, so what was the
point
of the SQL?

It sounds like you are in efect creating a new mailmerge main document with
a new data source at this point.

Yes, perhaps I did. I can't repeat this now that I know what I'm doing!

My goal is to allow the user to create a form letter based on a single
entry
in the DB with as few clicks as possible. I think that I need to start by
getting some good defaults in the template (like the correct data source),
carrying that default over to the docs created from the template, and also
having the Merge toolbar visible to the user when they are looking at a
Merge
document.

If your application is fairly simple, i.e. you just need to stuff a few
values into Word from a selected recod in Access, I would avoid MailMerge
altogether as it's far too complicated. it's probably better to follow e.g.

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

What an interesting kb! That would be exactly what I want, except it's not
quite what the user had before.

BTW, the sample doesn't use Merge at all - that's odd isn't it?

Peter Jamieson

Peter, your reply has been very helpful, Thank you!

Steven

[Peter Jamieson]

Regarding the warning's security purpose, I regard the effects of data
being
inserted into a document as harmless -- Merge can't insert malicious code
can
it? Is everyone here happy to disable that warning?

This warning is a "scattergun" solution. The security problem is essentially
that when Word connects to a data source, it executes code that is not part
of Word, not necessarily part of Office, not necessarily supplied by MS, and
not necessarily bening. The mention of SQL is a bit of a red herring, but
for example if Word connects to Access via DDE and executes an Access query
containing user-defined functions (i.e. defined in Access VBA) those
functions may theoretically have significant side effects. But I could in
theory write a text converter (in fact I have), an OLEDB provider or ODBC
driver that takes the request "SELECT * FROM mytable" and actually wipes the
hard drive or whatever.

Now of course in an ideal world a properly configured system would have no
such dangerous drivers/providers, and Access and every other database would
have proper security controls so that the user+system administrator (if any)
can know that they are not going to do anything malicious when they execute
SQL code. In which case you could switch the warning off and forget about
it. I expect most people probably do anyway as they have to get their work
done and they cannot possibly tell whether the "SQL" they are about to
execute is malicious or not.

The unfortunate thing in this situation is that there are plenty of
connection types where it is difficult to see that any harm could arise
unless someone has modified Microsoft code. e.g. when Word connects to a
Word data source. In other cases I do not really see why the /Word/ user
should get a warning.

I'm not sure that answers your question but I suppose I would want to have
reasonable confidence that my system was safe (in this respect) before doing
so.

V helpful, thanks. Right now, I'm imagining that some code that pops up an
input to allow the user to enter a row id, alters the QueryString, then
does
the merge shouldn't be hard to do, and will hide the slightly tedious
MailMergeRecipients dialog from the user. I'll try ... is there a problem
you'd expect me to see?

Not unless you run into multi-user related problems, e.g. if a table is
locked while your form is displaying in Access, or the record you're looking
at has been added as part of a transaction that is incomplete and Word can't
see it.

/A/ reason to avoid using merge for many users is that a query that tries to
use Access Forms!formname!fieldname syntax (which works in Access) isn't
necessarily going to work - actually, I've never really investigated that
area. If however you are creating a query that hits the underlying
table/query, as you are proposing, you should be OK.

What an interesting kb! That would be exactly what I want, except it's not
quite what the user had before.

BTW, the sample doesn't use Merge at all - that's odd isn't it?

Not really - I think it's using the "simple" approach. I would saythat the
main advantages of using merge come into play if you are letting the users
modify the layout and content of the letters/documents they are producing.
In that case, there is a pre-existing UI (i.e. the various mailmerge tools)
which many users understand, at least to an extent, which allows them to
insert fields (and even nest them and so on), preview data, etc. However,
having a separate data source in the Word document complicates everything
because of
a. the SQL dialog we've discussed
b. multi-user considerations
c. the fact that if you move the data source, you run into problems if the
Word document has the old data source set up when you open it, etc.

(and probably other stuff I've forgotten).

Peter Jamieson


"SJMac" wrote in message
...


"Peter Jamieson" wrote:

I suggest you use Google groups to have a good look through this
newsgroup -

I had been doing that, but remained confused until I started recognising
and
understanding the term "main merge document", and discovered that "detach
data source" corresponds with the Merge toolbar button "Main Document
SetupNormal Word Document".

The simple stuff :-(

be a button in the toolbar called "Merge" that prompted the user to
type
in a
In which application? Access or Word?

Word - but I see that you suggest it's easier from the Access side of the
fence (below).

When I open the Word templates I get warnings that some SQL is about to
be
executed - that's OK,
This prompt is displayed every time you open a Mail Merge Main document
connected to a data source (well, there may be circumstances in which it
is
not displayed, but they are oddities). The prompt can be suppressed by
changing the Windows registry - see
http://support.microsoft.com/default...b;en-us;825765

Yup, I'd seen that, but was happy with the prompt temporarilly so that I
could tell that when there was still some embeded "automation".

Regarding the warning's security purpose, I regard the effects of data
being
inserted into a document as harmless -- Merge can't insert malicious code
can
it? Is everyone here happy to disable that warning?

The "SQL" is created when the user connects the mail merge main document
to
a data source, and is saved with the document and re-issued when it is
opened, along with a suitable connection string etc. You can't really
delete
it, since there is always some "SQL" - that's simply how Word codifies
its
queries. The connection may have been made programmatically using Word's
OpenDataSource method, and the SQL may have been modified by changing
ActiveDocument.MailMerge.DataSource.QueryString. You can find out
(roughly)
what Word is trying to issue, after it has successfully opened the data
source, by printing the values of
ActiveDocument.MailMerge.DataSource.Name
ActiveDocument.MailMerge.DataSource.ConnectString
ActiveDocument.MailMerge.DataSource.QueryString

V helpful, thanks. Right now, I'm imagining that some code that pops up an
input to allow the user to enter a row id, alters the QueryString, then
does
the merge shouldn't be hard to do, and will hide the slightly tedious
MailMergeRecipients dialog from the user. I'll try ... is there a problem
you'd expect me to see?

No data is inserted in to the templates after I open them. Why is SQL
being
executed for the template? Surely the data normally needs to be
retrieved
for
the actual documents, or when I do the merge?

When you create a new document based on a template that has a data source
attached, Word tries to connect tot he data source for both the template
and
the new document. It is AFAIK trying to check the connection information,
but is also trying to retrieve data to be used in the merge preview and
to
let the user select records using Word's Select Recipients dialog box.

OK, that makes sense.

As far as I can tell, in Word 2000 and earlier, Word did not try to open
one
connection for the document and another for the attached template, but in
Word 2002/2003 it generally does so, and that can cause problems if the
data
source is not a multi-user data source, or is opened using a method that
insists on exclusive access. Frankly, you don't get much control over the
exclusivity, read-only-ness of a Word connection to a data source using
OpenDataSource and its connection string - in some cases, Word appears
just
to use whatever settings it likes in that area.

Interesting.

Additionally, the SQL described in the warning is a search for a
particular
row from Access. If I click the "Merge to new document" button while
editing
the template, then every entry in the table is merged, so what was the
point
of the SQL?

It sounds like you are in efect creating a new mailmerge main document
with
a new data source at this point.

Yes, perhaps I did. I can't repeat this now that I know what I'm doing!

My goal is to allow the user to create a form letter based on a single
entry
in the DB with as few clicks as possible. I think that I need to start
by
getting some good defaults in the template (like the correct data
source),
carrying that default over to the docs created from the template, and
also
having the Merge toolbar visible to the user when they are looking at a
Merge
document.

If your application is fairly simple, i.e. you just need to stuff a few
values into Word from a selected recod in Access, I would avoid MailMerge
altogether as it's far too complicated. it's probably better to follow
e.g.

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

What an interesting kb! That would be exactly what I want, except it's not
quite what the user had before.

BTW, the sample doesn't use Merge at all - that's odd isn't it?

Peter Jamieson

Peter, your reply has been very helpful, Thank you!

Steven

[SJMac]

Peter,

Thanks again, your help has been very useful helping me to understand my
problem!

Not unless you run into multi-user related problems, e.g. if a table is
locked while your form is displaying in Access, or the record you're looking
at has been added as part of a transaction that is incomplete and Word can't
see it.

Which actions in Access would cause a table to be locked? I assume multiple
readers are OK? It looks like my user makes all changes to the DB via a
series of forms, rather than editing the tables directly.

I'm not (yet!) a VB Programmer, but can you give me any comments on the
Macro that I recorded and then edited?

I think I should at least validate that the user input is numeric, but I'm
not going to bother checking that it is valid record!

Sub MERGE()
'
' MERGE Macro
' Macro recorded 7/26/2006 by Steven Mackenzie
'

On Error GoTo ExitMerge
' ... otherwise error messages are output
' in to a new document

CaseRef = InputBox("Please type in the case reference number", "Case Ref")

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
.QueryString = .QueryString & " WHERE Ref = " & CaseRef
End With
.Execute Pause:=False
End With

Exit Sub

ExitMerge:
MsgBox Err.Description

End Sub




"Peter Jamieson" wrote:

Regarding the warning's security purpose, I regard the effects of data
being
inserted into a document as harmless -- Merge can't insert malicious code
can
it? Is everyone here happy to disable that warning?

This warning is a "scattergun" solution. The security problem is essentially
that when Word connects to a data source, it executes code that is not part
of Word, not necessarily part of Office, not necessarily supplied by MS, and
not necessarily bening. The mention of SQL is a bit of a red herring, but
for example if Word connects to Access via DDE and executes an Access query
containing user-defined functions (i.e. defined in Access VBA) those
functions may theoretically have significant side effects. But I could in
theory write a text converter (in fact I have), an OLEDB provider or ODBC
driver that takes the request "SELECT * FROM mytable" and actually wipes the
hard drive or whatever.

Now of course in an ideal world a properly configured system would have no
such dangerous drivers/providers, and Access and every other database would
have proper security controls so that the user+system administrator (if any)
can know that they are not going to do anything malicious when they execute
SQL code. In which case you could switch the warning off and forget about
it. I expect most people probably do anyway as they have to get their work
done and they cannot possibly tell whether the "SQL" they are about to
execute is malicious or not.

The unfortunate thing in this situation is that there are plenty of
connection types where it is difficult to see that any harm could arise
unless someone has modified Microsoft code. e.g. when Word connects to a
Word data source. In other cases I do not really see why the /Word/ user
should get a warning.

I'm not sure that answers your question but I suppose I would want to have
reasonable confidence that my system was safe (in this respect) before doing
so.

V helpful, thanks. Right now, I'm imagining that some code that pops up an
input to allow the user to enter a row id, alters the QueryString, then
does
the merge shouldn't be hard to do, and will hide the slightly tedious
MailMergeRecipients dialog from the user. I'll try ... is there a problem
you'd expect me to see?

Not unless you run into multi-user related problems, e.g. if a table is
locked while your form is displaying in Access, or the record you're looking
at has been added as part of a transaction that is incomplete and Word can't
see it.

/A/ reason to avoid using merge for many users is that a query that tries to
use Access Forms!formname!fieldname syntax (which works in Access) isn't
necessarily going to work - actually, I've never really investigated that
area. If however you are creating a query that hits the underlying
table/query, as you are proposing, you should be OK.

What an interesting kb! That would be exactly what I want, except it's not
quite what the user had before.

BTW, the sample doesn't use Merge at all - that's odd isn't it?

Not really - I think it's using the "simple" approach. I would saythat the
main advantages of using merge come into play if you are letting the users
modify the layout and content of the letters/documents they are producing.
In that case, there is a pre-existing UI (i.e. the various mailmerge tools)
which many users understand, at least to an extent, which allows them to
insert fields (and even nest them and so on), preview data, etc. However,
having a separate data source in the Word document complicates everything
because of
a. the SQL dialog we've discussed
b. multi-user considerations
c. the fact that if you move the data source, you run into problems if the
Word document has the old data source set up when you open it, etc.

(and probably other stuff I've forgotten).

Peter Jamieson


"SJMac" wrote in message
...


"Peter Jamieson" wrote:

I suggest you use Google groups to have a good look through this
newsgroup -

I had been doing that, but remained confused until I started recognising
and
understanding the term "main merge document", and discovered that "detach
data source" corresponds with the Merge toolbar button "Main Document
SetupNormal Word Document".

The simple stuff :-(

be a button in the toolbar called "Merge" that prompted the user to
type
in a
In which application? Access or Word?

Word - but I see that you suggest it's easier from the Access side of the
fence (below).

When I open the Word templates I get warnings that some SQL is about to
be
executed - that's OK,
This prompt is displayed every time you open a Mail Merge Main document
connected to a data source (well, there may be circumstances in which it
is
not displayed, but they are oddities). The prompt can be suppressed by
changing the Windows registry - see
http://support.microsoft.com/default...b;en-us;825765

Yup, I'd seen that, but was happy with the prompt temporarilly so that I
could tell that when there was still some embeded "automation".

Regarding the warning's security purpose, I regard the effects of data
being
inserted into a document as harmless -- Merge can't insert malicious code
can
it? Is everyone here happy to disable that warning?

The "SQL" is created when the user connects the mail merge main document
to
a data source, and is saved with the document and re-issued when it is
opened, along with a suitable connection string etc. You can't really
delete
it, since there is always some "SQL" - that's simply how Word codifies
its
queries. The connection may have been made programmatically using Word's
OpenDataSource method, and the SQL may have been modified by changing
ActiveDocument.MailMerge.DataSource.QueryString. You can find out
(roughly)
what Word is trying to issue, after it has successfully opened the data
source, by printing the values of
ActiveDocument.MailMerge.DataSource.Name
ActiveDocument.MailMerge.DataSource.ConnectString
ActiveDocument.MailMerge.DataSource.QueryString

V helpful, thanks. Right now, I'm imagining that some code that pops up an
input to allow the user to enter a row id, alters the QueryString, then
does
the merge shouldn't be hard to do, and will hide the slightly tedious
MailMergeRecipients dialog from the user. I'll try ... is there a problem
you'd expect me to see?

No data is inserted in to the templates after I open them. Why is SQL
being
executed for the template? Surely the data normally needs to be
retrieved
for
the actual documents, or when I do the merge?

When you create a new document based on a template that has a data source
attached, Word tries to connect tot he data source for both the template
and
the new document. It is AFAIK trying to check the connection information,
but is also trying to retrieve data to be used in the merge preview and
to
let the user select records using Word's Select Recipients dialog box.

OK, that makes sense.

As far as I can tell, in Word 2000 and earlier, Word did not try to open
one
connection for the document and another for the attached template, but in
Word 2002/2003 it generally does so, and that can cause problems if the
data
source is not a multi-user data source, or is opened using a method that
insists on exclusive access. Frankly, you don't get much control over the
exclusivity, read-only-ness of a Word connection to a data source using
OpenDataSource and its connection string - in some cases, Word appears
just
to use whatever settings it likes in that area.

Interesting.

Additionally, the SQL described in the warning is a search for a
particular
row from Access. If I click the "Merge to new document" button while
editing
the template, then every entry in the table is merged, so what was the
point
of the SQL?

It sounds like you are in efect creating a new mailmerge main document
with
a new data source at this point.

Yes, perhaps I did. I can't repeat this now that I know what I'm doing!

My goal is to allow the user to create a form letter based on a single
entry
in the DB with as few clicks as possible. I think that I need to start
by
getting some good defaults in the template (like the correct data
source),
carrying that default over to the docs created from the template, and
also
having the Merge toolbar visible to the user when they are looking at a
Merge
document.

If your application is fairly simple, i.e. you just need to stuff a few
values into Word from a selected recod in Access, I would avoid MailMerge
altogether as it's far too complicated. it's probably better to follow
e.g.

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

What an interesting kb! That would be exactly what I want, except it's not
quite what the user had before.

BTW, the sample doesn't use Merge at all - that's odd isn't it?

Peter Jamieson

Peter, your reply has been very helpful, Thank you!

Steven

[Peter Jamieson]

Which actions in Access would cause a table to be locked? I assume
multiple
readers are OK? It looks like my user makes all changes to the DB via a
series of forms, rather than editing the tables directly.

I don't know Access /that/ well - broadly speaking, if a user can have the
form you want open and can still run queries successfully on another app. or
machine, you are probably OK. Personally, I would probably want to see what
happens when the user has made an update or insertion from the form - can
another program then get the data? what data does it see? When experimenting
with Access one of the things that generally screws up Word connecting to it
is having a table /definition/ dialog box open, but of course that's
generally not going to happen in the sort of scenario you're describing.

As for your macro,

1. I agree about validating that the value is numeric. When you construct an
SQL statement programmatically, from a security perspective you should
always validate all the input in case the user has typed in something that
makes your SQL statement do something completely unexpected.

For example in this case supposing .QueryString has

SELECT * FROM mytable

and the user enters a value for Ref of "(SELECT xyz FROM someothertable
WHERE myvalue = 123)"

then your statement ends up as

SELECT * FROM mytable WHERE Ref=(SELECT xyz FROM someothertable WHERE
myvalue = 123)

OK, that's not "dangerous", but someone might well be able to think of
something that is.

2. If you execute this macro more than once, you will end up with a
statement like

SELECT * FROM mytable WHERE Ref=123 WHERE Ref=456

which won't work. You really need to save the .Querystring somewhere before
modifying it.

3. I would verify that changing the .Querystring always works the way you
expect. Sometimes I've found that it's necessary to issue an OpenDataSource
to change it, not necessarily with Access though.

Peter Jamieson

"SJMac" wrote in message
...
Peter,

Thanks again, your help has been very useful helping me to understand my
problem!

Not unless you run into multi-user related problems, e.g. if a table is
locked while your form is displaying in Access, or the record you're
looking
at has been added as part of a transaction that is incomplete and Word
can't
see it.

Which actions in Access would cause a table to be locked? I assume
multiple
readers are OK? It looks like my user makes all changes to the DB via a
series of forms, rather than editing the tables directly.

I'm not (yet!) a VB Programmer, but can you give me any comments on the
Macro that I recorded and then edited?

I think I should at least validate that the user input is numeric, but I'm
not going to bother checking that it is valid record!

Sub MERGE()
'
' MERGE Macro
' Macro recorded 7/26/2006 by Steven Mackenzie
'

On Error GoTo ExitMerge
' ... otherwise error messages are output
' in to a new document

CaseRef = InputBox("Please type in the case reference number", "Case
Ref")

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
.QueryString = .QueryString & " WHERE Ref = " & CaseRef
End With
.Execute Pause:=False
End With

Exit Sub

ExitMerge:
MsgBox Err.Description

End Sub




"Peter Jamieson" wrote:

Regarding the warning's security purpose, I regard the effects of data
being
inserted into a document as harmless -- Merge can't insert malicious
code
can
it? Is everyone here happy to disable that warning?

This warning is a "scattergun" solution. The security problem is
essentially
that when Word connects to a data source, it executes code that is not
part
of Word, not necessarily part of Office, not necessarily supplied by MS,
and
not necessarily bening. The mention of SQL is a bit of a red herring, but
for example if Word connects to Access via DDE and executes an Access
query
containing user-defined functions (i.e. defined in Access VBA) those
functions may theoretically have significant side effects. But I could in
theory write a text converter (in fact I have), an OLEDB provider or ODBC
driver that takes the request "SELECT * FROM mytable" and actually wipes
the
hard drive or whatever.

Now of course in an ideal world a properly configured system would have
no
such dangerous drivers/providers, and Access and every other database
would
have proper security controls so that the user+system administrator (if
any)
can know that they are not going to do anything malicious when they
execute
SQL code. In which case you could switch the warning off and forget about
it. I expect most people probably do anyway as they have to get their
work
done and they cannot possibly tell whether the "SQL" they are about to
execute is malicious or not.

The unfortunate thing in this situation is that there are plenty of
connection types where it is difficult to see that any harm could arise
unless someone has modified Microsoft code. e.g. when Word connects to a
Word data source. In other cases I do not really see why the /Word/ user
should get a warning.

I'm not sure that answers your question but I suppose I would want to
have
reasonable confidence that my system was safe (in this respect) before
doing
so.

V helpful, thanks. Right now, I'm imagining that some code that pops up
an
input to allow the user to enter a row id, alters the QueryString, then
does
the merge shouldn't be hard to do, and will hide the slightly tedious
MailMergeRecipients dialog from the user. I'll try ... is there a
problem
you'd expect me to see?

Not unless you run into multi-user related problems, e.g. if a table is
locked while your form is displaying in Access, or the record you're
looking
at has been added as part of a transaction that is incomplete and Word
can't
see it.

/A/ reason to avoid using merge for many users is that a query that tries
to
use Access Forms!formname!fieldname syntax (which works in Access) isn't
necessarily going to work - actually, I've never really investigated that
area. If however you are creating a query that hits the underlying
table/query, as you are proposing, you should be OK.

What an interesting kb! That would be exactly what I want, except it's
not
quite what the user had before.

BTW, the sample doesn't use Merge at all - that's odd isn't it?

Not really - I think it's using the "simple" approach. I would saythat
the
main advantages of using merge come into play if you are letting the
users
modify the layout and content of the letters/documents they are
producing.
In that case, there is a pre-existing UI (i.e. the various mailmerge
tools)
which many users understand, at least to an extent, which allows them to
insert fields (and even nest them and so on), preview data, etc. However,
having a separate data source in the Word document complicates everything
because of
a. the SQL dialog we've discussed
b. multi-user considerations
c. the fact that if you move the data source, you run into problems if
the
Word document has the old data source set up when you open it, etc.

(and probably other stuff I've forgotten).

Peter Jamieson


"SJMac" wrote in message
...


"Peter Jamieson" wrote:

I suggest you use Google groups to have a good look through this
newsgroup -

I had been doing that, but remained confused until I started
recognising
and
understanding the term "main merge document", and discovered that
"detach
data source" corresponds with the Merge toolbar button "Main Document
SetupNormal Word Document".

The simple stuff :-(

be a button in the toolbar called "Merge" that prompted the user to
type
in a
In which application? Access or Word?

Word - but I see that you suggest it's easier from the Access side of
the
fence (below).

When I open the Word templates I get warnings that some SQL is about
to
be
executed - that's OK,
This prompt is displayed every time you open a Mail Merge Main
document
connected to a data source (well, there may be circumstances in which
it
is
not displayed, but they are oddities). The prompt can be suppressed by
changing the Windows registry - see
http://support.microsoft.com/default...b;en-us;825765

Yup, I'd seen that, but was happy with the prompt temporarilly so that
I
could tell that when there was still some embeded "automation".

Regarding the warning's security purpose, I regard the effects of data
being
inserted into a document as harmless -- Merge can't insert malicious
code
can
it? Is everyone here happy to disable that warning?

The "SQL" is created when the user connects the mail merge main
document
to
a data source, and is saved with the document and re-issued when it is
opened, along with a suitable connection string etc. You can't really
delete
it, since there is always some "SQL" - that's simply how Word codifies
its
queries. The connection may have been made programmatically using
Word's
OpenDataSource method, and the SQL may have been modified by changing
ActiveDocument.MailMerge.DataSource.QueryString. You can find out
(roughly)
what Word is trying to issue, after it has successfully opened the
data
source, by printing the values of
ActiveDocument.MailMerge.DataSource.Name
ActiveDocument.MailMerge.DataSource.ConnectString
ActiveDocument.MailMerge.DataSource.QueryString

V helpful, thanks. Right now, I'm imagining that some code that pops up
an
input to allow the user to enter a row id, alters the QueryString, then
does
the merge shouldn't be hard to do, and will hide the slightly tedious
MailMergeRecipients dialog from the user. I'll try ... is there a
problem
you'd expect me to see?

No data is inserted in to the templates after I open them. Why is
SQL
being
executed for the template? Surely the data normally needs to be
retrieved
for
the actual documents, or when I do the merge?

When you create a new document based on a template that has a data
source
attached, Word tries to connect tot he data source for both the
template
and
the new document. It is AFAIK trying to check the connection
information,
but is also trying to retrieve data to be used in the merge preview
and
to
let the user select records using Word's Select Recipients dialog box.

OK, that makes sense.

As far as I can tell, in Word 2000 and earlier, Word did not try to
open
one
connection for the document and another for the attached template, but
in
Word 2002/2003 it generally does so, and that can cause problems if
the
data
source is not a multi-user data source, or is opened using a method
that
insists on exclusive access. Frankly, you don't get much control over
the
exclusivity, read-only-ness of a Word connection to a data source
using
OpenDataSource and its connection string - in some cases, Word appears
just
to use whatever settings it likes in that area.

Interesting.

Additionally, the SQL described in the warning is a search for a
particular
row from Access. If I click the "Merge to new document" button while
editing
the template, then every entry in the table is merged, so what was
the
point
of the SQL?

It sounds like you are in efect creating a new mailmerge main document
with
a new data source at this point.

Yes, perhaps I did. I can't repeat this now that I know what I'm doing!

My goal is to allow the user to create a form letter based on a
single
entry
in the DB with as few clicks as possible. I think that I need to
start
by
getting some good defaults in the template (like the correct data
source),
carrying that default over to the docs created from the template,
and
also
having the Merge toolbar visible to the user when they are looking
at a
Merge
document.

If your application is fairly simple, i.e. you just need to stuff a
few
values into Word from a selected recod in Access, I would avoid
MailMerge
altogether as it's far too complicated. it's probably better to follow
e.g.

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

What an interesting kb! That would be exactly what I want, except it's
not
quite what the user had before.

BTW, the sample doesn't use Merge at all - that's odd isn't it?

Peter Jamieson

Peter, your reply has been very helpful, Thank you!

Steven

[SJMac]

Peter,

Thanks again, especially for pointing out that the multi-WHERE problem on
subsequent runs -- I'd only tested it once.

Just for completeness, here's my final version

Sub MERGE()

On Error GoTo ExitMerge
' ... otherwise error messages are output to a new document!

CaseRef = Val( _
InputBox("Please type in the case reference number", _
"Case Ref"))
' ... don't trust that the user entered a number, use Val to
' convert string input to a number value. (Ignore fact that
' numbers with decimal points aren't valid for CaseRef.)

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
WhereIdx = InStrRev(.QueryString, " WHERE ", -1, vbTextCompare)
If (WhereIdx 0) Then
.QueryString = Left(.QueryString, WhereIdx - 1) & _
" WHERE Ref = " & CaseRef
Else
.QueryString = .QueryString & " WHERE Ref = " & CaseRef
End If
End With
.Execute Pause:=False
End With

Exit Sub

ExitMerge:
MsgBox Err.Description

End Sub


"Peter Jamieson" wrote:

Which actions in Access would cause a table to be locked? I assume
multiple
readers are OK? It looks like my user makes all changes to the DB via a
series of forms, rather than editing the tables directly.

I don't know Access /that/ well - broadly speaking, if a user can have the
form you want open and can still run queries successfully on another app. or
machine, you are probably OK. Personally, I would probably want to see what
happens when the user has made an update or insertion from the form - can
another program then get the data? what data does it see? When experimenting
with Access one of the things that generally screws up Word connecting to it
is having a table /definition/ dialog box open, but of course that's
generally not going to happen in the sort of scenario you're describing.

As for your macro,

1. I agree about validating that the value is numeric. When you construct an
SQL statement programmatically, from a security perspective you should
always validate all the input in case the user has typed in something that
makes your SQL statement do something completely unexpected.

For example in this case supposing .QueryString has

SELECT * FROM mytable

and the user enters a value for Ref of "(SELECT xyz FROM someothertable
WHERE myvalue = 123)"

then your statement ends up as

SELECT * FROM mytable WHERE Ref=(SELECT xyz FROM someothertable WHERE
myvalue = 123)

OK, that's not "dangerous", but someone might well be able to think of
something that is.

2. If you execute this macro more than once, you will end up with a
statement like

SELECT * FROM mytable WHERE Ref=123 WHERE Ref=456

which won't work. You really need to save the .Querystring somewhere before
modifying it.

3. I would verify that changing the .Querystring always works the way you
expect. Sometimes I've found that it's necessary to issue an OpenDataSource
to change it, not necessarily with Access though.

Peter Jamieson

"SJMac" wrote in message
...
Peter,

Thanks again, your help has been very useful helping me to understand my
problem!

Not unless you run into multi-user related problems, e.g. if a table is
locked while your form is displaying in Access, or the record you're
looking
at has been added as part of a transaction that is incomplete and Word
can't
see it.

Which actions in Access would cause a table to be locked? I assume
multiple
readers are OK? It looks like my user makes all changes to the DB via a
series of forms, rather than editing the tables directly.

I'm not (yet!) a VB Programmer, but can you give me any comments on the
Macro that I recorded and then edited?

I think I should at least validate that the user input is numeric, but I'm
not going to bother checking that it is valid record!

Sub MERGE()
'
' MERGE Macro
' Macro recorded 7/26/2006 by Steven Mackenzie
'

On Error GoTo ExitMerge
' ... otherwise error messages are output
' in to a new document

CaseRef = InputBox("Please type in the case reference number", "Case
Ref")

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
.QueryString = .QueryString & " WHERE Ref = " & CaseRef
End With
.Execute Pause:=False
End With

Exit Sub

ExitMerge:
MsgBox Err.Description

End Sub




"Peter Jamieson" wrote:

Regarding the warning's security purpose, I regard the effects of data
being
inserted into a document as harmless -- Merge can't insert malicious
code
can
it? Is everyone here happy to disable that warning?

This warning is a "scattergun" solution. The security problem is
essentially
that when Word connects to a data source, it executes code that is not
part
of Word, not necessarily part of Office, not necessarily supplied by MS,
and
not necessarily bening. The mention of SQL is a bit of a red herring, but
for example if Word connects to Access via DDE and executes an Access
query
containing user-defined functions (i.e. defined in Access VBA) those
functions may theoretically have significant side effects. But I could in
theory write a text converter (in fact I have), an OLEDB provider or ODBC
driver that takes the request "SELECT * FROM mytable" and actually wipes
the
hard drive or whatever.

Now of course in an ideal world a properly configured system would have
no
such dangerous drivers/providers, and Access and every other database
would
have proper security controls so that the user+system administrator (if
any)
can know that they are not going to do anything malicious when they
execute
SQL code. In which case you could switch the warning off and forget about
it. I expect most people probably do anyway as they have to get their
work
done and they cannot possibly tell whether the "SQL" they are about to
execute is malicious or not.

The unfortunate thing in this situation is that there are plenty of
connection types where it is difficult to see that any harm could arise
unless someone has modified Microsoft code. e.g. when Word connects to a
Word data source. In other cases I do not really see why the /Word/ user
should get a warning.

I'm not sure that answers your question but I suppose I would want to
have
reasonable confidence that my system was safe (in this respect) before
doing
so.

V helpful, thanks. Right now, I'm imagining that some code that pops up
an
input to allow the user to enter a row id, alters the QueryString, then
does
the merge shouldn't be hard to do, and will hide the slightly tedious
MailMergeRecipients dialog from the user. I'll try ... is there a
problem
you'd expect me to see?

Not unless you run into multi-user related problems, e.g. if a table is
locked while your form is displaying in Access, or the record you're
looking
at has been added as part of a transaction that is incomplete and Word
can't
see it.

/A/ reason to avoid using merge for many users is that a query that tries
to
use Access Forms!formname!fieldname syntax (which works in Access) isn't
necessarily going to work - actually, I've never really investigated that
area. If however you are creating a query that hits the underlying
table/query, as you are proposing, you should be OK.

What an interesting kb! That would be exactly what I want, except it's
not
quite what the user had before.

BTW, the sample doesn't use Merge at all - that's odd isn't it?

Not really - I think it's using the "simple" approach. I would saythat
the
main advantages of using merge come into play if you are letting the
users
modify the layout and content of the letters/documents they are
producing.
In that case, there is a pre-existing UI (i.e. the various mailmerge
tools)
which many users understand, at least to an extent, which allows them to
insert fields (and even nest them and so on), preview data, etc. However,
having a separate data source in the Word document complicates everything
because of
a. the SQL dialog we've discussed
b. multi-user considerations
c. the fact that if you move the data source, you run into problems if
the
Word document has the old data source set up when you open it, etc.

(and probably other stuff I've forgotten).

Peter Jamieson


"SJMac" wrote in message
...


"Peter Jamieson" wrote:

I suggest you use Google groups to have a good look through this
newsgroup -

I had been doing that, but remained confused until I started
recognising
and
understanding the term "main merge document", and discovered that
"detach
data source" corresponds with the Merge toolbar button "Main Document
SetupNormal Word Document".

The simple stuff :-(

be a button in the toolbar called "Merge" that prompted the user to
type
in a
In which application? Access or Word?

Word - but I see that you suggest it's easier from the Access side of
the
fence (below).

When I open the Word templates I get warnings that some SQL is about
to
be
executed - that's OK,
This prompt is displayed every time you open a Mail Merge Main
document
connected to a data source (well, there may be circumstances in which
it
is
not displayed, but they are oddities). The prompt can be suppressed by
changing the Windows registry - see
http://support.microsoft.com/default...b;en-us;825765

Yup, I'd seen that, but was happy with the prompt temporarilly so that
I
could tell that when there was still some embeded "automation".

Regarding the warning's security purpose, I regard the effects of data
being
inserted into a document as harmless -- Merge can't insert malicious
code
can
it? Is everyone here happy to disable that warning?

The "SQL" is created when the user connects the mail merge main
document
to
a data source, and is saved with the document and re-issued when it is
opened, along with a suitable connection string etc. You can't really
delete
it, since there is always some "SQL" - that's simply how Word codifies
its
queries. The connection may have been made programmatically using
Word's
OpenDataSource method, and the SQL may have been modified by changing
ActiveDocument.MailMerge.DataSource.QueryString. You can find out
(roughly)
what Word is trying to issue, after it has successfully opened the
data
source, by printing the values of
ActiveDocument.MailMerge.DataSource.Name
ActiveDocument.MailMerge.DataSource.ConnectString
ActiveDocument.MailMerge.DataSource.QueryString

V helpful, thanks. Right now, I'm imagining that some code that pops up
an
input to allow the user to enter a row id, alters the QueryString, then
does
the merge shouldn't be hard to do, and will hide the slightly tedious
MailMergeRecipients dialog from the user. I'll try ... is there a
problem
you'd expect me to see?

No data is inserted in to the templates after I open them. Why is
SQL
being
executed for the template? Surely the data normally needs to be
retrieved
for
the actual documents, or when I do the merge?