Query_VBA to attach pdf in access database

[Rajpatelb]

hi friends,

i want to attach PDF from folder as per file name(Local reference number) in table name AOSI in access database. i have below code but its not working. i am receiving compile error. need help. thanks in advance.

>>> use code - tags <<<

Sub AttachPDFByFileNameAutomatically()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim filePath As String
    Dim attachmentField As DAO.Field2
    Dim newAttachment As DAO.Attachment
    Dim folderPath As String
    Dim fileName As String
   
    ' Replace "YourTableName" with the actual name of your table
    Const tableName As String = "AOSI"
   
    ' Replace "PDFFileName" with the name of the field containing the PDF file names
    Const fileNameField As String = "Local Reference Number"
   
    ' Replace "PDFAttachment" with the name of the attachment field
    Const attachmentFieldname As String = "Source Documents"
   
    ' Replace "C:\Path\To\Your\PDF\Files\" with the folder path where the PDF files are located
    folderPath = "\\LMN-data-69\PVG_AHM\ICSR\Source documents\2023\Jun-2023\01-Jun\"
   
    ' Open the database and table
    Set db = CurrentDb
    Set rs = db.OpenRecordset(tableName, dbOpenDynaset)
   
    ' Loop through the records
    Do Until rs.EOF
        ' Get the PDF file name from the field
        fileName = rs(fileNameField).Value
       
        ' Check if the file exists in the folder
        If Dir(folderPath & fileName) <> "" Then
            ' Get the attachment field and create a new attachment
            Set attachmentField = rs.Fields(attachmentFieldname)
            Set newAttachment = attachmentField.Value.AddNew
           
            ' Set the file name and load the PDF file
            newAttachment.fileName = folderPath & fileName
            newAttachment.LoadFromFile folderPath & fileName
           
            ' Save the attachment and the record
            newAttachment.Update
            rs.Update
        Else
            MsgBox "File not found: " & fileName, vbExclamation
        End If
       
        rs.MoveNext
    Loop
   
    ' Clean up
    rs.Close
    Set rs = Nothing
    Set db = Nothing
   
    MsgBox "PDFs attached successfully!", vbInformation
End Sub

 

[vletm]

Rajpatelb​

You're running above code with ... Excel.
Which line gives that error?

 

[Rajpatelb]

i running this code with access file...first line coming yellow Sub AttachPDFByFileNameAutomatically().

 

[vletm]

Rajpatelb​

This is an Excel-forum.
I can give only one idea:
Hide all rows with two ' -marks.
( '' because You can see Your own comments, which are marked with single ' )
After that, there are only the 1st and the last lines.
After that, remove those one-by-one from the beginning as many times as You'll get that error.
Of course, You have to take care Your loop and if-sentences