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?