VBA code to attach file into table Access-CodePudding

I´m struggling trying to figure out how to insert / attach a file into a table field when clicking on a button in a form. I have been searching on the Internet and have tried many codes but until now, I have not got success.

I found this code below here on stackoverflow but this doesn´t work because apparently I need to define the variables db, rsfile, rsReport and filePath.

The table name is "GC_Eventos" and the field to store the file is "Contrato" where the Event_ID of the form is equal to the Event_ID register in the table

I´m defining db as DAO.Database but i don´t know if the others variables need to be defined as objects or variables or something else. Does someone know how to fix this code or a better way to insert a file into a table on Access using a button??

I would really appreciate your help

Private Sub Command879_Click()
Dim db As DAO.Database

rsfile = db.OpenRecordset("GC_Eventos")

Do While Not rsfile.EOF
 If rsfile.Fields("Evento_ID").Value = 1 Then
    'Activate edit mode.
    rsfile.Edit

    'Instantiate the child recordset.
    Set rsReport = rsfile.Fields("Contrato").Value
    'Add a new attachment.
    filePath = "C:\dbPDF\sitereport.pdf"
    rsReport.AddNew
    rsReport.Fields("FileData").LoadFromFile (filePath)
    rsReport.Update
    'Update the parent record
    rsfile.Update
 End If
'Next row
 rsfile.MoveNext
Loop


End Sub

CodePudding user response：

Only have to declare variables if module header has Option Explicit line which forces variable declaration. I recommend this be done by default when module is created. From the VBA editor > Tools > Options > Editor > check Require Variable Declaration. Will have to manually add to existing modules. Dim statements for the 3 variables lacking them:

Dim rsfile As DAO.Recordset
Dim rsReport As DAO.Recordset
Dim filepath As String

Regardless of variable declaration, object variables do need to be Set. You already have one example in your code - rsReport. Add Set to the rsfile = line and add a Set line for db variable.

Set db = CurrentDb
Set rsfile = db.OpenRecordset("GC_Eventos")