How to Automatically Load Multiple Images for Customers, Products (or Anything) in Microsoft Access
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost. Got a good one for you today. I'm going to show you how to automatically load multiple images for products or customers or whatever you want at one time in Microsoft Access.
Today's question comes from Megan in Chandler, Arizona, one of my Platinum members. Megan says, I have to store multiple pictures for each of my products, and right now I'm just putting in the file names one at a time like you show in your images video. However, this is very time-consuming. Is there a way that I can select just a bunch of images and say these are all for product XYZ and have them all load up automatically? It would save me hours every day, and I will be in your debt, great Klingon warrior. Kapla.
Now, the Star Trek reference alone will get you a video, and being a Platinum member, well, that just bumped your question up to the top of the list. So let's talk about it.
Okay, in my images video, I show you how to display images in your forms and reports by just simply putting the file name in a text field and using the image control. We do not use attachments. We do not store images inside our databases. All right, they bloat your database, and they're bad for you. Go watch this video for all the information you need on that.
Now, in the extended cut, I teach you how to make a browse button. So you can click on that, pick a file, and it puts the file name in there for you automatically. Today, I'm going to show you a trick where you can set up an import folder, and you can drop all the pictures that you want into the import folder and then click a button, and they'll all get loaded into your database on whatever record you want. Let me show you a sample.
All right, so today, what we're going to do is we're going to take a customer form. We're just going to drop pictures in the contacts. Let's pretend this was a product record. You can have another table that's related to it where you can have multiple products, okay? And then those will be displayed in here. So what we're going to do is we're going to set up an import folder. We'll make a button so you can click on it, and it will open up that import folder. It's just a folder underneath your database folder, right? We're going to call it the import folder.
Now, any pictures that you want to import into the database, you put in this folder. So I'll just go grab some. All right, I copied these pictures into the import folder, and now I'm going to go back to the database. I'm going to hit import photos, and look at that. They all came in automatically. There's this one, this one, this one, this one, this one, and they're all stored in the contacts under this customer. Whatever images were in that folder are just immediately tagged in here.
These aren't actually imported into the database, but we've stored the location in the contact table. So if you come down here, here they are. And there's the locations. We're going to copy them to an images folder under the database, rename them, and store them. So they're under here in images. There they are with the unique file names now, okay? And you can use them in your database, and they're attached to the customer right there.
And this could be, like I said, this could be a product, this could be students, this could be whatever you want documents, doesn't matter. So that's what we're going to cover in this series.
Let's talk some prerequisites first. What do you need to know before we get started today? Well, it's going to be a developer-level video. So you're going to need some VBA. Obviously, go watch that images video that I mentioned earlier. Now, if you've never done any VBA programming before, don't worry, it's not scary. Go watch this video. It'll get you started, everything you need to know in about 20 minutes.
We're going to use a while loop to loop through the file, so make sure you understand while loops. We're going to use some of the string functions left, right, mid, in string, those guys, make sure you know these. We're going to use in string reverse. That's finding something from the end of a string. We'll use that to find the file extensions. We're going to use if then statements, make sure you know how to use these. And we're also going to use a select case statement. So brush up on this one.
We're going to use a record set to add the file to the contact table. All right, so make sure you know how to use record sets. These are all free videos. They're on my website. They're on my YouTube channel. Go watch all of these if you're not familiar with any of this stuff, and then come on back. This is going to be a developer, developer. This is a higher-end developer bit of code we're doing today. So for those of you who keep telling me you want more advanced stuff, well, here's some advanced stuff. I usually cover this kind of stuff in my developer course.
Now, we're also going to use some basic file input-output, reading through files in a folder, that kind of stuff. I don't have TechHelp videos for this kind of thing. I'm going to show you what you need to know today in today's video. But if you want to learn more about these things in Access Developer 30 and 31, I cover basic file input-output, reading, writing text files, file and folder navigation, copying files and lots more. So I'll put links to these down below as well if you want to learn more about these things.
All right, we're going to start with a copy of my TechHelp free template. This is a free database. You can download a copy from my website if you want to. I'm going to call this one load multiple images. And we're going to put this in its own folder. I'm just going to put mine on my desktop. So new folder, we'll call this images database or whatever you want to call it. And I'm going to put this guy in there. Okay, now inside of this folder, there we go, it opened up on my other screen. We're going to create two other folders. One is going to be our images folder. And that's where we're going to store images that are actually logged in the database.
Okay, remember, we don't store files in the database itself. We put them in a folder, and we just store the reference, the file, and path to that image in the database itself. Then we're going to set up another folder called our import folder, new folder, import folder. And that's where we're going to put files that we have not yet inputted or imported into the database. And we'll just drag them all, drop them in there like you just saw me do a minute ago, hit a button, and they'll all get copied up to the images folder and then deleted, and then logged, and then everything will be hunky-dory. Okay, all right.
And yes, for those of you that are asking, can't you just use a button to select multiple files with the file dialog? Yes, you can. It's a lot more advanced. I find this method a lot easier, actually. And I will cover that in the extended cut for the members after we're done with what we're doing here. But this works easy, and the programming for this is not that difficult.
All right, so let's open up our database. And the first thing we need to store is a location to keep track of the image file name. So I got customers and contacts already set up in here. What you want is if you want to have multiple pictures associated with something, you're going to want to have another related table to store all of that something. If you're doing a customer's profile picture, and it's just one, you can just store the file name in here, right in the customer table, which is what we did in the images video.
But, if you've got multiple profile pictures or multiple pictures that you want to associate with this customer, you're going to need a second related table. So if this is products, you can make a product images table, and you can store 15, 20, 1,000 pictures for that product in the related table. I've already got customers and contacts set up, so rather than waste all this time reinventing the wheel, we're just going to store the pictures related to this customer in the contacts table.
Okay, so let's go to the contact table design view. And right down here on the bottom, I'm going to put my image, don't use the word image image is a reserved word, just like picture is a reserved word, you want to try and avoid those as possible. If you're not sure if something is a reserved word, just put my in front of it, like instead of date. Date's reserved, my date, or whatever. All right, save this. And I'm going to go, let's go into here and just put some sample data in one record. I'm going to put in my image for me. I'm going to put in here, I'm just going to put in Picard.jpeg. And I'm going to throw a file called Picard.jpeg in the images folder, just so we can see that it's working.
Okay. All right, here's my images folder. And I'm going to grab a copy of Picard that I just happened to have handy. We're going to drag and drop that into here. And there we go. So now you'll see that that's in there. When we build the form, you'll see that it's working.
All right, next up, let's open up the contact form where we're going to store our pictures, or we're going to display the pictures, I should say. And we don't need the notes to be quite that big for this example. So I'm going to shrink up notes just like that, and we're going to put a nice big image right here in the footer. So we're going to go to form design, and find the image control, which is this guy, do not use this guy, this is different, this is an unbound object frame, don't use this guy, that's a bound object frame those are different completely. You want this guy, image. All right, draw out a box down here, like about so. You're going to cancel this, we just want an empty image frame right there.
Okay. If you want to give it a background color, I like to give it a background color so that people can tell something's there, like that. All right, that's just empty. Let's open up the properties for it. I don't like image eight, let's call it my image object. All right, so we don't confuse it with the actual image field value. Now, for the control source, I want to use my image, which is a field in the table underneath this, right, my image, but I need to tell the database to look in the database folder, and then go into the images folder under that to find my image.
All right, how does that look? I'm going to zoom in, so you can see this better, shift F2. Okay, it's going to be equals current project.path, that's the current database folder, and backslash images, that's my images folder, right, backslash, close quotes, and then my image, the field name. So it's going to be whatever the path is to the database, right, C colon, backslash, users, backslash, Rick, backslash, desktop, backslash, whatever the database folder name is, slash images slash Picard. All right, that's going to be put all together, and it's going to be in the control source to this guy right there. All right, save it, close it, open it back up again, and there's Picard, right, because I got Picard.jpeg in the table. So that's easy.
So far, nothing new. We've done all of this before in the images video. Now we're actually ready to start building our buttons and put some code in to pull in the multiple pictures and put them in different records up in here. And we'll start that in tomorrow's video. So tune in tomorrow, same bat time, same bat channel.
Yeah, we got all of the prep work done. Sometimes the prep work can take a few minutes, right, but you got to get the database set up properly. And of course, if you remember, you can watch it right now, because I'm going to post it as soon as I'm done with recording it in just a few minutes. But that's going to be your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you tomorrow for part two.
A special thank you and shout out to our diamond sponsor, Juan Soto with Access Experts Software Solutions. They're manufacturing experts specializing in Microsoft Access and SQL Server. Juan is a 13-time Microsoft Access MVP. Check him out at accessexperts.com.
For a complete video tutorial on this topic, please visit https://599cd.com/LoadMultipleImages
Top comments (0)