MS Access 2007 - VBA Module - wait for condition, loop, continue

[boggsie]

I am so very new to this I am almost embarassed to post.

In this scenario:
Data files incoming from different sources.
Multiple files are required to proceed.
Files are being dropped in a particular folder.

So, I want to:

Dim sFileA = c:\File_1.txt
Dim sFileB = c:\File_2.txt
Dim sFileC = c"\File_3.txt

count n = 1

If Exists (sFileA, sFileB, sFileC) = No
wait for 30 seconds
count = n + 1
if n < 11
loop
else
exit

So the theory is:
If all three files do NOT exist
add 1 to my count
wait for 30 seconds
if my count is less than 11
loop back to check again
else exit

barring major infrastructure issues, the files never fail to show up in five minutes. if they do fail to show up, someone has bigger issues than me not being able to generate my report data.

Thanks for looking. I'm searching here now, but wanted to post this while I search to see if anyone can offer up a hint.

Best regards,
-boggsie

 

[boggsie]

Another thought on this ... I know I need three files. Those will be the only files in the folder.

dir folder
if files <3
count to 30
loop
else
end

I know I have three files and I can proceed or I wait 30 seconds and loop back.

 

[boggsie]

This is where I ended up:

I concluded that waiting for specific files was not necessary. I only need to ensure that I have accounted for the total number of files required to start the next process.

I call CheckNumberFiles from a separate process to ensure that I have the number of files I need to go [do until filecount > n], or loop (up to a number of times (loopcount) and wait (sleep 10000 - 10 seconds) as part of each loop.

The sleep requires the Private Declare "kernel3" from above. This is cool because it does not PEG the cpu the way some other count or time elapse methods might.

Option Compare Database
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

Public Function GetFileCount(folderspec As String) As Integer
   Dim fso As Object
   Set fso = CreateObject("Scripting.FileSystemObject")
   If fso.FolderExists(folderspec) Then
      GetFileCount = fso.GetFolder(folderspec).Files.Count
   Else
      GetFileCount = -1
   End If
End Function
Sub CheckNumberFiles()
  Const sProjTargOut As String = "Some Important Folder to Monitor"
  Dim fileCount As Long
  Dim loopcount As Long
  Dim datLoopStart As Date
  Dim intSeconds As Integer
  
  loopcount = 0
  
  Do Until fileCount > 9
  fileCount = GetFileCount(sProjTargOut)
        
      Dim datTime As Date
    
      datTime = DateAdd("s", intSeconds, Now)
    
      Do
        Sleep 10000
        DoEvents
      Loop Until Now >= datTime
        
  loopcount = loopcount + 1
  If loopcount > 30 Then Exit Do
  Loop
End Sub

In the interest of full disclosure, I want to make it absolutely obvious that this was the result of probably no less than 150 different searches between the OP and now. So, if I grabbed some of your work and didn't credit, I offer my sincerest apologies and respect.

Best Regards,
-boggsie

 

[Andrzejek]

  loopcount = 0
  
  Do Until fileCount > 9[green]
      'How come you check if you have more than 9 files?
      'You were looking for only 3 files in your OP[/green]
      fileCount = GetFileCount(sProjTargOut)
        
      Dim datTime As Date[green]
      'This Dim should be at the top of your proc
      'Why Dim it on every loop?
      'You add here 0 "s"econds to Now, why?[/green]
      datTime = DateAdd("s", intSeconds, Now)
    
      Do
        Sleep 10000
        DoEvents
      Loop Until Now >= datTime[green]
      'This Do loop is not needed since after Sleep
      'your Now will always be > than 10 seconds ago[/green]

      loopcount = loopcount + 1
      If loopcount > 30 Then Exit Do
  Loop

Have fun.

---- Andy

 

[boggsie]

Thank you for your response, Andy.

The end to end runs as follows. I don't have much ability to re-architect the process.

I submit three separate query requests to the "job".
The job responds with a total of ten files.
These ten files include data to be crunched and formatted.
My original thought was to wait until the last file of each process had arrived.
My follow-up thought was to simply wait until I was certain that all ten files were available.

That is how we went from three files by name to > 9.

The way I understood / read, the DoEvents and loop until is helpful to keep Access from consuming the CPU, but like I said, I've been grabbing and trying code for quite a few hours over the last 1.5 days and this finally worked.

Best regards,
-boggsie