Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Filesearch only returning a limited set of records

Posted on 2004-09-13
17
Medium Priority
?
232 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

885 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