Skip to content

Comparing Files in a Folder with Files in a Table

How to compare actual files in a folder with files names in a table.

Access Archon #128


Introduction

Thanks to AW reader, Keith Hungate, for suggesting the topic of this article.  He wrote to ask if I could suggest a method for comparing file names stored in an Access table to actual files stored in a folder on the hard disk, to see if any files have not been recorded in a table.  This can be done by using the FileSystemObject (part of the Scripting Runtime library) to iterate through the scanned files in the folder and put their names into an Access table, and then creating a query with the Find Unmatched Query Wizard to compare the file names in the folder with the entries in the table.


Getting File Names from a Folder

To pick up file names from a folder, and store them in a table for purposes of comparison, you need to use several components of the Scripting Runtime Library.  Be sure to use the library name (Scripting) in front of the component name, when declaring objects, because some of the object names also occur in other libraries (for example, the Files collection is a member of the Outlook library as well as the Scripting library).

I first created a simple table, tblFilesFromFolder, to hold the file names from the folder.  This table has two fields, FileName and DateChecked.  Another table, tblRecordedFiles, contains the file information that is to be matched against the files in the folder.  It has three fields, an AutoNumber FileID field, and FileName and DateEntered.

Another small table, tblFolderPath, has only one field, FolderPath.  This table stores the folder path, and it is the record source of a dialog form, fdlgSelectFolderPath, shown in Figure A.

Figure A.  Entering a folder path where files are stored.

To find out whether any files in the designated folder were not recorded in tblRecordedFiles, I made a query using the Find Unmatched query wizard.  This query (qryMissingFiles) lists any files in tblFilesFromFolder that are not found in tblRecorded Files.


VBA Code

The first procedure listed below runs from the Check Files command button on fdlgSelectFolderPath.  It checks whether a file path has been entered into the text box, and if so, calls the GetFilesFromFolder procedure with the file path as its argument.

The GetFilesFromFolder procedure first clears tblFilesFromFolder of old entries, using a SQL string.  Then several components of the FileSystemObject are used to check whether the folder path is valid, and then to iterate through the files in the folder path, putting the name of each file into a new record in tblFilesFromFolder, using a DAO recordset.  Finally, the query qryMissingFiles is counted.  If it has no records, then a message box reports that all the files in the folder were found in the table; otherwise, a message reports that some files were missed, and the qryMissingFiles query is opened to display the names of the missing file(s), as shown in Figure B.

Figure B.  A query showing unmatched files

Private Sub cmdCheckFiles_Click()

 

On Error GoTo ErrorHandler

 

   Dim strFolderPath As String

   Dim strPrompt As String

   Dim strTitle As String

  

   strFolderPath = Nz(Me![txtFolderPath].Value)

   If strFolderPath <> “” Then

      Call GetFilesNamesFromFolder(strFolderPath)

   Else

      strPrompt = “Please enter a folder path”

      strTitle = “Folder path missing”

      MsgBox strPrompt, vbCritical + vbOKOnly, strTitle

      Me![txtFolderPath].SetFocus

   End If

  

ErrorHandlerExit:

   Exit Sub

 

ErrorHandler:

   MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description

   Resume ErrorHandlerExit

 

End Sub

 

Public Sub GetFilesNamesFromFolder(strFolderPath As String)

 

On Error GoTo ErrorHandler

 

   Dim fso As Scripting.FileSystemObject

   Dim fld As Scripting.Folder

   Dim fil As Scripting.File

   Dim strSQL As String

   Dim dbs As DAO.Database

   Dim rst As DAO.Recordset

   Dim strPrompt As String

   Dim strTitle As String

   Dim strTable As String

   Dim intMissingCount As Integer

  

   ‘Clear table of old file names

   strTable = “tblFilesFromFolder”

   strSQL = “DELETE * FROM ” & strTable

   DoCmd.SetWarnings False

   DoCmd.RunSQL strSQL

  

   Set fso = CreateObject(“Scripting.FileSystemObject”)

   If Not fso.FolderExists(strFolderPath) Then

      ‘Specified folder does not exist

      strPrompt = “Please enter a valid folder path”

      strTitle = “Folder path not found”

      MsgBox strPrompt, vbCritical + vbOKOnly, strTitle

      GoTo ErrorHandlerExit

      Forms![fdlgSelectFolder].SetFocus

   Else

      ‘Folder exists

      Set dbs = CurrentDb

      Set rst = dbs.OpenRecordset(strTable)

      Set fld = fso.GetFolder(strFolderPath)

      For Each fil In fld.Files

         rst.AddNew

         rst![FileName] = fil.Name

         rst![DateChecked] = Date

         rst.Update

      Next fil

   End If

  

   ‘Check whether any missing files were found

   intMissingCount = Nz(DCount(“*”, “qryMissingFiles”))

   Debug.Print “Missing count: ” & intMissingCount

   If intMissingCount = 0 Then

      strTitle = “Finished”

      strPrompt = “All files in ” & strFolderPath & ” have been recorded”

      MsgBox strPrompt, vbInformation + vbOKOnly, strTitle

   ElseIf intMissingCount > 0 Then

      strTitle = “Problem”

      strPrompt = “Some files in ” & strFolderPath & ” were not recorded”

      MsgBox strPrompt, vbInformation + vbOKOnly, strTitle

      DoCmd.OpenQuery “qryMissingFiles”

   End If

  

ErrorHandlerExit:

   Exit Sub

 

ErrorHandler:

   MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description

   Resume ErrorHandlerExit

 

End Sub

 


References

The code in the sample database needs the following references (in addition to the default references):

Microsoft DAO 3.6 Object Library

Microsoft Scripting Runtime

If you import code or objects into a database of your own, you may need to set one or more of these references.  The version number may differ, depending on your Office version; check the version you have.  References are set in the References dialog, opened from the VBA window.  For more information on working with references, see Access Archon #107, Working with References.


Supporting Files

The zip file containing this article, in Word format, plus the supporting file(s), may be downloaded from the Access Archon page of my Web site.  It is accarch128.zip, which is the last entry in the table of Access Archon columns for Woody’s Access Watch.










Document Name

Document Type

Place in

File Names.mdb

Access 2000 database (can also be used in higher versions of Access)

Wherever you want

 

 

 

About this author