Solved

Filesearch only returning a limited set of records

Posted on 2004-09-13
17
210 Views
Last Modified: 2008-02-01

I have a filesearch routine that is only returning 775 or the 1138 files out there on my c:\clients directory.  Does anyone see anything wrong with this code?  I have stripped out the code as much as I can to simplify the code for debugging, but I only get 775 values back.

Private Sub bScan_Click()
' The purpose of this routine is to find all pdf files located on N drive that have the SOxxxx string
' Located in them.  SO pdf files are names with a standard of Invxxxx soxxxx.pdf.  This routine will attempt
' to match these files up with their so records in the system.

' Written by Scott Huotari 2/11/2004

'Dim fs As Object
Dim i As Long

vDefPath = "N:\clients\"            'This is the default path for the client files

'Set fs = Application.FileSearch

DoCmd.Hourglass True
varReturn = SysCmd(acSysCmdSetStatus, "Processing...  ")

With Application.FileSearch
    .LookIn = vDefPath
    .SearchSubFolders = True
    .FileName = "*SO*.pdf"
    'If .Execute(SortBy:=msoSortbyFileName, SortOrder:=msoSortOrderAscending) > 0 Then
     If .Execute() > 0 Then
        varReturn = SysCmd(acSysCmdSetStatus, "Processing...  " & .foundfiles.Count & " files...")
        MsgBox "There were " & .foundfiles.Count & " file(s) found."
        For i = 1 To .foundfiles.Count
            'MsgBox .FoundFiles(i)
            vso = getso(.foundfiles(i))
            varReturn = SysCmd(acSysCmdSetStatus, "Processing...  " & i & " of " & .foundfiles.Count & " files...")
            If (vso <> "") Then
                vSQL = "update serviceorder set SOPath = '" & .foundfiles(i) & "' where ID = " & vso
               
                Debug.Print vSQL
                DoCmd.SetWarnings False
                DoCmd.Hourglass True
                DoCmd.RunSQL vSQL
                DoCmd.Hourglass False
                DoCmd.SetWarnings True
            End If
        Next i
    Else
        MsgBox "There were no files found in " & vDefPath
    End If
End With


varReturn = SysCmd(acSysCmdSetStatus, " ")


End Sub
0
Comment
Question by:ccsistaff
  • 7
  • 7
  • 2
  • +1
17 Comments
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 12048032
You didn't specify the code for your FileSearch function, but an easy trap to fall in is the issue of case sensitivity. Does your Filesearch function look for *SO* files or does it also look for *so* files (and for that matter *So* and *sO*)?

Best would be to do the search something like (in pseudo-code):

-Before adding a file to you FoundFiles property/variable check for a match as follows:
- if  Uppercase(YourFilter) is in Uppercase(SomeFile) then add it to your FoundFiles

Regards
Pierre
0
 
LVL 3

Author Comment

by:ccsistaff
ID: 12048082
I tried changing the .filename parameter to *so*.pdf and *SO*.pdf and it does not appear to be case sensitive.  I always get 775 of the 1138 files I get when I do a search from the windows search dialog box.

Thank you for your help,

Scott
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 12048183
What happens if you try this:
 
  If .Execute(SortBy:=msoSortbyFileName, SortOrder:=msoSortOrderAscending, true) > 0 Then


instead of this:
  If .Execute() > 0 Then
0
 
LVL 3

Author Comment

by:ccsistaff
ID: 12048317
That is what I originally had and it totally stopped working.  Just returns a zero count and the function exits with my "There where no files found" routine I have.  

Thanks,

Scott
0
 
LVL 3

Author Comment

by:ccsistaff
ID: 12048522
Actually the code I had did not have the true parameter.  When I try to add the tru parameter, it gives me an error.
0
 
LVL 3

Author Comment

by:ccsistaff
ID: 12048600
More information in my troubleshooting of this:  I took this sample code and get 774 records also.  I am running access 2003

Private Sub Command1_Click()
         Dim vItem As Variant
         With Application.FileSearch
            .NewSearch
            .FileName = "*so*.pdf"
            .LookIn = "n:\clients"
            .SearchSubFolders = True
            .Execute
            For Each vItem In .FoundFiles
               Debug.Print vItem
               Debug.Print i
               i = i + 1
            Next vItem
         End With
End Sub
0
 
LVL 27

Expert Comment

by:jjafferr
ID: 12048859
Copied this from Access help,
Check it out

Set fs = Application.FileSearch
With fs
    .LookIn = "n:\clients"
    .SearchSubFolders = True
    .FileName = "*so*.pdf"
    .SearchSubFolders = True
    If .Execute() > 0 Then
        MsgBox "There were " & .FoundFiles.Count & _
         " file(s) found."
        For i = 1 To .FoundFiles.Count
            MsgBox .FoundFiles(i)
        Next i
    Else
        MsgBox "There were no files found."
    End If
End With
0
 
LVL 3

Author Comment

by:ccsistaff
ID: 12049993
I just tried to create a ned empty database with the sample code we have been playing with and it still only returns the first 775 records.

I am thinking that I am running into some kind of array limitiation of the foundfiles routine.

Scott
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 12051989
Try this:

Function CustomFindFile(strFileSpec As String)
   Dim fsoFileSearch As FileSearch
   Dim NumFiles As Integer
   
   Set fsoFileSearch = Application.FileSearch
   With fsoFileSearch
      .NewSearch
      .LookIn = "c:\delphi" '"N:\clients\"
      .FileName = "*.dpr" 'strFileSpec ' Set this to *so*.pdf
      .SearchSubFolders = True
      NumFiles = 0
      If .Execute() > 0 Then
         For Each varFile In .FoundFiles
            NumFiles = NumFiles + 1
            'strFileList = strFileList & varFile & vbCrLf
         Next varFile
      End If
   End With
   MsgBox "Files found: " & NumFiles 'strFileList
End Function

Be sure to have "Microsoft Office 10 Object Library" referenced.
1.) From Access press Alt +F11 to go to the visual basic editor
2.) Select Tools, References and click on "Microsoft Office 10 Object Library"

0
 
LVL 3

Author Comment

by:ccsistaff
ID: 12055662
I created a new module (module1) and ran the customfindfile("") function.  It returns 775 records.  I ran the search on the network share using microsoft's find file and it returns 1138 file count.

I also moved my entire network share to my local hard drive and I get the same file count.  Are was all sure that there isn't some kind of a limit on the size of the .filecount object?

I also do not have the microsoft office 10 object library and am using the microsoft office 11 object library.

Scott
0
 
LVL 27

Accepted Solution

by:
jjafferr earned 500 total points
ID: 12055707
Scott
Stupid it sounds, but why don't you break your search into 2 parts,
1st will search A-M
2nd part search M-Z

then join them
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 12058625
Do you have to search sub-directories as well?
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 12059193
If you don't need to search the sub-directories, the following should help. I based it on the WinAPI FindFile functions.

Const MAX_PATH As Integer = 260
Const FILE_ATTRIBUTE_DIRECTORY = 16
Const INVALID_HANDLE_VALUE As Long = -1

Type FILETIME
    dwLowDateTime As Long
    dwHighDateTime As Long
End Type

Type WIN32_FIND_DATA
    dwFileAttributes As Long
    ftCreationTime As FILETIME
    ftLastAccessTime As FILETIME
    ftLastWriteTime As FILETIME
    nFileSizeHigh As Long
    nFileSizeLow As Long
    dwReserved0 As Long
    dwReserved1 As Long
    cFileName As String * MAX_PATH
    cAlternateFileName As String * 14
End Type


Declare Function FindFirstFile Lib "kernel32" Alias "FindFirstFileA" _
    (ByVal lpFileName As String, lpFindFileData As WIN32_FIND_DATA) As Long
Declare Function FindNextFile Lib "kernel32" Alias "FindNextFileA" _
    (ByVal hFindFile As Long, lpFindFileData As WIN32_FIND_DATA) As Long
Declare Function FindClose Lib "kernel32" (ByVal hFindFile As Long) As Long

Function TrimNull(s As String) As String
    Dim i As Long
    i = InStr(1, s, vbNullChar)
    If i = 0 Then
        TrimNull = s
    Else
        TrimNull = Left$(s, i - 1)
    End If
End Function

Public Sub WinAPIFindFile(path As String, FindStr As String)
    Dim s As String
    Dim iSearchHandle As Long
    Dim pFindFileBuff As WIN32_FIND_DATA
    Dim NumFiles As Long
   
    s = ""
    NumFiles = 0
   
   
      iSearchHandle = FindFirstFile(path & FindStr, pFindFileBuff)
      If iSearchHandle <> INVALID_HANDLE_VALUE Then
        s = TrimNull(pFindFileBuff.cFileName)
        If Not (pFindFileBuff.dwFileAttributes And FILE_ATTRIBUTE_DIRECTORY) _
            And (s <> ".") And (s <> "..") _
            Then NumFiles = NumFiles + 1
                 
        Do While FindNextFile(iSearchHandle, pFindFileBuff)
            s = TrimNull(pFindFileBuff.cFileName)
            If Not (pFindFileBuff.dwFileAttributes And FILE_ATTRIBUTE_DIRECTORY) _
               And (sBuff <> ".") And (sBuff <> "..") _
               Then NumFiles = NumFiles + 1
        Loop
        Call FindClose(iSearchHandle)
      End If
  MsgBox "Number of files found: " & NumFiles
End Sub


If you do need to search sub-directories, it becomes a bit more involved, as you need to first recurse to find them all and then apply the above (not rocket science, but more work. That's why I'm asking first)

Regards
Pierre
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 12074244
ANy results, Scot?
0
 
LVL 3

Author Comment

by:ccsistaff
ID: 12105117
No, I do need to search sub directories, so the above code would not work well.  I just cannot figure out why this code wont return more than the 775 records.


Weird....  Scott
0
 
LVL 14

Expert Comment

by:Pierre Cornelius
ID: 12106598
I've added the sub directory functionality to the prevous written procedure. All I do when a match is found is count it, but you should modify it to do with what you want. I have tested it and it works fine:

Public Sub WinAPIFindFile2(path As String, FindStr As String)
    Dim s As String
    Dim iSearchHandle As Long
    Dim pFindFileBuff As WIN32_FIND_DATA
    Dim NumFiles As Long
    Dim NumFldrs As Long
    Dim i As Long
    Dim ChkIndex As Long
   
   
    s = ""
    NumFiles = 0
    NumFldrs = 0
    ReDim Folders(0)
    ReDim FoldersToCheck(1)
    FoldersToCheck(1) = path
    ChkIndex = 1

    If Right(path, 1) <> "\" Then path = path & "\"
    Folders(0) = path

    'first enumerate all sub-folders
    Do
     iSearchHandle = FindFirstFile(FoldersToCheck(ChkIndex) & "*.", pFindFileBuff)
     If iSearchHandle <> INVALID_HANDLE_VALUE Then
        s = TrimNull(pFindFileBuff.cFileName)
        If (pFindFileBuff.dwFileAttributes And FILE_ATTRIBUTE_DIRECTORY) _
            And (s <> ".") And (s <> "..") Then
          ReDim Preserve Folders(UBound(Folders) + 1)
          Folders(UBound(Folders)) = FoldersToCheck(ChkIndex) & s & "\"
          ReDim Preserve FoldersToCheck(UBound(FoldersToCheck) + 1)
          Folders(UBound(FoldersToCheck)) = FoldersToCheck(ChkIndex) & s & "\"
          NumFldrs = NumFldrs + 1
        End If

        Do While FindNextFile(iSearchHandle, pFindFileBuff)
          s = TrimNull(pFindFileBuff.cFileName)
          If (pFindFileBuff.dwFileAttributes And FILE_ATTRIBUTE_DIRECTORY) _
              And (s <> ".") And (s <> "..") Then
            ReDim Preserve Folders(UBound(Folders) + 1)
            Folders(UBound(Folders)) = FoldersToCheck(ChkIndex) & s & "\"
            ReDim Preserve FoldersToCheck(UBound(FoldersToCheck) + 1)
            FoldersToCheck(UBound(FoldersToCheck)) = FoldersToCheck(ChkIndex) & s & "\"
            NumFldrs = NumFldrs + 1
          End If
        Loop
        Call FindClose(iSearchHandle)
     End If 'if search handle valid

     'ReDim Preserve FoldersToCheck(UBound(FoldersToCheck) - 1)
     ChkIndex = ChkIndex + 1
    Loop While UBound(FoldersToCheck) >= ChkIndex
 
  ReDim FoldersToCheck(0)
 
  'now that we have all the subdirectories, let's start searching
    ChkIndex = 0
    Do
      iSearchHandle = FindFirstFile(Folders(ChkIndex) & FindStr, pFindFileBuff) ' Find first file and create search handle
      If iSearchHandle <> INVALID_HANDLE_VALUE Then
        s = TrimNull(pFindFileBuff.cFileName)
        If Not (pFindFileBuff.dwFileAttributes And FILE_ATTRIBUTE_DIRECTORY) _
            And (s <> ".") And (s <> "..") _
            Then NumFiles = NumFiles + 1

        Do While FindNextFile(iSearchHandle, pFindFileBuff)
            s = TrimNull(pFindFileBuff.cFileName)
            If Not (pFindFileBuff.dwFileAttributes And FILE_ATTRIBUTE_DIRECTORY) _
               And (sBuff <> ".") And (sBuff <> "..") _
               Then NumFiles = NumFiles + 1
        Loop
        Call FindClose(iSearchHandle)
      End If
      ChkIndex = ChkIndex + 1
    Loop While UBound(Folders) >= ChkIndex
  MsgBox "Number of folders searched: " & Str(NumFldrs) & vbCrLf & _
         "Number of files found: " & Str(NumFiles)
End Sub

Let me know your results.

Good luck.

Pierre
0
 
LVL 6

Expert Comment

by:Plamodo
ID: 13961454
For the record, I ran a "FileSearch.Execute" routine on a large directory and it returned 3339 records for me, so it doesn't appear to have a limit - or at least not in the range being suggested.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now