none
Adding attachments automatically RRS feed

  • Question

  • Hi all,

    I have an Access DB (a table) that has a column that's dedicated for attachments (photos, 1 photo per cell).

    On my PC, I have a sub-directory that has all the images. Their names represent their order (i.e. im001.jpg, im002.jpg and so on).

    The number of rows in the DB is the same as the number of image files in the sub-directory.

    I have hundreds of images to attach.

    My question is: how can I do it automatically, as opposed to one-by-one?

    Thanks!

    Tuesday, January 24, 2017 6:33 PM

Answers

  • Hi Nathan,

    You're welcome. In which case, you probably can't avoid using VBA. Good luck!

    • Marked as answer by k_knock Thursday, January 26, 2017 5:58 AM
    Tuesday, January 24, 2017 9:15 PM
  • Yes - I need to store the actual image files, not their paths...


    Do you have a particular reason for storing the images as attachments?  Storing the path and setting the ControlSource property of an Image control to the name of the column containing the path is an efficient way of displaying images in an Access database.

    You'll find examples of varying degrees of complexity in Images.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    The basic Images_Simple.accdb file would probably suit your purposes if you wish to associate only one image per row in a table


    Ken Sheridan, Stafford, England

    Tuesday, January 24, 2017 10:31 PM

All replies

  • Hi,

    You can loop through a recordset of your table and use the LoadFromFile method.

    Hope it helps...

    Tuesday, January 24, 2017 8:32 PM
  • Hey .theDBguy. Thanks for the prompt reply!

    I am just a beginner in Access and so I think what you suggested is probably waaaay over my league as it involves coding in VBA :)

    I was hoping that there was some easy-to-use option in Access or perhaps an add-on.

    Unless, of course, what you suggested is actually very easy...


    Nathan

    Tuesday, January 24, 2017 8:40 PM
  • Hi Nathan,

    There's no add-on I am aware. If you're talking about storing the images/files in the tables as an Attachment field, then the code I mentioned would be the only way to do it. But, if you were talking about merely storing the "file path" to the images/files in the table as Text field, then it gets easier.

    Tuesday, January 24, 2017 9:02 PM
  • Yes - I need to store the actual image files, not their paths...

    Thanks anyway!


    Nathan

    Tuesday, January 24, 2017 9:05 PM
  • Hi Nathan,

    You're welcome. In which case, you probably can't avoid using VBA. Good luck!

    • Marked as answer by k_knock Thursday, January 26, 2017 5:58 AM
    Tuesday, January 24, 2017 9:15 PM
  • Yes - I need to store the actual image files, not their paths...


    Do you have a particular reason for storing the images as attachments?  Storing the path and setting the ControlSource property of an Image control to the name of the column containing the path is an efficient way of displaying images in an Access database.

    You'll find examples of varying degrees of complexity in Images.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    The basic Images_Simple.accdb file would probably suit your purposes if you wish to associate only one image per row in a table


    Ken Sheridan, Stafford, England

    Tuesday, January 24, 2017 10:31 PM
  • Hi k_knock,

    I agree with the suggestion given by .theDBguy and Ken Sheridan.

    it is better to save the path of the image in table instead of saving the image in the table.

    if you save the path then it will consume a very less memory then image.

    then also if your requirement is to save the image in db then you can refer the suggestion given by .theDBguy.

    you need to use loop in VBA code till last record.

    then you need to loop through all the records in table and update it if records are already exists.

    otherwise you need to loop through all the images in the folder and insert a new record in table for each image.

    there is no any other way to perform this operation by user interface to automatically insert all the images in database.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 25, 2017 5:32 AM
    Moderator
  • Thanks Ken! I will give it a try.

    Nathan

    Wednesday, January 25, 2017 9:00 AM
  • Thanks Deepak! That's very helpful!

    Nathan

    Wednesday, January 25, 2017 9:01 AM
  • Hi k_knock,

    if you got the answer for your question then please try to mark the appropriate suggestion as an answer.

    so that we can try to close this thread.

    if you have any further question regarding this issue then let us know about that.

    we will try to suggest you further.

    Regards

    Deepak


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 26, 2017 12:34 AM
    Moderator