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 |