Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2016

    Unanswered: How to store the attachments externally from the DB

    Hi guys,

    I'm new to Access and to DB in general. I'm trying to build a simple DB, which main purpose is to store and display the PNG and JPEG files for each record.

    I built the below form. My issue is how to store the attachments. I've read somewhere that the db's max size is 2 GB. That's why I need the attachments to be stored externally form the DB, but still to be shown in the form. I also need to open them easily from the form in their full size. Each record has minimum 2 attachments.

    Click image for larger version. 

Name:	DB Form.jpg 
Views:	11 
Size:	281.0 KB 
ID:	16713

    If needed I can send the db file.

    Thank you in advance!

  2. #2
    Join Date
    Feb 2004
    New Zealand
    Provided Answers: 11
    If i was doing it I would just store the The File name in the database
    storing the Images will blot the database

    in the Event On Current

    point the Image Object to the Feild hold the FileName

    by the way the Screen look nice
    hope this help

    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    there's several issues to address
    1) as Myle say store the filename of the image, and assign it/them to an image control/s in the forms on current event, or a command button to load pictures on request. it helps if you have an image not found image so that that you can set an error handler and dispay that if there is no image

    2) if these are all your images, under your control then you could just store the filename and optionally the immediate preceding sub directory, and set the main location as a paraeter / configuration inside your application eg break up the lcoation to be
    filename \AUSTRIAN\20_SCH_FACE.jpg
    that way if you move the images to another computer, providing you recreate the BANKNOTES directory and all subsequent files/sub directories you merely change the configuration/parameter setting ONCE. However if you do not control the image or ther ikmages are scattered across one or more drives then you need to store the file name in its fully qualified form eg:-
    \\MYCOMPUTER\PATH\TO\MY\IMAGES\BANKNOTES\AUSTRIAN\ 20_SCH_FACE.jpg The downside of the latter approach is that moving the image library location or changing computers can cause problems and the images need updating to the new location using a query, which depnding on your skills could be fiddly to easily complete. changing it via a software setting is easy and can be tested prior to copleting the change. it also allows the setting to be customised. granted this looks like homework, but say you had two people, one was only allowed to look at banknotes, one only allowed to look at coins, so ones parameter is
    the others is
    ..and with a bit of code in the background (to set the banknotes or coins sub directory) the admin would be

    3) if its a multi user environment and you have concerns about people changing the image then you need to be a bit smarter on how you handle the file location. the file location needs to be on a server share, ideally all users have read access, selected users have write access. you don't want people overwriting, deleting or otherwise manipulating images outside the application. What I have used in the past is a two stage approach, users can upload (WRITE) images into a temporary directory and then that temporary directory is transferred to the main area periodically (at what ever time interval you require (I think I used an overnight process to do that)) you need to develop a manual process to approve changes to images (IE can an image overwrite an existing image)

    4) bear in mind that screen resolution is around one third of printed resolution so if you images are for display on screen the around 70dpi is plenty, if it may be used on paper then resize the image control to around one third the size of the nominal dimensions. OR store two scans for each image one hires for printing, one lowres for screen. this si something to bear in mind if this library will require iamges pumped up and down a network connection (including an internet connection). you cna alwasy provide for the hiores image to be displayed on request. that coudl be down to your file structure. ether has a chaneg in file name
    or change the path structure
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2016
    Thank you for your replies!

    To healdem. Regarding:
    2) All images are under my control.
    3) Single user environment.

    I'm not a developer and it's a little bit hard to get in details what you suggest to be done. However, I will try.

    First question, when I add an Image control there is no "On Current" in "Event"? Only "On Click", "On Dbl Click", "On Mouse Down/Up/Move". I use Access 2016.

    Let me summarize and please correct me if I didn't get it right.
    I will make a table containing the file paths. How should it looks? First column "id", second column "file path to the first image for the record", third column "file path to the second image for the record" and so on?
    Then create an "Image control", but I don't see an "On Current" in "Event".

  5. #5
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    form on current event fires when the current row changes

    ultimately its up to you and what you are truing to achieve
    is this homework for an assignement
    is this something you are doing for entertainment
    is this something you think may have a use elsewhere, either by friends or as a product
    there is no one 'right' answer, because part of that mix is whats right for you.

    bear in mind that in any software development the real cost is only identified when you come back to rework something.

    so if its not
    ..multi user
    ..going to be used by a third party
    then that simplifies thingfs enormously.

    I would still use a parameter / configuration settign to defien the server/drive path
    I would still use a column in the db to store the fielname, but thats me and reflects my design choices. it may well make sense for you to store the whole path or relative path to your images... paly with the settigns and see what works for you

    you don't need a tabel for file paths, but you may well need a table for images
    what I could see in your environment is say a table for items
    A table for resource types (including say pictures, noters, examples etc....
    a table for items_resources (eg photos of an item)...
    this way round you could have multiple images of the same item
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts