Avoiding "Data Link" during Word merge

#1 (**permalink**) 28-Jul-2006, 08:29 AM

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]"
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/12420-avoiding-data-link-during-word-merge.html
objWord.MailMerge.Destination = wdSendToNewDocument
objWord.MailMerge.Execute
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12420

#2 (**permalink**) 28-Jul-2006, 08:29 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12420
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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12420

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

#3 (**permalink**) 28-Jul-2006, 08:29 AM

Try my word merge sample here:

http://www.members.shaw.ca/AlbertKal.../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/AlbertKal...rge/page2.html

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12420
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12420
http://www.members.shaw.ca/AlbertKallal

#4 (**permalink**) 28-Jul-2006, 08:29 AM

Hi Albert,

I have downloaded your word merge and when testing the add template I keep
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12420
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" wrote in message
news:%23M$imXZnGHA.4328@TK2MSFTNGP04.phx.gbl...
> Try my word merge sample here:
>
> http://www.members.shaw.ca/AlbertKal.../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/AlbertKal...rge/page2.html
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@msn.com
> http://www.members.shaw.ca/AlbertKallal
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12420
>

#5 (**permalink**) 28-Jul-2006, 08:29 AM

For some reason, often the word directly does not get created correctly.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12420
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12420

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

#6 (**permalink**) 28-Jul-2006, 08:29 AM

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" wrote in message
news:uLxAEsjnGHA.2364@TK2MSFTNGP02.phx.gbl...
> For some reason, often the word directly does not get created correctly.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12420
>
> Try creating a dir called "word", and then try my example.....
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12420
>
> 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 (**permalink**) 28-Jul-2006, 08:30 AM

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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12420

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/AlbertKal.../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/AlbertKal...rge/page2.html
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKallal@msn.com
> http://www.members.shaw.ca/AlbertKallal
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=12420