Close a Database after a Mail Merge

[HappyChap]

I hope someone can help; furthermore, please be aware that my knowledge of the correct terminology may not be 100% accurate but I do hope that you will afford me some understanding because of that.

I am using Word and Access 2000.

I have created some code (linked to a command button on a form) which opens a word document (with mergefields) and then performs the intended mail merge: the code is below.

However, I am in need of some help please with code to close the "second" copy of the database (Reservations.mdb) and the Module code page once the merge is completed (it would need to entered, I think, after the red line below).

To explain, when the mail merge starts, it opens a second copy of the database; I have managed to create the code to close the Main Document (BrksDednsEM.doc) but need help closing the second database.

I have searched various forums and googled for 3 whole days this week to try and find the solution/s but to no avail; in addition, I do appreciate that many members may wish to suggest the use of a report instead of mail merge; however, I most definitely want to use mail merge and not a report.

Many thanks


Function MergeIt()
Dim objWord As Word.Document
Set objWord = GetObject("H:\enjoy120210\CorrespondenceLetters\Ma ilMerge\BrksDednsEM.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the Northwind database.
objWord.MailMerge.OpenDataSource _
Name:="H:\enjoy120210\Reservations.mdb", _
LinkToSource:=True, _
Connection:="QUERY MasterDataSource", _
SQLStatement:="SELECT * FROM [Customers]"
' Execute the mail merge.
objWord.MailMerge.Execute
' Close BrksDednsEM.doc
Word.Documents("BrksDednsEM.doc").Close (Word.WdSaveOptions.wdSaveChanges)
' Close Reservations.mdb

End Function

 

[Rx_]

objWord.MailMerge.OpenDataSource Name:="H:\enjoy120210\Reservations.mdb", _
LinkToSource:=True, _
Connection:="QUERY MasterDataSource", _
SQLStatement:="SELECT * FROM [Customers]"

Just guessing that you are using Access to open Word that opens another Access DB.
What seems to be missing is a command to Word to close the 2nd DB.

On a search, this method linked below appears to be better. Have not actually tested the code, however it looks good.
This method has the original Access - grab the data from the 2nd Access. Then with that data-set brought over to the original Access, completes the Word mail merge from the original Access DB.
Opening one external object at a time would be better than the chain-of-custody method.
http://stackoverflow.com/questions/...by-vba-in-access-let-word-open-database-again

 

[HappyChap]

Thanks Rx

Sorry if I'm being thick (but still happy !) here but where are you pointing me to for help please ?

Thanks

 

[HappyChap]

Can you help please ?