Q: simple Access to Word mail merge

[Mark]

I'm using Access 2003 to mail merge to a Word 2003 doc.

Below is the code that I modified from the microsoft website.

=========================
Dim strWordDocName As String
Dim objWord As Word.Document
Dim strTableName As String
strTableName = "MAILMERGESUBSET"

strWordDocName = "S:\PATH\MailMerge.doc"
Set objWord = GetObject(strWordDocName, "Word.Document")
objWord.Application.Visible = True

objWord.MailMerge.OpenDataSource _
Name:=CurrentProject.FullName, _
linktosource:=True, _
Connection:="QUERY MAILMERGESUBSET", _
ReadOnly:=True, _
sqlstatement:="select * from mailmergesubset"

objWord.MailMerge.Execute
=========================
When I try to run th ecode above, I get the following error

"The operation cannot be completed because of dialog of database engine
failures. Please Try again later."

Notes:
* MAILMERGESUBSET is a query off a table, and all the query is doing is
selecting all records from the table where a charge_amt >0". No query
parameters, no parameters based on forms, etc.
* When I open the word doc, it merges just fine.


Any help appreciated!
-Mark

 

[Albert D. Kallal]

I have a nice working sample that does a merge of the current record to
word.

The sample I have can be found here:
http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

What is nice/interesting about my sample is that is specially designed to
enable ANY form with ONE LINE of code....

Thus, each time you build a new form, you can word merge enable it with
great ease.

Make sure you read the instructions from above, and you should eventually
get to the follwoing page
http://www.members.shaw.ca/AlbertKallal/wordmerge/page2.html


Note that the merge can also use a query, and thus you don't have to merge
just "one" record.

After the merge occurs, you get a plain document WITHOUT any merge fields,
and this allows the end user to save, edit, or even email the document
(since the merge fields are gone after the merge occurs).

If no one else jumpes in with a solution to your posted code, then consider
giving the above a try.

 

[KARL DEWEY]

Have you tried from the other side - Word Mail-Merge? Open a blank Word
document, click on menu TOOLS - Letters and Mailings - Mail Merge. It opens
an additional window on the right and has step-by-step instructions and
choices. You can also backup in the process. Works fine for me.

 

[Mark]

Hi Karl,

Yes, I've tried the other side. My apologies for kinda being ambiguous in my
response, but when I have tried the other side, it works perfectly fine.

But I need this automated to one-click. As easy as the word wizard is, I
need it to the point that a blind, drunk, half-conscious monkey could run the
report.

 

[Mark]

Thanks Albert,

I'll try it if I don't get any responses.

But I would also like to see if there's a simple fix for what seems like
should be a simple macro.

Appreciate it,
-Mark

 

[Mark]

Hi Albert,

Going through your code (albeit on a very high level), it seems like you're
writing the query to a text file and lauching word and automating the mail
merge via access, but using the text file. Is that about right?

Thanks again for your help,
-Mark

 

[Ken Sheridan]

You might like to take a look at:


http://community.netscape.com/n/pfx/forum.aspx?msg=23781.1&nav=messages&webtag=ws-msdevapps


which includes examples of various Access to Word automation operations.

Ken Sheridan
Stafford, England

 

[Albert D. Kallal]

Hi Albert,

Going through your code (albeit on a very high level), it seems like
you're
writing the query to a text file and lauching word and automating the mail
merge via access, but using the text file. Is that about right?

Thanks again for your help,
-Mark

Yes, that is correct. furthermore, the reason why I don't use the csv file
extension is because I've had some bugs when you have the "display" of
extensions on windows turned off.

my website outlines several reasons why take this approach. One of them is
simply that don't like allowing word to connect directly to access as then
if word craps out, it can lock up ms-access also. Furthermore, if you have
a different name for your application, often word will launch a second copy
of MS access when you do the merge, and again that just uses more system
resources, and is unexpected behavior.

Furthermore if you're running more than one version of access, or
potentially in a runtime environment, then word has no clue as to which
version of the access it's going to launch to do the merge, and again it
creates confusion (and even worse, is the fact that you don't have control
over which version of access will actually be launched to do the merge)

Further if you have things like vba expressions etc in the sql, then word
can't connect, or use the sql. So, a export to a text file solves all kinds
of problems here.


Furthermore, if you have things like MS access security setup, or even a
simple database password, then once again exporting "out" of ms-access to a
text file completely eliminates any kind of problems here (word can't
connect when you have passwords, or security ...and if it does connect,
often you have problems).

And, the same goes if your back end is SQL server, once again you've got
increased flexibility because of a limiting all kinds of connectivity
issues.

so, export out of the application, and then we only need word + a simple
text file....

you'll not interfere with the operation of MS access in any way shape or
form when you do the above.

It goes without saying, that application and example word merged has been
installed on a very large number of machines, across an amazing variety of
different versions of word and even make "mixed" versions of office where
access and word are different. So, you can run an access 97 database, and do
a merge to word 2007 without any problems using the above approach.

I wrote this system because I simply want to install software, press a
button and have the merge work, and not waste valuable support dollars of my
customers who need to do a stupid merge.

It just simply works, and it's dead rock solid 100% reliable. This is a
problem it simply solved for me, and we can now work on new features and do
something more productive tomorrow.

 

[Mark]

Wanted to thank you for your very helpful response. My apologies for such a
late response -- I got thrown into a high priority task -- but I really
appreciate it.

-Mark