指定したフォルダのファイルリストを作成（サブフォルダも含む）

Sub WriteFilesToTable(folderPath As String)
    Dim fso As FileSystemObject
    Dim folder As Folder
    Dim subFolder As Folder
    Dim file As file
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    
    Set fso = New FileSystemObject
    Set folder = fso.GetFolder(folderPath)
    Set db = CurrentDb
    Set rs = db.OpenRecordset("テーブル1", dbOpenDynaset)
    
    ' メインフォルダ内のファイルを処理
    For Each file In folder.Files
        With rs
            .AddNew
            !フルパスフィールド = file.Path
            !ファイル名フィールド = file.Name
            .Update
        End With
    Next file
    
    ' サブフォルダ内のファイルも処理
    For Each subFolder In folder.SubFolders
        Call WriteFilesInSubFolder(subFolder, rs)
    Next subFolder
    
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Set folder = Nothing
    Set fso = Nothing
End Sub

Sub WriteFilesInSubFolder(subFolder As Folder, ByRef rs As DAO.Recordset)
    Dim file As file
    
    For Each file In subFolder.Files
        With rs
            .AddNew
            !フルパスフィールド = file.Path
            !ファイル名フィールド = file.Name
            .Update
        End With
    Next file
    
    ' 再帰的にサブフォルダ内のファイルも処理
    Dim nextSubFolder As Folder
    For Each nextSubFolder In subFolder.SubFolders
        Call WriteFilesInSubFolder(nextSubFolder, rs)
    Next nextSubFolder
End Sub