Word MailMerge from Access

[Ed Warren]

Please point me in the right direction. (found the Word MVP site, great but
wrong direction Word --> Access)

1. Have a word document doc1.doc with query that accesses data from my
access database db.mdb
2. Desire to have a button on a form that will open the word document, run
the query, merge the data, show the merged file.

Poking around google, I can find lots of stuff on how to do this from the
word side, but silence on how to make this work from access. What I have
found is that most people export the access data out to an CSV file (*.doc)
then use this as the word datasource, this is a problem for me because they
use lots of " in the stored data. (working with word 2000 and access 2000)

Thanks,

Ed Warren

 

[Albert D. Kallal]

Try my solution, and see how it works:

It is a simply and easy one-click to use word merge....

http://www.attcanada.net/~kallal.msn/msaccess/msaccess.html

 

[Ed Warren]

Thanks, that is great and will work for me, but not my user (sometimes I
think the world would better off without users!!!)

He really wants the program to use the Word query (select * from theTable).
My first answer was "Open word, run the merge", but that alas does not
serve.

Reading your link below leads me to believe this is problematic to
accomplish.

Either a link or some short words about why his approach is a problem would
be helpful to trigger a 'user mind changing event'

(by the way -- lots of really good stuff on the site below, will take a more
in depth look later looks like some very useful tools)

Thanks for caring enough to share your work.

Ed Warren

 

[Albert D. Kallal]

You are most welcome!

Hum, I not sure, but you do realize that you can prompt, or use a query, and
use that for my merge?

I mean, your original question was click on a button..and merge query to
word, and then show the results (in word..right?).

My example code library works like:

dim strSql as string

strSql = "select * from tblCustomers where City = 'Edmonton'"

MergeAllWord (strSql)

The result of the above (when you select the word document from my neat-o
template list) would be a merged document.

You could also prompt, or present the user with some options before you
launch the word merge system.

He really wants the program to use the Word query (select * from theTable).
My first answer was "Open word, run the merge", but that alas does not
serve.

Hum, I am now not 100% sure quite what is needed here!. Is the issue that
they want to use the qurey builder in word?

 

[Ed Warren]

Thanks for you comments Albert, I think your program works like a charm and
fulfils the need exactly.

What my user does not like is that your code outputs a CSV file (merge.888)
as an intermediate step. (I think it is probably a brillant step to avoid
some arcane Word merge problem)

I honestly do not have the experience with Word/Word Merge to know why you
do that, but from comments from your web page, there is a damn good reason
for it. I was hoping you could provide divine enlightenment and I could go
back to the 'seat of power' with a convincing argument of why this is
necessary.

Ed Warren

 

[david epsom dot com dot au]

Out of all the many methods available, I personally use Excel export
by preference. Every Secretary/PA I ever met can do Mail Merge from
a spreadsheet better than I can, and likes to do it that way.
Just export to a Excel Spread sheet, and leave the rest up to them.

Only automate the next step (mail merge or bookmark update) if the
user is a clerk rather than a Secretary/PA.

(david)

 

[Albert D. Kallal]

My news reader missed this!

Yes, the reasons are quite simple:

1) The user does not know, or care that a csv file is created...so this is
really moot to the end user. So, it really don't matter.

2) Any issues of security are avoided with my approach. If you secure your
mdb database, then the word merge now must supply security information..and
that can be a real pain. Further, it means that word has to deal with
security now.

3) Allowing ms-word to open, and attach to a mdb file means that if word
craps out, it can leave ms-access hanging. Why have one application open,
and talk to another if you don't not have to?

4) if the user closes ms-access while word is running, once again, then
things can hang up. With my solution, this can't happen.

5) If ms-access were to stop responding, then it can hang up word. (more
instability). Also, once the word merge starts, then ms-access can be
stopped, closed, or even if it were to freeze up, the word merge continues
freely. Obviously, this will increase reliability here.

6) If we allow word now to open ms-access, what happens if you have more
then one copy of ms-access installed? This occurs quite often if you install
the runtime version etc.(or even one of the many commercial programs that
uses the access runtime). Now, which version of ms-access will word launch?
I think it is quite obviously again that allowing word to launch ms-access
can open up a large can of worms.