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