automatically update an Access table by a files listing

[bip54]

Hello,
I looked everywhere on the net but I can't to find an answer, so I come to solicit your help .
I have a directory "D: \ Books", all books are in pdf (book1.pdf, book2.pdf ... book500.pdf).
I have a very very simple database for these books (I will try to complexify this database later (with several tables, relations, etc ...)) but for now I prefer to take things in order which suits my neuron; It is in Access 2016 and contains a table "tBooks" with just 2 fields: "Num_Book (Key) and" Book_name ", the names of the records correspond perfectly to the names of the files (book1.pdf, book2.pdf ... book500 .pdf); There is a form "fBooks" based on the table "tBooks"; If I add or remove books in the "D: \ Books" directory how can I automatically update my "tLivres" table just by clicking on an "Update" command button in the associated "fBooks" form ; I just want to update the table: if there is no new book added to the directory then no new record is created in the table; If the added book does not exist in the table, then a new record is added to the table, if a book is removed from the directory then the corresponding record is removed from the table.
Thanks in advance, I have been rowing for weeks ...

[PhilOfWalton]

I'm not going to give you the full answer, but this is the tricky bit. With a bit of modification, that will create your table.

Remember to have BookName as No Duplicates

If you get stuck, come back

Public Function ListFiles(strPath As String, Optional strFileSpec As String, _
    Optional bIncludeSubfolders As Boolean)
    'Debug.Print ListFiles("G:\SBEAM", "*.csv", True)
    '? ListFiles("E:\Phil Data\Access\Mdb 2010", "*.PDF", True)
 
    On Error GoTo Err_Handler
    'Purpose:   List the files in the path.
    'Arguments: strPath = the path to search.
    '           strFileSpec = "*.*" unless you specify differently.
    '           bIncludeSubfolders: If True, returns results from subdirectories of strPath as well.
    '           lst: if you pass in a list box, items are added to it. If not, files are listed to immediate window.
    '               The list box must have its Row Source Type property set to Value List.
    'Method:    FilDir() adds items to a collection, calling itself recursively for subfolders.
    Dim colDirList As New Collection
    Dim VarItem As Variant
 
    Call FillDir(colDirList, strPath, strFileSpec, bIncludeSubfolders)
 
    'Add the files to a list box if one was passed in. Otherwise list to the Immediate Window.
 
    For Each VarItem In colDirList
        Debug.Print VarItem & " " & GetFileDate(CStr(VarItem))
    Next
 
Exit_Handler:
    Exit Function
 
Err_Handler:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Exit_Handler
 
End Function
 

And the FillDir Routine is

Private Function FillDir(colDirList As Collection, ByVal strFolder As String, strFileSpec As String, _
    bIncludeSubfolders As Boolean)
    'Debug.Print ListFiles("H:\SBEAM", "*.Csv", True)
    'Build up a list of files, and then add add to this list, any additional folders
 
    Dim strTemp As String
    Dim colFolders As New Collection
    Dim vFolderName As Variant
 
    'Add the files to the folder.
    strFolder = TrailingSlash(strFolder)
    strTemp = Dir(strFolder & strFileSpec)
    Do While strTemp <> vbNullString
        colDirList.Add strFolder & strTemp
        strTemp = Dir
    Loop
 
    If bIncludeSubfolders Then
        'Build collection of additional subfolders.
        strTemp = Dir(strFolder, vbDirectory)
        Do While strTemp <> vbNullString
            If (strTemp <> ".") And (strTemp <> "..") Then
                If (GetAttr(strFolder & strTemp) And vbDirectory) <> 0& Then
                    colFolders.Add strTemp
                End If
            End If
            strTemp = Dir
        Loop
        'Call function recursively for each subfolder.
        For Each vFolderName In colFolders
            Call FillDir(colDirList, strFolder & TrailingSlash(vFolderName), strFileSpec, True)
        Next vFolderName
    End If
 
End Function
 

Phil

[bip54]

Many thanks for your very quick response .

I try today or tomorrow (because job...) and I send an S.O.S. if I'm flooded .