Delay a portion of code from running

[chec]

I have code that creates a file to a shared network drive. After the file is created it is attached to an email. I need to delay the attach portion of the code until the file has been created. Once in a while the code will bomb because it's trying to attach the file while it's still being created. My current fix: I have a message box that prompts before the file attach portion. I would would like to do away the message box if possible. Does anybody have any ideas? Thanks for your help!!

 

[beetee]

The only reason I can think that the file is not 'complete' before your code reaches the 'attach' portion is that the file creation task is running asynchronously (e.g. via the shell command). if this is the case, check out this site:

http://www.experts-exchange.com/Programming/Programming_Languages/Visual_Basic/Q_20380302.html

The ideal solution is to devise a way to tell that the file creation process is complete, then do the attach. Any time you resort to a time delay you are asking for trouble.

In any case, here's some time delay code:

dim Start as date
start = now
do while (start = now)
doevents
loop

this will pause, on the average, for .5 seconds (depending on how close we are to the next second).

 

[ajdesalvo]

One way to tell if a file is complete is to name it something other than the final file name, then when it is done, rename it to the file name or extension your are looking for.

For example, while the file is being built, call it myfile.xxx. Then when the file is finished, use the Name statement to rename it something else, myfile.dat.

Then in your loop, test for the .dat extension, when you see it, it is time to move on!

Good luck! Anthony J. DeSalvo
President - ScottTech Software
"Integrating Technology with Business"

 

[danvlas]

You could trap the error in an error handling routine:
On Error GoTo ErrHandler
...your code
Exit Sub
ErrHandler:
'Retries=Retries+1
'If Retries>=500 Then
'Resume Next 'or whatever
'Else
Resume
'End If
End Sub

In this way the code tries and tries and tries until it succeeds. Use a counter to get out of a (possibly) infinite loop, as in the commented code.

If you pass the control from the database to another application, you could signal from the other application whether the file was successfully attached and if not, jump back with a GoTo statement until everything is OK. But again make sure you don't get stuck in an infinite loop-this type of action is the best opportunity to resume code endlessly...

Variation to beetee's code:

dim Start as Long
start = Timer
While (Timer - start)< 5 'seconds
DoEvents
Wend

Good luck


Daniel Vlas
Systems Consultant

 

[chec]

Thanks for all the suggestions. I'll definitely stay away from the time delay solution and go with the file check before attaching the file. Thanks again!!