Test for existence of file, try again every nn minutes if not found

[kq]

MS Access Database A produces a file every morning which, when finished, exports a text file to f:\mydirectory. Database B (mine) imports the file. OCCASIONALLY the file is not in f:\mydirectory at the expected time.

I'd like to automatically check for the existence of the txt file using the date created date (to match today's date) and, if found - proceed as usual; if not found, wait 10 minutes and try again. Thoughts?

 

[SkipVought]

Hi,

From what application are you running the VBA?

What code are you working with? Please post.

Skip,
[sub]
To be safe on the [red]FOURTH[/red],
Don't take a [red]FIFTH[/red] on the [red]THIRD[/red]
Or you might not come [red]FORTH[/red] on the [red]FIFTH[/red]
[red][highlight blue]FORTH[/highlight][/red][white][highlight red]WITH[/highlight][/white] [/sub]

 

[kq]

I'm working in Access -- haven't even begun the code because I didn't know where to begin!

 

[PHV]

Have a look at the Dir and FileDateTime functions in the VBA help.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[CautionMP]

kq,
Checking for the file is easy.

Function FileExists(Filename As String) As Boolean
If Dir(Filename) = "" Then
  FileExists = False
Else
  FileExists = True
End If
End Function

Function FileExistsFSO(Filename As String) As Boolean
Dim objFSO As Object
Set objFSO = CreateObject("Scripting.FileSystemObject")
FileExistsFSO = objFSO.FileExists(Filename)
Set objFSO = Nothing
End Function

...try again every nn minutes...

Since your using Access just put the function in a form and use the [tt]Timer[/tt] event to keep checking for the file until it is found.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[kq]

Thanks, CMP & everyone! That would've taken me quite a while to figure out! Is there any way to avoid using the timer event on the form? Can do, but would love to avoid for some reason....

 

[CautionMP]

kq,
If you are comfortable with API and Callback functions take a look at SetTimer / KillTimer functions available in the user32 library.

Hope this helps,
CMP

[small]For the best results do what I'm thinking, not what I'm saying.[/small]
(GMT-07:00) Mountain Time (US & Canada)

 

[kq]

oooh -- sounds like I could do some damage! Thanks! I'll give it a whirl! appreciate your assistance ...