Import only PDF files from folder that are missing in a table

Claude Larocque 666 Reputation points
2022-11-24T21:14:38.917+00:00

Hi everyone,
My import function below works perfectly when no data are in the table, however, I want to import only the files that are not in the table already,
ex: In my table named "DocApprouvées", I have these 3 files that I have imported from the function below.
I call the function on a button... everything works fine..

But let say that some new files are created in the folder, I want to import only the new files, the reason is that I enter some information in the table specific to the DocFileName so I don't want to delete the first imported files, I just want to import the new file.

On my button I have this event: "Call ImportDirListing("C:\Temp")" and that works but I want in the ImportDirListing function to import only the files that were never imported

Thanks
Claude from Quebec, Canada

Public Function ImportDirListing(sPath As String, Optional sFilter As String)  
On Error GoTo Error_Handler  
    Dim db                    As DAO.Database  
    Dim sFile                 As String  
    Dim sSQL                  As String  
    Dim sSQL2                  As String  
    Set db = CurrentDb()  
    sPath = "C:\Katherine\Soumissions\1-Approuvées\"  
    sFilter = "*"  
    If Right(sPath, 1) <> "\" Then sPath = sPath & "\"  
    If sFilter = "" Then sFilter = "*"  
    sFile = Dir(sPath & "*." & sFilter)  
    Do While sFile <> ""  
        sSQL = "INSERT INTO [DocApprouvées] ([DocFileName]) VALUES('" & sFile & "')"  
        db.Execute sSQL, dbFailOnError  
        sFile = Dir  
    Loop  
Error_Handler_Exit:  
    On Error Resume Next  
    If Not db Is Nothing Then Set db = Nothing  
    Exit Function  
   
Error_Handler:  
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _  
           "Error Source: ImportDirListing" & vbCrLf & _  
           "Error Number: " & Err.Number & vbCrLf & _  
           "Error Description: " & Err.Description & _  
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _  
           , vbOKOnly + vbCritical, "An Error has Occured!"  
    Resume Error_Handler_Exit  
  
    Exit Function  
   
End Function  
0 comments No comments
{count} votes

Accepted answer
  1. Oskar Shon 866 Reputation points MVP
    2022-11-25T17:50:09.19+00:00

    That can be easy.
    VBA way
    Read your existing data from table to dictionary collection and then make loop for dictionary with condition *.pdf
    And use dit.exists commend to add or not file to your table.

    SQL way
    or you can use record sett to request using SQL (select) if file existing in table, If don't then do Insert :)
    sql-how-to-properly-check-if-a-record-exists

    Regards

    0 comments No comments

0 additional answers

Sort by: Most helpful