Results 1 to 6 of 6
  1. #1
    newyorkyankee is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2012
    Posts
    11

    Question How do you bulk attach files in Access?

    Hello, consider this database: Motorcycles.accdb which has a table tblMotorcycles with 4 fields:

    MotorcycleID
    MotorcycleBrand
    MotorcycleModel
    MotorcycleImage

    The last field (MotorcycleImage) is an Attach type where images are attached.

    Let's suppose the IDs from 1 to 100 contain Harley Davidson models, from 101 to 200 contain Honda models and from 201 to 300 contain Yamaha models. How do I attach 100 images at once?

    Consider the images are located here:

    C:\Documents and Settings\User\Desktop\Images\HarleyDavidson\
    C:\Documents and Settings\User\Desktop\Images\Honda\
    C:\Documents and Settings\User\Desktop\Images\Yamaha\

    And they are numerically organized like this (in their respective folders):

    HarleyDavidson0001.jpg
    HarleyDavidson0002.jpg
    HarleyDavidson0003.jpg
    ...
    Honda0001.jpg
    Honda0002.jpg
    ...


    Yamaha0001.jpg
    Yamaha0002.jpg
    ...

    E.g.: From ID 101 to 200 attach all images from Honda0001.jpg to Honda0100.jpg, in the MotorcycleImage field, from their respective folder.

    In other words, how do I attach from a certain range a certain amount of files, always considering that the files attached are in sequence. And how do I remove images from a certain range (e.g.: remove all images from IDs 150 to 200)?

    Could you understand or you need more additional information?

    Thanks!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That is a lot of images in a database. What format and size are the images? You do know that Access has a 2gb overall size limit for the database?

    Review (skip to the end first then read the rest)
    http://office.microsoft.com/en-us/ac...001213852.aspx
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    newyorkyankee is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2012
    Posts
    11
    Hello June7, thank you for your comments!

    Actually I know that attaching all images to a database isn't the best idea and that MS-Access databases have a size limit of 2 GB. I'm doing it that way because it's the only way I found to (easily) show images in forms. Most images are .jpg, .jpe and .png and Access doesn't show those formats in forms, and I don't want to convert them to .bmp.

    It would be much easier if the table had a Text Field called ImageLocation and each record had the location of a different immage, e.g.:

    C:\Documents and Settings\User\Desktop\Images\HarleyDavidson\Harley Davidson0001.jpg
    C:\Documents and Settings\User\Desktop\Images\HarleyDavidson\Harley Davidson0002.jpg
    C:\Documents and Settings\User\Desktop\Images\HarleyDavidson\Harley Davidson0003.jpg
    ...

    This is very easy and convenient to do, and would be a much better way to do it so the database wouldn't grow dramatically. However if done that way, how can images be shown in a form and/or report?

    Is there an easier way than just inserting VBA code? Because this is the best answer I could find, including in Microsoft's website.

    Are there other topics here dealing with this problem?

    Thanks!

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Access 2007 added ControlSource property to the Image Control. This allows dynamic display of images (jpg, png, bmp, tif - not pdf) on forms and reports. The ControlSource can be attachment field or a text field with full path to the image outside the db. No code required.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    newyorkyankee is offline Novice
    Windows XP Access 2007
    Join Date
    Apr 2012
    Posts
    11
    I already solved it!

    Instead of attaching files individually (which is unecessary and eventually makes the database grow a lot) I did this:

    1 - Ran a .vbs script to list all files within a directory with its full path and saved in a text file
    2 - Created a new field called Path in the table and pasted all values there
    3 - Created an Image controller in the form
    4 - In the Image controller's Properties I went to Data and in Control Source I selected Path

    Pretty easy!

    It would be even better if I could change part of the path to a variable (e.g.: change C:\Documents and Settings\User\Desktop\Images\HarleyDavidson\ to $harleypath) and then just add the image name so it would become cleaner and lighter. But it's nice the way it is.

    Thanks!

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you don't want to store the full path in field, can concatenate the path string with the image name in the ControlSource property:

    ="drive:\path\" & [fieldname]
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 08-13-2011, 11:50 AM
  2. Replies: 1
    Last Post: 02-21-2011, 09:55 PM
  3. Can't attach to email
    By newtoAccess in forum Access
    Replies: 10
    Last Post: 12-03-2010, 02:10 PM
  4. Bulk Email / Loop through recordset
    By smikkelsen in forum Forms
    Replies: 4
    Last Post: 07-12-2010, 06:59 PM
  5. Replies: 0
    Last Post: 04-24-2009, 12:20 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums