Controlling Word Mailmerge

Topic

New Reply

I use Access97 to control Word97 as an object. Now I want to have a mailmerge document use a predefined query stored in the Access application as datasource. When loading the document Word starts a second instance of Access prompting the user to enter username and password again as the database is secured.
Is there any way of telling Word to use that instance of Access which has created the Word-object in order to avoid the second user-authorization?

Reply | Quote

Replies

Unfortunately, this was “feature” of Office 97, and was why I never used Access as a direct datasource for a mail merge. You can export the query to a text file with a fixed name using TransferText and make that text file the datasource for your Word mail merge. I did this in code, killing the existing file and replacing it with the new file for the mail merge. One problem I found was that I had to do an export and manually link the Word mail merge document to that text file the first time. After that, it would read the new version of the text file automatically.

Reply | Quote

Thank you, Charlotte and greisz.
Using TransferText was in fact the alternative I successfully use. I just wondered, whether there was really no way of getting it to work the other way. Has this changed with Access2K?

BUT: Strange enough, after installing Microsofts Y2K-Patch ontop of Office97 SR-2, “DoCmd.TransferText acExportMerge” does not work with .doc files anymore, it shows error 3027 (“database or object write protected”). Using a .txt file instead works fine. But Word will not accept the .txt as datasource for mailmerge anymore, so I have to rename the .txt to .doc after creation. Any hints to why this happens?

Reply | Quote

Hmmm, I just tried it in Word 2K and ran into the same problem. In that version it may have to do with unicode and Word not knowing how to intrepret the text. But I had done it previously with O97-SR1, I know, and I thought it had worked in SR2 as well.

Reply | Quote

This may not be your problem, but I know for a fact that it solved my problem. While trying a Word merge from Access for labels, it also opened a second “Access”. What was causing it was this. When you create an Access DB you can set the start up options like the opening form and you can give your database file an application name that appears on the icon on your task bar. This name was causing word to open a second Access. Once I removed the “application name”, it stopped opening the second one. I hope this fixes your problem. By the way this was with Access 97. I dont have 2000 yet.
Cary

Reply | Quote

2000 is a whole different animal because Access is an ActiveX (OLE in previous versions) server as well as a client as in previous versions. That means that Word can automate Access, which changes the whole picture.

Reply | Quote

The last post from ‘greisc’ was right on the mark. Using the select method option is right on the money.

I have found that using that method, along with defining a DSN to the Access Database allows me to have Word merge documents permanently linked to queries in the database.

Rich P.

Reply | Quote

I’m not sure I understand. Where do you check “Select Method”? That’s not something I remember seeing?

Thanks

“CD”

Reply | Quote

This is from Office 2K, but I remember it being similar or the same in Office ’97. When you have the Word document open, click Tools | Mail Merge. When you get to step 2, if you choose “Open Data Source” it pops up a window to choose the source, and next to the file type field is the “Select Method” check box. I tried this for my mailmerge code, but it did not stop my Access db from opening another instance. Hope this helps.

Reply | Quote

Hi
I am a new to this forum and a newbe when it comes to Visual Basic in Access. I have having a similar problem and would like to take you up on your offer of details of your technique of using text files. Tried this but could not get it to work.

I use an Access 97 Db to track cases I deal with. When viewing a record I want to click a button which will open a standard letter in Word 97 with the person name and address filled in. To do this I use a query with picks up the record number from the current form and then collects the fields I need from the various tables.

I have tried using the mailmerge method described in Article Q159328 but cannot work out how to pass the record number parameter. It runs OK but prompts for the record number in Access.

Any help would be GREAT! )
JohnHW
Like databases but struggling with VB.

Reply | Quote

This has been a problem with mail merges since Office 95, and it still is with Office XP. Two things contribute to this problem. The first is having security turned on – i.e. having a password for the Admin account. The second is renaming your application either in code or via the startup options. The first one you can’t do much about, but the second problems can be done by renaming your application as “Microsoft Access: MY APPLICATION” – apparently when Word starts the merge it looks at the application name expecting to find “Microsoft Access” and when it doesn’t, it starts another instance of Access.

We typically execute the merge from Access using OLE Automation code which solves some but not all of the problems associated with mergeing. This is a fairly complicated subject – most unfortunate because it’s a very powerful tool when it works correctly. We wrote an article a few months ago that you may find useful – you can find it on Woody’s Access Watch Vol 3 No 20. It includes several tips for making merges work reliably, and some sample code to do merges from Access.

Reply | Quote

I wrote the following code for Access 97 and have since upgraded the database to 2K. It has always worked fine, though it can be slow, and you have to be careful with mapped network drives as they can cause another Access to start.
The letters themselves are mail merge linked from Word to a standard query in the database, and all have a AutoOpen macro which specifies the document name. Is this a good way to do it? Would another method be quicker?

This first procedure sets the path for Word and the letters to be used.

Public Sub WordandDocPaths()
WordLocation = “C:Program FilesMicrosoft OfficeOfficeWINWORD.EXE”
DocLocation = “server1group datadatabasesDCDATA”

End Sub

This procedure determines whether or not Word is already running. If No, word is started and the appropriate letter loaded. If yes, the current instance of Word is used.

Sub openletter()

Dim wrd As Object

On Error Resume Next

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Set wrd = GetObject(, “Word.Application”)
If Err.Number 0 Then
Call Shell(WordLocation & ” “”” & DocLocation & StdLet, 1)

Else: wrd.Documents.Open DocLocation & StdLet

wrd.wdWindowStateNormal = 0
End If

AppActivate (“Microsoft Word”)

Set wrd = Nothing

End Sub

Each button on the form has the following code to set the value of stdlet so the correct letter is loaded:

Private Sub CommandGeneralLetter_Click()
On Error GoTo CommandGeneralLetter_Click_Err

StdLet = “ack0.doc”

Call WordandDocPaths

Call openletter

CommandGeneralLetter_Click_Exit:
Exit Sub

CommandGeneralLetter_Click_Err:
MsgBox Error$
Resume CommandGeneralLetter_Click_Exit

End Sub

Reply | Quote