Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    MailMerge leftovers (Access 2002)

    When I run the following code (this is main part of a Function) I find the Datebase it refers to is left open (which just happens to be the same as the database as I am calling the function from).
    What is also left open is the word document that it uses as the source of the merge, this document is "d:dataGrantsChairperson Ltr GST - Master.doc".

    My question is, how do I close the document and database that are left open?


    The code is:
    Dim objWord As Word.Document
    Set objWord = GetObject("d:dataGrantsChairperson Ltr GST - Master.doc", "Word.Document")
    ' Make Word visible.
    objWord.Application.Visible = True
    ' Set the mail merge data source as the NEW Community Database database.
    objWord.mailmerge.OpenDataSource _
    Name:="d:dataGrantsNEW Community Database.mdb", _
    LinkToSource:=True, _
    Connection:="QUERY qry MailMerge Sample1", _
    SubType:=wdMergeSubTypeWord2000

    ' objWord.mailmerge.OpenDataSource _
    ' Name:="d:dataGrantsNEW Community Database.mdb", _
    ' LinkToSource:=True, _
    ' Connection:="QUERY qry MailMerge Sample1", _
    ' SQLStatement:="SELECT * FROM [??????]", _
    ' SubType:=wdMergeSubTypeWord2000

    objWord.mailmerge.destination = wdSendToNewDocument
    ' Execute the mail merge.
    objWord.mailmerge.Execute
    ' Save as a file on disk
    ActiveDocument.SaveAs "d:dataGrantsChairperson Ltr GST - Master Merge", _
    FileFormat:=wdFormatDocument

    ' set background, this is required as the printout won't work unless you specify it
    ' objWord.Application.Options.PrintBackground = False
    ' Print
    ' objWord.Application.ActiveDocument.PrintOut

    ' Close the active document
    ActiveDocument.Close SaveChanges:=wdSaveChanges, OriginalFormat:=wdWordDocument

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: MailMerge leftovers (Access 2002)

    Closing the document is easy:

    objWord.Close SaveChanges:=wdDoNotSaveChanges
    I would put this line staight after the Execute line

    Why would you want to close the database, if it is the same one as the calling one? Or has the code opened another copy?

    There is no need for a second copy to be opened. This is usually caused by having an Application title in Tools...Startup, but Security can also cause it.
    Regards
    John



  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MailMerge leftovers (Access 2002)

    In addition to John's question/remark: the mail merge uses DDE to connect to the database. If the Access database has an Application Title that does not begin with "Microsoft Access", the mail merge process will often open a second (and sometimes third, ...) instance of the database. To avoid this, either clear the application Title, or give it a value that begins with "Microsoft Access". The Application Title can be set in Tools | Startup...

    An alternative is to use ODBC to connect to the database. I always use this method: in my experience, it is dependable and results in faster operation. Downside is that it won't work if your query contains parameters or uses Access-specific or user-defined functions. And some Loungers have reported problems getting it to work at all.

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: MailMerge leftovers (Access 2002)

    Thanks John
    I put the name "Microsoft Access" in front of the app name (as Hans suggested) as well as putting your code in to close the document.
    Now it takes well over an hour to run, I assum it will take this long or longer as I stopped it after 30 minutes and it was nowhere finished.
    When it opened multiple databases and left the source document open it took just a few minutes.

  5. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: MailMerge leftovers (Access 2002)

    When you say to ODBC, how do you do this in the code I have supplied above?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MailMerge leftovers (Access 2002)

    To use ODBC, you must leave the Name argument of OpenDatasource blank, specify the ODBC data source and the database in the Connection argument, and specify an SQL statement in (surprise, surprise) the SQLStatement argument. The Connection argument looks like this:

    Connection:="DSN=MS Access Database;DBQ=" & CurrentDb.Name

    Check the ODBC Data Sources control panel for the actual name of the User DSN for Access. Here is the skeleton code for opening the data source:

    With objWordDoc.MailMerge
    ...
    .OpenDataSource _
    Name:="", _
    LinkToSource:=True, _
    Connection:="DSN=MS Access Database;DBQ=" & CurrentDb.Name, _
    SQLStatement:="SELECT * FROM tblMerge", _
    SubType:=wdMergeSubTypeWord2000
    ...
    End With

    The SubType argument is required in Word 2002 for all data sources different from the default OLE DB. See the code attached to <post#=362462>post 362462</post#> for a more elaborate example. It was originally meant for Word 2000, so the SubType argument is not used there.

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: MailMerge leftovers (Access 2002)

    Patt

    Take a look at the long thread beginning with <post#=361665 >post 361665 </post#> .

    This has sample code for a number of different approaches to merging (including Hans' code for ODBC).

    I don't know why it should take a long time when the merge uses the current copy of the db. The status bar message (In word) tells you what is going on - initially it is setting up the DDE link, then it shows the record number as it merges. Does it take ages to get started, or is it the merging that is slow? How long did it take when it opened a second copy of the db?

    How many records are in your merge? how many merge fields? Is the query especially complicated?

    I did a test recently that created 68 letters, with 8 merge fields in 1.25 secs .
    Regards
    John



  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: MailMerge leftovers (Access 2002)

    John
    I will look at the post after I try Hans suggestion abot the database name.
    The setting of the DDE link takes a long time and the merge takes even longer.
    However, when I set the App name to non-Null (without the phrase Microsoft Access) it literally flies, takes seconds to set up the DDE link then a couple of minutes to run the merge. There are 2959 records in the merge and about 10 fields to merge.

  9. #9
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: MailMerge leftovers (Access 2002)

    <<Closing the document is easy:

    objWord.Close SaveChanges:=wdDoNotSaveChanges >>

    Well it closes the document, but it leaves an instance of Word open, how do I get rid of the Word instance?

  10. #10
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: MailMerge leftovers (Access 2002)

    Thanks Hans
    Well the ODBC method certainly works and is quick as well, it processes 2950 records in 1 minute 16 seconds. The only problem I have got is the one I mentioned to John, re the closing the instance of Word.

  11. #11
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: MailMerge leftovers (Access 2002)

    Here is a way of closing the instance of Word.

    Declare another variable.

    Dim objWordApp As Word.Application

    When you opened the first document, add this line, to point the variable to the open instance of Word

    Set objWordApp = objWord.Application

    then when all is finished

    objWordApp.quit

    It is good to see how fast the ODBC merge was. I have never got it to work, but it prompts me to try again and harder.
    Regards
    John



  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: MailMerge leftovers (Access 2002)

    Do you use this in preference to objWord.Close?

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: MailMerge leftovers (Access 2002)

    No, in addition to it.

    By using GetObject to open the document, you are implicitly creating a Word.Application object too. You must close the document object, and you must quit the application object too.

    As an alternative you can use

    objWordApp.Quit SaveChanges:=wdDoNotSaveChanges
    Set objWordApp = Nothing

    This would quit the application and close all open documents without saving them.

  14. #14
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: MailMerge leftovers (Access 2002)

    I tried what you suggested Hans, and it worked a treat.

    Thanks to Hans and John I have learnt quite a bit from this exercise, as I have never used a mail merge before.

  15. #15
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: MailMerge leftovers (Access 2002)

    I have bumped into another problem I haven't met before.

    If I put in an Application Title that does not start with Microsoft Access (which Patt said made his merge really quick) I get this error, aned when I go to debug, this is the line of code highlighted.
    Attached Images Attached Images
    Regards
    John



Page 1 of 2 12 LastLast

Posting Permissions

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