Results 1 to 14 of 14
  1. #1
    Lounger
    Join Date
    Jan 2001
    Posts
    47
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Controlling Word Mailmerge

    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?

  2. #2
    greisz
    Guest

    Re: Controlling Word Mailmerge

    It sounds like you need to relink the Word document. Word mail merge documents with DDE links can lose their link to the mdb file. This will cause the document to open a new copy of Access.

    This was a very common problem for us until about two years when I began using mail merge documents that are linked to text files. These text files are created on the user's hard drive by code that uses the Transfertext method. The same procedure that creates the text file also uses automation to open Word and run the mail merge. Let me know if you want details on this technique.

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Controlling Word Mailmerge

    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.
    Charlotte

  4. #4
    Lounger
    Join Date
    Jan 2001
    Posts
    47
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Controlling Word Mailmerge

    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?

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Controlling Word Mailmerge

    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.
    Charlotte

  6. #6
    New Lounger
    Join Date
    Dec 2000
    Location
    Calif
    Posts
    12
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controlling Word Mailmerge

    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

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Controlling Word Mailmerge

    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.
    Charlotte

  8. #8
    greisz
    Guest

    Re: Controlling Word Mailmerge

    Whenever you link to the data source you need to be sure that you have the check box labeled "Select Method" checked. On some systems the link to the text file will be made for you using ODBC if you don't have this option selected. I encountered this problem recently with a Word 97 document and it took awhile to figure out what the problem was.

  9. #9
    Lounger
    Join Date
    Jan 2001
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controlling Word Mailmerge

    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.

  10. #10
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Ohio - USA, Ohio, USA
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controlling Word Mailmerge

    I'm not sure I understand. Where do you check "Select Method"? That's not something I remember seeing?

    Thanks


    "CD"[img]/w3timages/icons/doh.gif[/img]

  11. #11
    Lounger
    Join Date
    Mar 2001
    Location
    LA, California, USA
    Posts
    45
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controlling Word Mailmerge

    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.

  12. #12
    Lounger
    Join Date
    Jan 2002
    Location
    Scotland, Midlothian, Scotland
    Posts
    25
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controlling Word Mailmerge

    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! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>)
    JohnHW
    Like databases but struggling with VB.

  13. #13
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Controlling Word Mailmerge

    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 <A target="_blank" HREF=http://www.woodyswatch.com/access/archtemplate.asp?3-20>Woody's Access Watch Vol 3 No 20</A>. It includes several tips for making merges work reliably, and some sample code to do merges from Access.
    Wendell

  14. #14
    New Lounger
    Join Date
    Feb 2001
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controlling Word Mailmerge

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •