Word 2000 request returns outdated Access query

[Richard]

I'm looping through code in VB6, using ADOX to modify an Access 2000
query and update the catalog. It then passes the query name (not its
SQL) to a macro in an open Word 2000 document. The macro then connects
via DDE to the query to run a Mail Merge.

About 25% of the time, however, its picking up the previous
iteration's query. Is this likely because Access itself hasn't re-read
the catalog, or is there a DDE cache that has to be flushed? Any
suggestions?

 

[Cindy M -WordMVP-]

Hi Richard,

I'm looping through code in VB6, using ADOX to modify an Access 2000
query and update the catalog. It then passes the query name (not its
SQL) to a macro in an open Word 2000 document. The macro then connects
via DDE to the query to run a Mail Merge.

About 25% of the time, however, its picking up the previous
iteration's query. Is this likely because Access itself hasn't re-read
the catalog, or is there a DDE cache that has to be flushed?

Are you changing the query name to which the main merge document is
linked? Or just the query def of an existing query in Access?

I'd say your best chance to find out which the problem is would be to
put a couple of checks in your code
- make sure the query actually exists in Access (maybe via DAO, as
that's Jet's "native" language)

As for the Word side, what properties and methods is your code using?
I'd suggest checking these values before and after the assignment (maybe
with a doevents or a do-loop to give things a chance to link through;
there can be a noticeable delay with DDE).

You might also want to see if ODBC gives a more reliable connection.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail

 

[Richard]

Hi Cindy.

I'm just changing the underlying SQL (it's my way of faking parameter
queries with DDE). The VB6 code seems fine - after I update the
catalog I re-read the query and it's changed as expected.

And yes, I expect if I build a delay into Word before the MailMerge it
will give the system time to "settle" - kinda guesswork, though.

I gave up on ODBC with Mail Merge in Word 2000 a couple of weeks ago -
wish I could remember why. Maybe it's in my notes somewhere...

Thanks.

 

[Richard]

"I gave up on ODBC with Mail Merge in Word 2000..."

Now I remember - it was when I was using "real" parametrized queries,
and therefore had to use DDE. Learnt that from one of your articles!

 

[Cindy M -WordMVP-]

Hi Richard,

"I gave up on ODBC with Mail Merge in Word 2000..."

Now I remember - it was when I was using "real" parametrized queries,
and therefore had to use DDE. Learnt that from one of your articles!

Ahhh Then if you're anyway going the route of picking up the
parameters in VB6 and changing the query, you might try going back to
ODBC. And if these query strings are 256 characters or less, rather than
linking back up to the data source, I'd try changing the .QueryString
property. Only if you need the additional 256 characters would it make
sense to run OpenDataSource again, so that you can use SqlStatement1.

(e-mail address removed) (Richard) wrote in message

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail

 

[Richard]

Thanks. Back to ODBC it is..

Hi Richard,

Ahhh Then if you're anyway going the route of picking up the
parameters in VB6 and changing the query, you might try going back to
ODBC. And if these query strings are 256 characters or less, rather than
linking back up to the data source, I'd try changing the .QueryString
property. Only if you need the additional 256 characters would it make
sense to run OpenDataSource again, so that you can use SqlStatement1.


Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)


This reply is posted in the Newsgroup; please post any follow question or
reply in the newsgroup and not by e-mail