Detach or change SQL data source for Word template, and other Qs

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SJMac - 25 Jul 2006 22:55 GMT

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

Reply to this Message

Peter Jamieson - 25 Jul 2006 23:43 GMT

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.aspx?scid=kb;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
[quoted text clipped - 3 lines]
> 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
[quoted text clipped - 4 lines]
> 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

> Hello,
>
[quoted text clipped - 53 lines]
>
> Steven

Reply to this Message

SJMac - 26 Jul 2006 08:17 GMT

> 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
Setup>Normal 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,
[quoted text clipped - 3 lines]
> changing the Windows registry - see
> http://support.microsoft.com/default.aspx?scid=kb;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
[quoted text clipped - 8 lines]
> 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
[quoted text clipped - 7 lines]
> 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
[quoted text clipped - 4 lines]
> 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
[quoted text clipped - 6 lines]
> 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
[quoted text clipped - 10 lines]
>
> 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

Reply to this Message

Peter Jamieson - 26 Jul 2006 09:37 GMT

> 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

>> I suggest you use Google groups to have a good look through this
>> newsgroup -
[quoted text clipped - 132 lines]
>
> Steven

Reply to this Message

SJMac - 26 Jul 2006 18:14 GMT

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

> > Regarding the warning's security purpose, I regard the effects of data
> > being
[quoted text clipped - 208 lines]
> >
> > Steven

Reply to this Message

Peter Jamieson - 26 Jul 2006 18:41 GMT

> 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

> Peter,
>
[quoted text clipped - 318 lines]
>> >
>> > Steven

Reply to this Message

SJMac - 27 Jul 2006 10:12 GMT

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

> > Which actions in Access would cause a table to be locked? I assume
> > multiple
[quoted text clipped - 282 lines]
> >> >> > for
> >> >> > the actual documents, or when I do the merge?

Reply to this Message