How to display the names of files that are not extracted as output in VBA Access software

Question

Sub ExportPDFs()
    Dim rs As DAO.Recordset
    Dim folder As String, path As String
    Dim adoStream As Object 'Late bound ADODB.Stream
    Dim failedFiles As New Collection 'list of failed files
    Dim failedFileNames As String 'A string to hold the names of failed files
    
    folder = "F:\rkp\archive  packing\tarhebasteh\"
    Set rs = CurrentDb.OpenRecordset("SELECT namemotor , FILE FROM tarhebasteh")
    
    failedFileNames = "" 'Definition and initialization
    
    Do Until rs.EOF
        path = folder & rs!namemotor & ".pdf"
        
        Set adoStream = CreateObject("ADODB.Stream")
        adoStream.Type = 1 'adTypeBinary
        adoStream.Open
        adoStream.Write rs("FILE").Value
        
        On Error Resume Next
        adoStream.SaveToFile path, adSaveCreateOverWrite
        If Err.Number <> 0 Then 'If an error occurs
            failedFiles.Add rs!namemotor ' Add filename to failure list
        End If
        On Error GoTo 0
        
        adoStream.Close
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
    
    'Show failed files
    If failedFiles.Count > 0 Then
        Dim failedFile As Variant
        For Each failedFile In failedFiles
            failedFileNames = failedFileNames & vbCrLf & failedFile
        Next failedFile
        MsgBox "The following files were not output:" & vbCrLf & vbCrLf & failedFileNames
    Else
        MsgBox "All files have been moved to the output."
    End If
End Sub

Gets an error from this line failedFileNames = failedFileNames & vbCrLf & failedFile

this erore is object invalid or no longer set

Can you help me make the code better? Or use another code to do this

---

The previous code worked well for extracting 500 files to PDF and displaying 300 files in the output. However, the code you provided above specifically extracted 480 PDF files and displayed them as output.

Sub ExportPDFs()
    Dim rs As DAO.Recordset
    Dim folder As String, path As String
    Dim adoStream As Object 'Late bound ADODB.Stream
    
    folder = "C:\Users\AliReza\Desktop\folder\"
    Set rs = CurrentDb.OpenRecordset("SELECT Name, Package FROM documents")
    Do Until rs.EOF
        path = folder & rs!Name & ".pdf"
        
        Set adoStream = CreateObject("ADODB.Stream")
        adoStream.Type = 1 'adTypeBinary
        adoStream.Open
        adoStream.Write rs("Package").Value
        adoStream.SaveToFile path, adSaveCreateOverWrite
        adoStream.Close
        
        rs.MoveNext
    Loop
    
    rs.Close
    Set rs = Nothing
End Sub

Answer 1

failedFiles.Add rs!namemotor

adds the Field object to the collection, so after you close the recordset that's no longer available.

failedFiles.Add rs!namemotor.Value

will add the field content instead, so you can use that after the recordset is closed.