Word Doc connected to Access Query

[RobGMiller]

Office 2003,

I'd like to set a single record SQL in an access Query, or Table if
necessary, and then launch a word document which uses this data. I've set the
doc up using the mail merge wizard. It works the first time but once the
document has been saved it returns the data from the original query the next
time it is opened.

Simply put, the word doc will be used to display the query fields. The user
needs to save this document using a different name.

In order to securely preserve the template, perhaps the doc can be saved
under a differnet formatted name and then opened with the correct current
query information.


Thanks in advance....

 

[Peter Jamieson]

OK, I'm not completely sure which particular problem(s) you need to solve
here.

How are you deciding which record to select? for example, do you have a
query that selects a single record?

Are you saying that the problem is that when the user opens the document,
they should see the current query result, but actually see the query result
they last saved?

Or are you saying that once the user has saved the document, when they
re-open it you want them to see the result they saved, but actually they are
seeing the current result?

(I think there are a few other possibilities but just let us know...)

Guessing that what you mean is that once the user has saved the document,
when they re-open it you want them to see the result they saved, there are a
few ways you could go, e.g.
a. get the user to "unlink" the fields before saving (ctrl-A then
ctrl-shift-F9), and they should probably also disconnect the data source,
then Save As - that's getting to be rather a lot so you'd probably need a
macro.
b. consider using a DATABASE field to insert the results. The user might
need to re-execute the database field (ctrl-A then F9, and to unlink the
results as in (a), they wouldn't need to disconnect the data source, but
they'd need to Save As)
c. get the user to perform the merge and save the resulting document. Or do
it from a Macro.

Since I'm not sure what you're really sasking there'ss little point in
offering any code (my stock is in any case rather limited) but if that
points you in the direciton you need to go, it may be worth your while
haveing a look around this group using Google groups.

Peter Jamieson

 

[Doug Robbins - Word MVP]

I would suggest that you see fellow MVP Albert Kallal's "MergeSingleWord"
utility at:

http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[RobGMiller]

Thanks Peter and Doug,

I had not thought about many of your scenarios Peter,

I've since found that if the Doc is connected to an access table containing
a single record it will show its content. It does not work with a query for
some reason. So If I make the table prior to opening the document it works as
expected.

Because the second time I open the doc it showed the data from the first
opening I did not think it would be a problem to save the original document.
Now It will show the current table record everytime it is opened even if it
has been saved under a new name of course.

Ideally, I want to open the document minimized or hidden, then replace merge
fields with actual text from the current record and then save it to a new
name programatically before the user gets to edit it.

I really dont want the user to have to learn how to and remember the steps
in disconnecting the data source before saving.

It sounds like a tall order.

I will look at Albert Kalla's utility to see if there is anything in there I
can use.

 

[Peter Jamieson]

OK, suggest you post here again when you've had a good look at the Kallal
link. Some points worth making though:
a. as far as I can tell, Word 2003 always tries to get data from Access
when it opens the mail merge main document. It also tries to remember the
record you were previewing when you closed the document. That may not be
true of all other versions of Word or all other data sources.
b. in Word 2003, one thing that might leave you with the old /query/
results is if you choose to connect to Access using DDE. In that case, when
you select the query when you first set up the mail merge main document,
there is an option to "link to query". If you leave that option checked,
Word 2003 stores a piece of SQL along the lines of

SELECT * FROM [queryname]

When you re-open the document, Word should re-execute the query and you
should get the result you expect. However, if you uncheck "link to query",
Word 2003 retrieves and stores the query's SQL. So if queryname says

SELECT * FROM tablename WHERE keyfield = 10

that's what Word stores. So perhaps your overall process is modifying the
query to select a particular record, e.g. changing the query to

SELECT * FROM tablename WHERE keyfield = 20

But when the user re-opens the document, Word will still execute

SELECT * FROM tablename WHERE keyfield = 10

and the data in the document will not change (assuming the underlying table
record has not changed).

c. Again, I'm not sure that's exactly how it works in previous versions of
Word. Anyway, what certainly seems to be the case is that the behaviour is
not easy to predict - I have actually had to do two non-default things to
get Word to behave the "use DDE and uncheck link to query" way, and yet on
your system it seems you have done nothing out of the ordinary and you see
this behaviour.
d. if you are automating Word and using its mailmerge facilities, the
chances are you will need to take account of the following article:

http://support.microsoft.com/kb/825765/en-us

Peter Jamieson