Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Avoiding "Data Link" during Word merge

Discussion in 'Information Technology' started by cjg.groups@gmail.com, Jul 28, 2006.

  1. cjg.groups@gmail.com

    cjg.groups@gmail.com
    Expand Collapse
    Guest

    In Access 2003, I am using VB to build a complex MAKE TABLE query,
    store it in a new Query Def, then execute it to make the table. I then
    launch a Word mail merge using that new table as the data source. The
    Word template is not bound to a data source.

    When I run the code, Word shows the Data Link window asking for a data
    source. Trying to cancel says the data source is already open, but the
    merge runs anyway. This happens often, but not always, and I can't
    tell what causes it.

    Is the complex MAKE TABLE simply not finished creating when the merge
    executes? The merge to Word in VB seems so flakey; it works, but often
    throws generic errors. Below is a code excerpt. Any ideas? Thanks.


    Set qdfMake = db.CreateQueryDef("", strSQL)
    qdfMake.Parameters(0) = Eval(strCtlOID)
    qdfMake.Execute

    appWord.Visible = True
    objWord.MailMerge.OpenDataSource Name:="E:\data.mdb",
    LinkToSource:=True, Connection:="TABLE tbltempOutputReport",
    SQLStatement:="SELECT * FROM [tbltempOutputReport]"
    objWord.MailMerge.Destination = wdSendToNewDocument
    objWord.MailMerge.Execute
     
  2. Loading...


  3. cjg.groups@gmail.com

    cjg.groups@gmail.com
    Expand Collapse
    Guest

    Some brainstorms that I'll look into:

    Is there a WaitUntil or PauseUntil statement that won't advance until a
    condition is met?
    Or maybe pause with a Do While loop until the condition is met?

    Does qdf.Execute have a return value for the above pause statements?

    Does the database's Tables collection send a signal when a new table is
    added, which could be watched for by a pause statement?

    Could a pause statement check that the table exists AND is populated
    before going on?

    Show a dialog box after table creation but before merge, making the
    user click OK to do the merge? That's one more step than I'd like,
    though.


    The other error that I sometimes get during the merge is maybe from
    Word, saying the data source is empty or has no records. Sure, because
    the query is not done creating the table when it tries to do the merge.
    Is there a standard solution to this? Thanks.


    cjg.groups@gmail.com wrote:
    > In Access 2003, I am using VB to build a complex MAKE TABLE query,
    > store it in a new Query Def, then execute it to make the table. I then
    > launch a Word mail merge using that new table as the data source. The
    > Word template is not bound to a data source.
    >
    > When I run the code, Word shows the Data Link window asking for a data
    > source. Trying to cancel says the data source is already open, but the
    > merge runs anyway. This happens often, but not always, and I can't
    > tell what causes it.
    >
    > Is the complex MAKE TABLE simply not finished creating when the merge
    > executes? The merge to Word in VB seems so flakey; it works, but often
    > throws generic errors. Below is a code excerpt. Any ideas? Thanks.
    >
    >
    > Set qdfMake = db.CreateQueryDef("", strSQL)
    > qdfMake.Parameters(0) = Eval(strCtlOID)
    > qdfMake.Execute
    >
    > appWord.Visible = True
    > objWord.MailMerge.OpenDataSource Name:="E:\data.mdb",
    > LinkToSource:=True, Connection:="TABLE tbltempOutputReport",
    > SQLStatement:="SELECT * FROM [tbltempOutputReport]"
    > objWord.MailMerge.Destination = wdSendToNewDocument
    > objWord.MailMerge.Execute
     
  4. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

  5. LJG

    LJG
    Expand Collapse
    Guest

    Hi Albert,

    I have downloaded your word merge and when testing the add template I keep
    getting an error:

    Run-time error 5174

    This file could not be found

    And suggests a file path C:\database\development\word\merge.888

    from your notes I assumed that the folder would be added? and that merge.888
    is used locate the folder and create a temp file name for the path.

    A folder 'word'has been created.

    Any help would be appreciated.

    TIA
    Les




    "Albert D.Kallal" <PleaseNOOOsPAMmkallal@msn.com> wrote in message
    news:%23M$imXZnGHA.4328@TK2MSFTNGP04.phx.gbl...
    > Try my word merge sample here:
    >
    > http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html
    >
    > You can run your code to create the new data, and then run my merge code.
    > (you can pass my code a query that is based on your temp table).
    >
    > Don't forget to read the usage stuff here:
    >
    > http://www.members.shaw.ca/AlbertKallal/wordmerge/page2.html
    >
    > --
    > Albert D. Kallal (Access MVP)
    > Edmonton, Alberta Canada
    > pleaseNOOSpamKallal@msn.com
    > http://www.members.shaw.ca/AlbertKallal
    >
     
  6. Albert D.Kallal

    Albert D.Kallal
    Expand Collapse
    Guest

    For some reason, often the word directly does not get created correctly.

    Try creating a dir called "word", and then try my example.....

    However, since you were able to create a template, then likely the dir did
    get created.

    Were you able to create a template, and add a few fields using my example?


    --
    Albert D. Kallal (Access MVP)
    Edmonton, Alberta Canada
    pleaseNOOSpamKallal@msn.com
    http://www.members.shaw.ca/AlbertKallal
     
  7. LJG

    LJG
    Expand Collapse
    Guest

    Hi Albert,

    All is working fine now. It appears word started on one occasion and
    stalled, so each time I tried it again it thought word was open.

    Simply cleared the temp file word created and that sorted it.

    Great little tool, many thanks

    Les
    "Albert D.Kallal" <PleaseNOOOsPAMmkallal@msn.com> wrote in message
    news:uLxAEsjnGHA.2364@TK2MSFTNGP02.phx.gbl...
    > For some reason, often the word directly does not get created correctly.
    >
    > Try creating a dir called "word", and then try my example.....
    >
    > However, since you were able to create a template, then likely the dir did
    > get created.
    >
    > Were you able to create a template, and add a few fields using my example?
    >
    >
    > --
    > Albert D. Kallal (Access MVP)
    > Edmonton, Alberta Canada
    > pleaseNOOSpamKallal@msn.com
    > http://www.members.shaw.ca/AlbertKallal
    >
     
  8. cjg.groups@gmail.com

    cjg.groups@gmail.com
    Expand Collapse
    Guest

    Albert,

    I first saw MergeSingleWord a month ago and was confused. Is it a full
    "application", creating a merge template in Word and doing the merge?
    Since I have a template and a data table, which functions from your
    module do I need to do the merge? The code is more advanced than I am.

    You have been a big help already in my project. Your printer code is
    in my database, and your search page is bookmarked for later use.
    Thank you!


    Albert D.Kallal wrote:
    > Try my word merge sample here:
    >
    > http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html
    >
    > You can run your code to create the new data, and then run my merge code.
    > (you can pass my code a query that is based on your temp table).
    >
    > Don't forget to read the usage stuff here:
    >
    > http://www.members.shaw.ca/AlbertKallal/wordmerge/page2.html
    >
    > --
    > Albert D. Kallal (Access MVP)
    > Edmonton, Alberta Canada
    > pleaseNOOSpamKallal@msn.com
    > http://www.members.shaw.ca/AlbertKallal
     

Share This Page