Solved

Filesearch only returning a limited set of records

Posted on 2004-09-13
17
219 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

739 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