Results 1 to 12 of 12
  1. #1
    kiwichick is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    15

    Add embedded image to record using a button

    Hi there, I'm sorry if this has been answered elsewhere. I have a form with a blank image control, and a button that uses Application.FileDialog to select an image for each record. This is the code I'm using for the button:



    Code:
        Private Sub btnAddImage_Click()
          Dim strPath As String
          On Error GoTo ErrHandler
          With Application.FileDialog(1)
            .Filters.Clear
            .Filters.Add "Image Files", "*.bmp;*.gif;*.jpg;*.png"
            .InitialFileName = "C:\Users\Deanna\Documents\Plant Database\Images"
            If .Show Then
              Me.ImagePath = .SelectedItems(1)
              SysCmd acSysCmdSetStatus, "Image: '" & Me.ImagePath & "'."
              Me.imgImage.Requery
            End If
          End With
          Exit Sub
    
        ErrHandler:
          MsgBox Err.Description, vbExclamation
        End Sub
    However, the images are linked and I would like them to be embedded instead. What changes do I need to make? I tried changing the image control property from linked to embedded but that didn't work so I guess there's more to it than that. Thanks :-)

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,703
    Many readers will tell you that embedding pictures in the database will bloat your database. Using a link to images stored on the filesystem is considered a better practice.
    Are you having an issue with your currents set up?
    Is there a technical issue?

  3. #3
    kiwichick is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    15
    Quote Originally Posted by orange View Post
    Many readers will tell you that embedding pictures in the database will bloat your database. Using a link to images stored on the filesystem is considered a better practice.
    Are you having an issue with your currents set up?
    Is there a technical issue?
    Thanks orange, yes, I've seen readers say that embedding the images will increase the size of the database. That seems obvious - images have a size and consequently will affect the size of the database. My question is this: is the size of the database with embedded images > than the total size of the database with linked images + the linked images stored on my system? Otherwise, why does it matter? Are there functional issues with using a large database? eg: running slower.

    And, no, I wouldn't say I have any issue with the current setup other than what I thought would be the reason most people would want to embed images - not having to worry about moving the database to another system, or moving/renaming the photos.

  4. #4
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    14,703
    Access has a 2GB size limit. If you embed a number of images, you'll increase the likelihood of failing on size.
    The images stored in files on filesystem do not affect the Access 2GB limit. You could put the images in a consistent/standard folder accessible to all users.
    I don't have personal experience with large volume of images, but I have not heard of any serious performance issues when using file system images.

  5. #5
    Ajax is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    7,961
    storing a file name takes say 20 characters which is 42 bytes - will be more if you are storing the filepath as well. What's a typical image size? a small one might be 60kb (60000 bytes) You can see the difference in size is considerable. But there are 1m Kb in a Gb so you can still store quite a lot.

    Other benefit of storing the filename is that you can sort and filter on it which may or may not be useful to you.

  6. #6
    kiwichick is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    15
    Quote Originally Posted by orange View Post
    Access has a 2GB size limit. If you embed a number of images, you'll increase the likelihood of failing on size.
    The images stored in files on filesystem do not affect the Access 2GB limit. You could put the images in a consistent/standard folder accessible to all users.
    I don't have personal experience with large volume of images, but I have not heard of any serious performance issues when using file system images.
    Thanks again orange, I didn't know about the database size limit so that certainly changes my mind about embedding the images. Which brings me to another couple of questions, if you would be so kind :-D

    1. If I move the database and the image folder to another system, obviously the new filepaths could be an issue. If I put the database file and the image folder in the same parent folder, can Me.ImagePath be stored as a relative location? And, if so, what code do I need to do that?

    2. If I use a folder to store the images all in one place, the image has to be moved to the image folder before it can be used. So, can I use Application.FileDialog to select a file, what code do I need to move the file to the image folder, preferably renaming it ([field1] - [field2].jpg) and store the new path?

  7. #7
    kiwichick is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    15
    Quote Originally Posted by Ajax View Post
    storing a file name takes say 20 characters which is 42 bytes - will be more if you are storing the filepath as well. What's a typical image size? a small one might be 60kb (60000 bytes) You can see the difference in size is considerable. But there are 1m Kb in a Gb so you can still store quite a lot.

    Other benefit of storing the filename is that you can sort and filter on it which may or may not be useful to you.
    Thanks Ajax, the sort and filter didn't occur to me. Definitely may be useful. I've decided to stick with linked images :-)

  8. #8
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,844
    1) Application.CurrentProject.Path will return the folder that the db is in. If you standardize the image folder (e.g. C:\Databases\YourApplication\Images) and the db is in the same root (e.g. C:\Databases\YourApplication) then about all you'd need to do is concatenate the left part & \ & the standard path to the images folder.

    2) if this is a one time thing, just use File Explorer and be done with it. Going forward, use the file dialog object as you suggest, but start where the path is equal to the concatenation you will already have or can build when the fd is invoked.
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  9. #9
    kiwichick is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    15
    Quote Originally Posted by Micron View Post
    1) Application.CurrentProject.Path will return the folder that the db is in. If you standardize the image folder (e.g. C:\Databases\YourApplication\Images) and the db is in the same root (e.g. C:\Databases\YourApplication) then about all you'd need to do is concatenate the left part & \ & the standard path to the images folder.
    Thanks Micron, Yes I see what you're saying. I've done a bit of coding but I'm not very familiar with VBA, and MS Access in particular. Do I use CurrentProject.Path with code and, if so, what code and where do I put it? Currently, the path is determined by the "Add Image" button on my form. Clicking the button allows the use to select an image file. (As you can see, I'm using CurrentProject.Path for the InitialFileName) The path is stored in the ImagePath field of my table. The path is then used to populate the image control. But that stores an absolute path. This is the code for the button:

    Code:
    Private Sub btnAddImage_Click()
      Dim strPath As String
      On Error GoTo ErrHandler
      With Application.FileDialog(1)
        .Filters.Clear
        .Filters.Add "Image Files", "*.bmp;*.gif;*.jpg;*.png"
        .InitialFileName = CurrentProject.Path & "\Images"
        If .Show Then
          Me.ImagePath = .SelectedItems(1)
          Me.imgImage.Requery
        End If
      End With
      Exit Sub
    
    ErrHandler:
      MsgBox Err.Description, vbExclamation
    End Sub
    Do I make changes here or will it need to be somewhere else eg: Form / On Load?
    Last edited by kiwichick; 02-16-2020 at 04:55 PM. Reason: additional information

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,844
    You code looks fine (except for one thing) if that's the event you want to use and I don't see why not. Don't do it on a form open or load event.
    I would have thought you needed Application.CurrentProject.Path but I guess it works without Application.

    However, the 'one thing' is that you don't requery the image control if that's what you have there, you set its picture property and the picture type property should be 'linked' not 'embedded'. So I think you will find you need

    Code:
          Me.ImagePath = .SelectedItems(1)
          Me.imgImage.Picture = .SelectedItems(1)
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    "Everyone has a photographic memory; some just don't have film." Steven Wright

  11. #11
    kiwichick is offline Novice
    Windows 10 Access 2016
    Join Date
    Feb 2020
    Posts
    15
    Quote Originally Posted by Micron View Post
    You code looks fine (except for one thing) if that's the event you want to use and I don't see why not. Don't do it on a form open or load event.
    I would have thought you needed Application.CurrentProject.Path but I guess it works without Application.

    However, the 'one thing' is that you don't requery the image control if that's what you have there, you set its picture property and the picture type property should be 'linked' not 'embedded'. So I think you will find you need

    Code:
          Me.ImagePath = .SelectedItems(1)
          Me.imgImage.Picture = .SelectedItems(1)
    Thanks again Micron, I'm not even sure what requery means :-D I've just altered some code I found online.

    However, my code only uses a "relative path" (CurrentProject.Path) for the opening location to select an image. It doesn't store the image path as relative - which means I can't move the database once an image has been selected.

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    6,844
    Google is your friend. Try ms access requery (or ms access requery vs refresh).
    It doesn't store the image path as relative
    Then you need to alter your code if that's what you want, and if you move the db, you move/copy the whole folder containing the db and the images subfolder, or at least the db file and the images folder so that they both end up in the same new place.

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

Similar Threads

  1. Replies: 4
    Last Post: 05-02-2016, 04:33 AM
  2. Button for Each Record in Table embedded within Form
    By BrainExplodingFromCoffee in forum Forms
    Replies: 3
    Last Post: 04-05-2016, 01:22 PM
  3. Replies: 3
    Last Post: 11-25-2014, 11:13 AM
  4. VBA for Embedded Image in Form 'Send To Back'
    By Kirsti in forum Programming
    Replies: 6
    Last Post: 06-11-2014, 09:21 PM
  5. Replies: 2
    Last Post: 07-10-2013, 11:19 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
  •  
Tech Forums: Microsoft Office Forums