none
Saving attachments into a table RRS feed

  • Question

  • Hi all,

    I need to save attachments into my table. I have an Attachment field in the table.

    I could establish that one cannot use SQL to save the attachment to the table. My form where the attachment is to be added, is not bound to any tables. When the user click on the "Save" button, I use SQL statements to save all the data to several tables in the database.

    Is there any other way that I can use to save the attachment to the table with VBA?

    Thanks

    Deon

    Tuesday, August 15, 2017 6:00 AM

Answers

  • Hi Deon,

    -> I use SQL statements to save all the data to several tables in the database.

    I suggest you use DAO record set  to save attachments to table in database.

    Here is the example.

    Private Sub Command0_Click()

    Dim db As DAO.Database

    Dim rst As DAO.Recordset

    Dim attField As Recordset2

    Set db = CurrentDb

    Set rst = db.OpenRecordset("AttachmentTable") 'AttachmentTable is table name

    rst.MoveFirst  'add to first record

    rst.Edit

    Set attField = rst.Fields("Documents").Value 'Documents is the attachment field name

        attField.AddNew

       attField.Fields("FileData").LoadFromFile "C:\Users\Desktop\New Microsoft Excel Worksheet.xlsx" 'add attachment via its path

       attField.Update

    rst.Update

    rst.Close

    Debug.Print Err.Description

    End Sub

    You could refer to below link for more information.

    https://msdn.microsoft.com/VBA/Access-VBA/articles/work-with-attachments-in-dao

    Best Regards,

    Terry

    • Marked as answer by Deon SA Wednesday, August 16, 2017 5:20 AM
    Tuesday, August 15, 2017 9:58 AM