I created a small routine to create the table. I called it table2sarge
Code:
Sub CreateTable2Sarge()
10 On Error Resume Next
Dim sqlDrop As String
20 sqlDrop = "Drop Table Table2Sarge"
30 CurrentDb.Execute sqlDrop, dbFailOnError
Dim sqlCreate As String
40 sqlCreate = "CREATE TABLE Table2Sarge " & _
"(EntryID AUTOINCREMENT PRIMARY KEY,filepath varchar(100),ModDate Date );"
50 CurrentDb.Execute sqlCreate, dbFailOnError
End Sub
This gives the fullpath filename and the date last modified.
This is my Handlefile modification.
Code:
Sub HandleFile(dir As String, filename As String)
10 On Error GoTo ErrHandler
Dim fullPath As String
Dim modDate As Date
Dim qry As String
Dim testDate As Date
20 fullPath = dir & filename
' the date we want to compare the file against (year,month,Day)
30 testDate = TempVars!checkDate ' DateSerial(2021, 2, 14)
40 If Not FolderExists(fullPath) Then 'if this is a directory then skip
50 modDate = FileDateTime(fullPath)
60 If DateSerial(Year(modDate), Month(modDate), Day(modDate)) = testDate Then
70 Debug.Print fullPath & " " & testDate
'create a table and a query to insert this data into your table if needed
80 qry = "INSERT INTO table2Sarge (filepath,moddate) VALUES ('" & fullPath & "','" & testDate & "');"
'execute the insert
90 CurrentDb.Execute qry, dbFailOnError
100 End If
110 End If
ExitHandler:
120 Exit Sub
ErrHandler:
130 MsgBox Err.Description, , "Error #" & Err.Number & " " & Erl
140 Resume ExitHandler
End Sub
And this is the modified TESTVRS as mentioned earlier.
Code:
' ----------------------------------------------------------------
' Procedure Name: TESTVRS
' Purpose: From accessforums.net https://www.accessforums.net/showthread.php?t=84428
'Traverse a path and find files with modified date equal to some specified date
' Procedure Kind: Sub
' Procedure Access: Public
' Author: kd2017 ---Jack/based on accessForums.net thread
' Date: 21-Sep-21
' ----------------------------------------------------------------
Sub TESTVRS()
'
'adjusted to allow user to enter a startDate and range as needed
10 Dim StartDate As Integer: StartDate = 1
20 For i = StartDate To StartDate ' + 4 'check files modified 2021 June 1 thru 5
Dim checkDate As TempVar
30 TempVars!checkDate = DateSerial(2021, 3, i) 'assign the date to be checked
40 TraversePath "C:\Users\jack\Downloads\"
50 Debug.Print "Traversing Completed for " & TempVars!checkDate & vbCrLf
60 Next i
End Sub
This is my result (immediate window)
C:\Users\jack\Downloads\Database1_o365.acc_BKUP_20 21-03-01--10-40-18.accdb 01-Mar-21
Traversing Completed for 01-Mar-21
and this is the Table2Sarge after the run
EntryID |
filepath |
ModDate |
1 |
C:\Users\jack\Downloads\Database1_o365.acc_BKUP_20 21-03-01--10-40-18.accdb |
01-Mar-21 |