Access chokes on too many Word docs

[Rod]

I have an Access program which sequentially opens 300 Word documents. Each
Word document contains about 50 tick box controls.
all the program does is read the ticks and writes them to a table.

About half way through the program stops, Access throws up what looks like a
more or less random error message, and Word complains that it can't leave
design view and create one of the tick boxes.

If I change the order in which the docs are read then the document that it
failed on is happily read without error.

If I close Word and re-open Word every 50 docs then it is better and I can
read maybe 75% of the docs at one go.

I have been careful to close and set to nothing all objects that I create in
the process, this opening of many docs is something I do lot of without
problem. This problem seems to be related to the tick box controls in Word.

I am at a loss, any suggestions welcome.


many thanks

Rod

 

[Jezebel]

Try running your code with the Task Manager showing and watch the process
count and memory usage. Neither Word nor Access is particularly good at this
sort of thing; Access in particular is a seriously lousy piece of coding
(just look at the literally hundreds of KB articles with work-arounds for
its various problems).

You could try doing *all* the work at the Word end: there's no problem
opening a database (using DAO or ADO) from Word VBA and writing to a
table -- at least that way you'll have only one Office app running, instead
of two.

 

[Albert D. Kallal]

Do you correctly close each word doc after?

I would try putting a doEvents in the loop, and see if that helps.

You don't mention what version, but also make sure all updates are
installed, as those updates often fix things like memory leaks etc.

 

[Rod]

Do you correctly close each word doc after?

I only do it after every 25 docs, I could try it after every one. but yes I
close it and set to nothing.

I would try putting a doEvents in the loop, and see if that helps.

You will have to flesh this out a bit for me, I don't follow.

You don't mention what version, but also make sure all updates are
installed, as those updates often fix things like memory leaks etc.

XP, and recently updated
many thanks

 

[Charlie]

You might consider using one instance of word.application. Within your loop, close out the document and open the next document each time. After the loop is complete, then set to nothing. At the very least you might try to use the same instance and Set it each time without setting it to nothing until the loop is finished

That's just an idea off the top of my head. I have little experience with automating Word

Good luck
Charlie