TransferText command

[Guest]

At the end of processing I export out a fixed length record using a query.
There are nine different queries that I run.
Sometimes the text file is created and sometimes not. It appears the problem
is that one query isn't finished before the next one starts. I have tried
putting a for loop with a count to slow things down but that also is hit or
miss.

Below is the code that I am using.
What I can do to make the program slow down enough to let each query run and
successfully export the text file?

Thanks in advance for any help.
DoCmd.TransferText acExportFixed, "Max&ErmasDownload", _
"qryDownload4", "J:\Max&Ermas\Weekly\MaxErma4.txt"

DoCmd.TransferText acExportFixed, "Max&ErmasDownload", _
"qryDownload5", "J:\Max&Ermas\Weekly\MaxErma5.txt"

conn.close

Dan

 

[John Nurick]

Hi Dan,

Have you tried using
DoEvents
between each call to TransferText?

 

[Guest]

Thanks for the info.
Since doevents returns an integer I coded "int = doevents() and still the
same problem.
Do you have an example I can use?

Thanks
Dan

 

[John Nurick]

The return value of DoEvents has no meaning in VBA, so there's no point
capturing it.

This isn't an everyday problem. Please check each of the following:

1) When you step through the code in the debugger, are all the text
files always created normally?

2) You say the files are sometimes "not created". Do you mean that when
the procedure starts there is no file called (say)
Max&Ermas\Weekly\MaxErma4.txt
in J:\, and when the procedure finishes there is still no file of that
name (as opposed to an empty file or a file that doesn't contain the
expected data)?

3) Disable any error handling in the procedure. Do you get any error
messages when files are not written, and what are they?

Also, I notice the statement
conn.close
at the end of your code snippet. Where does this fit in? Why isn't it
conn.Close
?