Why does Mail merge start another copy of Access? 1

[rmelnyck]

I have the following code which opens a word document template and does a mail merge using an access query. Everything works fine but an extra copy of access opens and I want to be able to close it. Any help would be great. Here is the code.

Public Function MergeIt()
Dim objWord As Word.Document
Set objWord = GetObject("C:\mailtest1.doc", "Word.Document")

' Make Word visible.
objWord.Application.Visible = True

objWord.MailMerge.OpenDataSource _
Name:="C:\Reporting Stewardship SYSDB_new.mdb", _
LinkToSource:=True, _
Connection:="QUERY Cover Letter Query", _
SQLStatement:="SELECT * FROM [Cover Letter Query]", _
SubType:=wdMergeSubTypeWord2000

' Execute the mail merge.
objWord.MailMerge.Execute
objWord.Close False


End Function

 

[PHV]

I guess the merge is done by DDE.
Have you tried this ? (in order to merging with OLEDB)
objWord.MailMerge.OpenDataSource _
Name:="C:\Reporting Stewardship SYSDB_new.mdb", _
SQLStatement:="SELECT * FROM [Cover Letter Query]"

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[rmelnyck]

I tried it but I am still having the same problem.

Thanks for trying though!

 

[psemianonymous]

Check out the way I did it:

thread181-817153

Of course, I was having problems with some users, but ... pending further horror ... problem resolved.

Basically, instead of having Word open a query on your database, you 'push' out a temporary text file (you must also support a valid export specification for this to work). Then you open the mailmerge document so that it opens the text file as the data source. Run the merge, close the mailmerge document, delete the temp text file, and you are left with the merged result.

Anyway, check it out if you're interested.

 

[rmelnyck]

Cool thanks. I will try it.

 

[TimTDP]

Hi rmelnyck

Have you found a solution to your problem? I have the exact same problem. Something that I have noticed is that if I close the second copy of the database, it is always opened when I click on the Word template on which the mail merge is based.

 

[dotdavid]

I had this problem too.

Check out

http://www.mvps.org/access/bugs/bugs0011.htm

 

[slickpg]

I also had this problem using Access 97 and Word 97. The solution is to make sure your word documents are being set up correctly in the mail merge process. For complete details see the article in the Microsoft Knowledge Base article Q199963 and follow the steps exactly as stated in Method #2 and your problems will go away. Quick note, when I first established my mail merge documents I used DDE which worked ok but like you, that second occurrence of Access continued to show up.

 

[dotdavid]

Further to everyone else's answers, it may be useful to include the code at

http://www.mvps.org/access/api/api0041.htm

when your database loads. It means that any additional copies of the database will be closed once they see there is already a copy open. Mail merges appear to work fine anyway.

Also make sure the word document doesn't have a datasource set - just the merge fields. This seems to confuse the hell out of it too

 

[knownote]

There is a simpler alternative to mail merges. An author
of computer books has code I've adapted; the only limitation
is not having too many fields in a given table.

John

 

[PizMac]

By far the easiest solution is to REMOVE THE APPLICATION TITLE (Tools-Start up). If you can live with "Microsoft Access" in the title bar then it all works fine. It's because Word looks for this as a title and opens a new one if it can't find it.

 

[BSando]

I think everyone has had the same problem

Try this
------------------------------------

Public Function MergeIt()
Dim objword As New Word.Application
Dim objdoc As Word.Document


objword.Application.Visible = True
Set objdoc = objword.Documents.open("C:\mailtest1.doc")
objword.Visible = True

objdoc.MailMerge.OpenDataSource_
Name:="C:\Reporting Stewardship SYSDB_new.mdb",,_
linktoSource:=True, passworddocument:="", writepassworddocument:="",_
writepasswordtemplate:="", Connection:="QUERY Cover Letter Query",_
sqlstatement:="Select * FROM [Cover Letter Query]", openexclusive:=False

objdoc.MailMerge.Execute


Set objword = Nothing
Set objdoc = Nothing

Some people make things happen, some watch while things happen, and some wonder 'What happened?'