Avoiding "Data Link" during Word merge

[cjg.groups]

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

 

[cjg.groups]

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.

 

[Albert D.Kallal]

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

 

[LJG]

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]

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?

 

[LJG]

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

 

[cjg.groups]

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!