Images Subfolder
By Richard Rost
2 years ago
Store Images Relative to DB to Prevent Broken Links
In this Microsoft Access tutorial, you will learn how to store image file paths relative to the current database folder, ensuring your database reflects the new folder locations after a system upgrade or change.
Wesley from Plantation, Florida (a Gold Member) asks: I'm following the example that you showed in your "Images" video on how to store the full path and filename of an image in the table, instead of storing the picture itself as an OLE object or an attachment, and it works great. I love it. However, my company just upgraded computers, and the folder the database is in has moved. Now, I've got all these image locations that are pointing to the wrong place. How can I fix this?
Members
Members will learn how to take their existing full path file names and bulk upgrade them to a relative path using an update query.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
Prerequisites
Keywords
TechHelp Access, relative image paths, database subfolder, broken links fix, store file path Access, CurrentProject.Path, Access image control, concatenate file paths, dynamic image links Access, Access update query, manage external files Access
Intro In this video, I will show you how to store image file names and paths in a Microsoft Access database by using a subfolder under your database folder, making it easy to keep image links working even if the database folder is moved. We will review the recommended setup for your customer table and form, discuss how to display images dynamically using a relative path with CurrentProject.Path, and explain why storing actual image files inside your database is not a good idea. This approach helps you avoid broken links and keeps your database lighter and more manageable.Transcript In today's video, I am going to show you how to store images in your database—not the actual images, but the file name, the path, and file name. We are going to put it in a subfolder under your database folder. In case that database folder moves in the future, you will not have to deal with broken links. Let's talk about it.
Today's question comes from Wesley in Plantation, Florida, one of my Gold members. Wesley says, I am following the example that you showed in your images video and how to store the full path and file name of an image in the table instead of storing the picture itself as an OLE object or an attachment. And it works great. I love it. However, my company just upgraded computers and the folder that database is in has moved. Now I have all these image locations that are pointing to the wrong place. How can I fix this?
Well, Wesley, we will talk about fixing your specific situation a little bit later in the video. But for now, let me explain to everyone else what we have done and how to do it better.
The video that Wesley is talking about is this one. It is my images video. It is one of my more popular videos. In a nutshell, I show you how you can store image data in your database. You store the full path and the file name in a text field. You do not want to store the image itself in the database, like in an attachment field or an OLE object field. These things were okay in the past, but they really make your database bloated and big. Microsoft Access databases were not designed to store binary data like this, any kind of file attachments. It is bad. Watch this video for a more in-depth description of why you do not want to do this stuff.
So what we do is we add a Profile Picture field in our Customer table. Then we stick the Profile Picture field on our form so we can type in where the image is—the path and the file name. Then we put an image object on the form. When you type in the image's full name like that with the file name, the image appears in the form. This is great if your images are always stored in C:\\Users\\Richard\\Desktop\\Images. That is the problem Wesley is having, as they recently upgraded and that folder location moved because he is storing it on a server. The database folder is still up on the server, but it is in a different path.
The way we can resolve this is by simply making this a folder that is relative to the database folder, so if the database folder moves, then the images folder is going to move with it.
In addition to this images video, which you should go watch first before watching this video, I want you to also go watch this video on concatenation. It is putting two strings together. These are both free videos. They are on my website. They are on my YouTube channel. Go watch those and come on back.
Here I am on my desktop, but this could be anywhere. It could be your database folder on a server or on a different machine. I have got a DB folder. That is where my database is going to be stored. Under here, I have got an images folder with some different images in it. These do not matter right now. Let's go back to the database folder.
What we are going to do is we are going to put the pictures in my database, but we are going to just tell it that the images folder is always under the current database folder. How do we do that? Well, let's set it up real quick. Let's repeat what we did in the images video. It will be a good review for you. Customer table, Design View, come down to the bottom. I am going to add Profile Picture. Do not use "picture" or "image" or any of those simple reserved words. Those are reserved words. Be careful. This will be Short Text. That is fine. Save it. Close it.
Let's go into the Customer form. Right click, Design. Just for the purposes of class, we are going to get rid of this stuff we do not need. Let's get rid of all of this stuff and bring in that field. So, Form Design, Add Existing Fields, Profile Picture is right down here. Let's drop it there. Close that. I am going to slide this over here. Profile Picture right there. Make it fit. It does not need to be as big as before because we are not going to have to put a full path in here.
Let's drop the image here. Do not use this one. Come in here and use this one. Then click and drag where you want it. It is going to ask you to browse to where you want it. Just hit Cancel.
Let's give this thing a name. Let's call it Profile Picture Object or whatever you want to call it. It does not matter.
The important thing is setting its Control Source. The Control Source is going to be this Profile Picture, but we are only going to have the file name in here. The folder is going to be something we are going to specify right in the Control Source itself in here.
Here's what we are going to put. I am going to click here, hit Shift+F2, and zoom in. Normally, if you were going to have your images folder in the database folder on your C drive, you could put in here = "C:\\Database\\Images\\" & [Profile Picture]. If this is a physical location, you could use this, for example, if your images folder is not necessarily in the database folder, you could put the path to your database images folder, maybe "Z:\\..." or whatever it is.
If you want to use the images folder that is under your database folder, we are going to replace this with CurrentProject.Path and then an ampersand. CurrentProject.Path is basically the path—the folder—that your database is in. We are going to take that, add "\\Images\\", and then put the Profile Picture field at the end of that. Now, in the Profile Picture field, all we have to store is just the file name.
Of course, this assumes that all of your images are in the same folder. If you want to put some folders under this, you could just have a slash and whatever.
I will hit OK.
One more change I like to make when I am working with this. I like to go here, go to the form properties, and under Other, I am going to make the Tab Cycle "Current Record." That way, when I hit tab, it does not go to the next one, it goes back to the first field of this record. It just stays on here. That is one of my preferences.
Save it. Close it.
Let me open these up side by side so I can see what some of these file names are.
Let's go in here, and for this one, I will put in JJJ.gif and hit tab. There it is. The full path to that is determined in the Control Source for this control.
Let's go to someone else. Here is James Kirk. That is test.jpg. Tab. There he is.
Let's go back to me for a minute. There we are. These are animated GIFs. By the way, note to the Access team: Sammy, add this to the list—it would be nice if Access could display animated GIFs in forms, because he is supposed to be laughing here.
Let's do another one. Let's do Captain Picard. I have Picard.gif. There they are. I love this one. If you are not familiar with these, this is Picard. I love these memes. Here is the JJJ one. I put this one on someone's post on Facebook when they see something ridiculous. Of course, it is Kip from Napoleon Dynamite. This one is not animated, but oh well.
So you get the point.
The point here is if for some reason this folder moves, it does not matter. As long as this stays under the database folder, you are good. Or if you want to specify your folder instead of using CurrentProject.Path, you could put in here "Z:\\..." or whatever. Then if your folder does move, all you have to do is change it in here. You do not have to go through changing all these fields.
One more thing. If you want a button that you can click on right here to browse for that file—so this pops up to this folder—then you can click this and hit OK, and that puts that picture in there. I do cover that. That is in the Extended Cut video for the original images video. Go check that out. You click a browse button, the dialog box pops up, and you stick that right in there.
Now, Wesley, for your problem specifically, in the Extended Cut, I am going to show you how to go through if you have got full paths in here. I am going to show you how to go through and get rid of them, so all you are left with is the file name. Of course, if all of your images are in one folder, then you just point the image control to the new folder, and then you will have just the file names in here. We will do that with a special update query. That will be in the Extended Cut for the members. Silver members and up get access to all of my Extended Cut videos.
Of course, Wesley, I know you and I have emailed back and forth, so I know you are pretty cool and you are a Star Trek fan like I am. I have to do it at least once: "Shut up, Wesley."
That is going to do it for today. Wesley, I know you appreciate my sense of humor. That is going to be your TechHelp video. I hope you learned something.
Live long and prosper, my friends. I will see you next time, and Wesley, I will see you in the Extended Cut.Quiz Q1. Why is it not recommended to store actual images (as OLE objects or attachments) in an Access database?
A. It makes the database file much larger and less efficient
B. It allows for easier image manipulation within Access
C. It requires less disk space than storing file paths
D. It guarantees image compatibility with all database software
Q2. What is a better alternative to storing the actual image in the database?
A. Storing only the file name and the path to the image
B. Embedding the image as binary data in the table
C. Compressing the image before adding it as an attachment
D. Using a hyperlink field to link to online images
Q3. What problem can occur if you store the full, absolute path to an image in your database?
A. If the folder moves, image links will break
B. Images will always appear as low resolution
C. The database will delete the image file
D. The database will automatically move images with it
Q4. How can you avoid broken image links if the database folder moves?
A. Store the image path as a relative path under the database folder
B. Store all images on the C drive only
C. Always use absolute network paths for images
D. Embed all images in the database
Q5. Which function in Access allows you to dynamically reference the path of the current database's folder?
A. CurrentProject.Path
B. Application.CurrentUser
C. DatabasePath()
D. PathName.Current
Q6. When creating an image control on a form to display images, what should typically be stored in the text field for an image following this method?
A. Only the file name of the image
B. The entire image as binary data
C. The full absolute path to the image file
D. The URL of an online image
Q7. What string concatenation technique is shown to generate the file path for an image in the form control source?
A. Concatenating CurrentProject.Path with "\\Images\\" and the file name
B. Using only the file name and letting Access guess the folder
C. Setting the control source to a constant folder location
D. Relying on default Access image settings
Q8. Why should you avoid using reserved words like "picture" or "image" as field names in Access tables?
A. They can cause conflicts or errors in the database
B. They increase the database size
C. They make queries faster
D. They encrypt the field content by default
Q9. What does setting the form's "Tab Cycle" property to "Current Record" accomplish?
A. It restricts tab movement to fields in the same record only
B. It makes the form read-only
C. It prevents editing any fields
D. It switches the form view to DataSheet
Q10. If your images folder is always under the main database folder, what benefit does using a relative path method provide?
A. Image links remain valid even if the database is moved to another location
B. Images will be uploaded to the cloud automatically
C. Every image must be manually relinked if the folder moves
D. You can store unlimited images without using disk space
Q11. What is the recommended way to update existing records that store a full path, so they use just the file name instead?
A. Use an update query to extract and store only the file name
B. Delete all the image records and start over
C. Manually edit each field one by one
D. Store the file name in a new field and leave the old path as is
Q12. What is one feature mentioned as part of the Extended Cut videos for members?
A. Creating a browse button to select images using a dialog box
B. Embedding images as OLE objects
C. Using hyperlinks to online photo sharing sites
D. Converting all images to grayscale automatically
Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A; 11-A; 12-A
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's TechHelp tutorial from Access Learning Zone covers how to store images in your Microsoft Access database efficiently by saving only the file name and the path, rather than embedding the entire image. Instead of saving images as attachments or OLE objects, which can quickly bloat your database and cause performance issues, I recommend simply saving the location of each image as a text field in your table.
Recently, one of our students, Wesley, ran into a problem. He had followed my images tutorial and stored the full file path of each image in his database, which worked well until his company upgraded their computers and moved the database folder. Suddenly all of his image links were broken, because the folder location referenced in each record was no longer correct. If you have experienced this too, you know how frustrating it can be.
To help you avoid this issue, I want to share a better approach. By storing images in a subfolder directly under your database folder and referencing their location relative to the database, you can ensure that even if the folder moves, your image links will still work as long as the folder structure remains the same.
As a quick review, and for those who have not yet seen my images video, here is the general process. In your customer table, add a new field for the profile picture. Be careful not to use reserved words like 'picture' or 'image' for your field name. I usually name my field something like 'Profile Picture' and use a Short Text data type. Add this field to your form, so you can enter the file name of each customer's image.
Next, add an image control to your form. When you configure its Control Source, instead of setting a fixed path such as C:\\Users\\Richard\\Desktop\\Images, you should use the CurrentProject.Path function. This function returns the folder where your database is stored. You can combine this path with the name of the images folder and the value from your profile picture field, using string concatenation. For example, your expression will build something like [CurrentProject.Path]\\Images\\[Profile Picture]. This way, you only need to store the file name in your table, and the database automatically looks for the image in the correct location--no matter where the database folder might move.
Remember that if you want to add subfolders under your images folder, you can simply include those in the file name or adjust the expression accordingly.
Another preference of mine is to set the form's tab cycle property to 'Current Record.' This makes navigation more convenient, especially if your form has only a few fields.
Once set up, you will see that entering just the file name displays the image as long as it's found in the designated folder. You only have to update the Control Source if your folder structure changes, rather than editing every record in your table.
If you want to add the ability to browse for images using a button, I cover that process in the Extended Cut of the original images video. There, I walk through how to use a dialog box to find and select images, and populate the file name in your form automatically.
For students like Wesley who already have full paths stored in the table and need to convert them to file names only, I show how to use an update query to strip out the old path and leave just the file name in the Extended Cut video. Silver members and above can find that bonus content on my website.
If you are new to working with file paths and concatenation in Access, I have additional free tutorials available on those topics as well. Watching those will be very helpful before tackling the techniques described here.
For step-by-step instructions and a complete demonstration of everything I have discussed, visit my website for the full video tutorial at the link below.
Live long and prosper, my friends.Topic List Storing image file names and paths in a table
Adding a Profile Picture field to a table
Adding a Profile Picture control to a form
Configuring an image control to display pictures
Using CurrentProject.Path for relative image folders
Setting form controls to use file names only
Building the Control Source formula for images
Differentiating between full paths and file names
Adjusting form layout for image controls
Testing form image displays with multiple records
Changing Tab Cycle property to Current Record |
