none
Strategy for storing images/links to images in an Access database. RRS feed

  • Question

  • First things first, yes I know SQL-Express would be better but the application has to be able to run on some seriously old and rubbish hardware.

    I have a database that currently stores nothing but text strings, dates & numbers.
    with a vb.net front end to display data i.e.. datagridviews, textboxes etc.

    What I'd like to do is give it the ability to display images. I think the best way to do this is to keep all the images in a separate folder and just store links to the image in the database.

    I'm also thinking I'd like to display any images related to a record in a sort of picture box control that will just show thumbnails so that the user can just double click one of the thumbnails to open the image to full size.

    I'm guessing it may take a while for the computer to generate these thumbnails when the user clicks on a particular record in the datagridview (especially on a poorly specked computer with only a gig or two of memory) . So what I'm considering is instead of just storing the link, I could also store a thumbnail of the image. This thumbnail could be loaded into the picture box instead of the thumbnail having to be generated. When the user clicks the thumbnail it would use the link to open the full image in whatever default image viewer window is using.

    So I'm thinking if the user wanted to store an image they would have to:

    browse to the image
    Click ok
    This application would then copy the image to the applications data folder and give it a unique name.
    Create the link and thumbnail and then store these in the db.

    I don't know how the mechanics of this would work at the moment but is it a reasonable strategy?
    Gone down too many dead ends before

    The application is very fast at the moment and can query the db as fast as it is possible to type.
    I don't want to throw this away if at all possible.

    The Access db is the newer .accdb type

    Friday, May 18, 2018 5:27 PM

Answers

  • I would say this is a good idea in regards to storing images in a folder, one full size, one thumbnail size. 

    The image base name can be done using a GUID which is stored in a database field. Thumbnail size image would have a post fix identifier e.g. 

    98fadf43-6116-48c5-bb51-d4c572b18129_tb.jpg

    98fadf43-6116-48c5-bb51-d4c572b18129.jpg

    Use Guid.NetGuid().ToString()


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, May 18, 2018 7:20 PM
    Moderator
  • I agree with Karen that this sounds reasonable.

    The only thing I would want to clarify is this part:

    "This application would then copy the image to the applications data folder and give it a unique name.
    Create the link and thumbnail and then store these in the db."

    When you say "the application's data folder", wouldn't that be local to each user running the application?  I would think you would want a networked share to store the raw image - something every user's PC will have access to without special permissions.

    When you say "store these in the db", the link is obvious as it is just text, but what about the thumbnail?  Are you thinking of storing this as binary data?  You might keep the thumbnails in the same shared network folder as the raw images (perhaps within a subfolder).  If the thumbs are really small then the binary data probably won't affect the access database in a noticeable way, but keeping the thumbs in a shared folder would allow you to potentially return your db results and then use a secondary process to load the images, thereby having zero impact on the actual database transmissions.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    • Marked as answer by AndyNakamura Friday, May 18, 2018 8:45 PM
    Friday, May 18, 2018 7:32 PM
    Moderator

All replies

  • I would say this is a good idea in regards to storing images in a folder, one full size, one thumbnail size. 

    The image base name can be done using a GUID which is stored in a database field. Thumbnail size image would have a post fix identifier e.g. 

    98fadf43-6116-48c5-bb51-d4c572b18129_tb.jpg

    98fadf43-6116-48c5-bb51-d4c572b18129.jpg

    Use Guid.NetGuid().ToString()


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, May 18, 2018 7:20 PM
    Moderator
  • Andy,

    What kind of answer you want?

    1. No it is a dumb way of doing this with access as this is the standard way of SQL server;
    2. Yes it is super, there can not be anything better. 



    Success
    Cor


    Friday, May 18, 2018 7:21 PM
  • I agree with Karen that this sounds reasonable.

    The only thing I would want to clarify is this part:

    "This application would then copy the image to the applications data folder and give it a unique name.
    Create the link and thumbnail and then store these in the db."

    When you say "the application's data folder", wouldn't that be local to each user running the application?  I would think you would want a networked share to store the raw image - something every user's PC will have access to without special permissions.

    When you say "store these in the db", the link is obvious as it is just text, but what about the thumbnail?  Are you thinking of storing this as binary data?  You might keep the thumbnails in the same shared network folder as the raw images (perhaps within a subfolder).  If the thumbs are really small then the binary data probably won't affect the access database in a noticeable way, but keeping the thumbs in a shared folder would allow you to potentially return your db results and then use a secondary process to load the images, thereby having zero impact on the actual database transmissions.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    • Marked as answer by AndyNakamura Friday, May 18, 2018 8:45 PM
    Friday, May 18, 2018 7:32 PM
    Moderator
  • Hi, thanks for the replies.

    The folder is in a network share but I hardly dare mention it in case Cor went into an apopleptic rage. (which he did anyway;-) 

    I was actually thinking of storing the thumbnail in the database, I didn't think of storing it alongside the main image in the folder. I thought it might be faster to store in the db but I can see what your mean about loading it as a secondary process. To test image size I took a full size image and shrunk it to 120px x120px with paint.net and it came out at around 11kb


    Friday, May 18, 2018 8:41 PM
  • Glad we could be of assistance.  And thanks for the giggle after a long day. :)

    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Friday, May 18, 2018 10:43 PM
    Moderator
  • Andy,

    Thank you to show exactly what I meant. 

    It shows up that you had marked my reply as answer, and got points if I had written. 

    "Yes it is super, there can not be anything better." 

    I did not, because gathering points is not my main reason to help here. (Oh yea we are currently in the MVP awarding time)

    You wrote that it should run on seriously old and rubbish hardware. Then don't use supported .Net program tools. They don't run on that. 

    However, you can now tell that some Microsoft MVP's (beside a foolish one) have told that your old known Access way was a better approach then any other current Microsoft solution. 



    Success
    Cor


    Saturday, May 19, 2018 7:40 AM
  • Cor, I don't think for one fleeting fraction of a second that you are foolish, as you well know.
    Without you, Karen, Reed and everyone else who contributes to these forum I would still be working with spreadsheets!

    By old and rubbish hardware I mean Celeron with 1Gb ram running XP service pack 3
    I doubt very much that I can run Server express on that, so I'm pretty much stumped on that front.

    I applaud that Microsoft see fit to enable their newest technology to run on the oldest technology and that there are solutions available for every situation.

    I marked Karen and Reed's replies as answers because they were useful, insightful and pointed out the downside of my original plan. In fact they answered exactly what I asked.

    and no, I am not going to go back to VB6;-) 

    Roger and out,

    Andy

    Saturday, May 19, 2018 10:41 AM
  • I would agree with the others. Microsoft Access supports the storage of file data using the OLE Object or Attachment fields, but they contribute to the overall size of the database and Access has a 2GB limitation.

    If your database is password protected then there would "some" level of security if you were restricting access to the data and it would make everything a bit more portable as well, one database - one file, but if you anticipate the data in the database to grow significantly then storing the files in the database would not be a good option.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, May 21, 2018 1:06 PM
  • @Paul,


    I really don't understand what you mean. It looks like a kind of political statement. You know that the portability sucks by this solution. You also know that it is weak, because if an image is removed (what can easily be done and is almost impossible to protect) then there will become problems. 

    Access is a database file solution, it is not a complete server (service). Therefore this Access solution has much security problems which comes with the easily reachable files. 

    It is a solution. Likewise this is a solution. 

    For me you three MVP give in this thread the perception that it is an always good solution. It can be done, like I and probably you have also done for at home. But that is for me something different than using it in a business environment. I see nowhere this warning in this thread.

    I sometimes wish Bill is still around.

    :-)



    Success
    Cor

    Monday, May 21, 2018 1:57 PM
  • @Cor,

    Please don't pick a fight with other contributors.  That may not be your intent, but it is a likely outcome of posts like this.

    Note that Andy opened his thread with the statement:

    "First things first, yes I know SQL-Express would be better but the application has to be able to run on some seriously old and rubbish hardware."

    This tells us that he is well aware that Access is not an ideal solution, but he is stuck with it.  In this case we are indeed offering Band-Aid solutions.  But when you can't afford a surgeon, a Band-Aid is better than bleeding to death.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"


    Monday, May 21, 2018 2:26 PM
    Moderator
  • Cor,

    Perhaps something more like: "Another way is something like this. Then you avoid problems caused by doing that...?"

    Monday, May 21, 2018 2:39 PM
  • Cor,

    Pretty much what Reed said.

    I wasn't offering an opinion about whether Access is the best solution, but in this instance it *is* the solution. I was simply stating the pros and cons of storing the files in an Access database.


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Monday, May 21, 2018 2:39 PM
  • @Cor,

    Please don't pick a fight with other contributors.  That may not be your intent, but it is a likely outcome of posts like this.

    Note that Andy opened his thread with the statement:

    "First things first, yes I know SQL-Express would be better but the application has to be able to run on some seriously old and rubbish hardware."



    At the moment the answer "reasonable solution" was given we did not even know what was meant with "rubbish old hardware" because SQL Server Express can run on the same hardware as ms Access but nobody told that SQL server is older than Accdb". My problem is the timing in this thread of proposing and marking answers.

    I think you know me well enough too know that I had written that a Band-Aid solution is better than no solution, but erased it again, it are only words. 


     


    Success
    Cor

    Monday, May 21, 2018 3:50 PM