Load Multiple Images 2
By Richard Rost
2 years ago
Auto-Load Multiple Product Images in MS Access Part 2 In this Microsoft Access tutorial, I will show you how to enhance your database by automatically loading multiple images using VBA. We will cover adding a status box, creating command buttons for managing image folders, and writing VBA code to import and display images. This is part 2. PrerequisitesRecommended CoursesUp Next
Keywords TechHelp Access, automatically load multiple images MS Access, Microsoft Access image loading, load images in database Access, import images Access VBA, VBA code images MS Access, Access import folder button, Access image import automation, Access image import code, MS Access status box, contact table images Access
Intro In this video, we continue working with Microsoft Access to load multiple images into your database by enhancing your contact form with a status box, adding buttons to open the import folder and import images, and writing VBA code to manage file importing. You'll see how to set up and use the shell command, track progress with a counter, and use the dir function in a loop to process files from a folder efficiently. This walkthrough makes it easier to manage image imports in your Access database. This is part 2.Transcript This is part two of my video series on loading multiple images into your database. If you haven't watched part one yet, go watch that first and then come on back.
Alright, so in yesterday's video we got our contact table all set to hold multiple images for a customer. We've got a field put in our contact form right there to display the picture. Now we're going to start actually putting some coding in here to get these pictures in from files on the hard drive.
Now the first thing I want to do is drop a status box on here so I can see what's going on. Remember I got the status box out here. If you haven't watched my video where I build the TechHelp free template, the status box is basically just it's kind of like my replacement for message box. I hate message box and stuff just pop it up in your face. So we are going to just literally copy this status box copy and we're going to paste it in here, and I'm going to put it down on the bottom.
Now I love using status box, especially when I'm developing because I can get all kinds of information and display it for myself. But then even after I'm done, this is a great thing for the user too because the user can then see some stuff too like, you know, copied for files or whatever you want to say. I'll make it big right now. Usually, I make it nice and large when I'm developing, and then when I'm done I make it smaller and just put in here messages for the user to see.
We also are going to need to steal the code if you go into the form module for the main menu. Let me move this over here so you can see it better. There we go. We need to grab this status subroutine right here, so copy that and if you want to you can go view project explorer and then open up the contact form right there. That's the code for the contact form. There's nothing in here right now, and then just paste that in. So now that form has that sub in it.
Okay and it's unique to the form. Alright so we got that. We're good there. We close and reopen the main menu and in here now we have a little status box. We can put messages in here. Alright this is handy to see what's going on. Let's get this guy out of the way. That's just a hidden customer ID which it's got to be on the form for some other purposes, but we don't need to see it. That's why it's red.
I'm gonna put two buttons on here. One button is to open up the import folder. It just makes it handy to open up that folder because if you want to add, you know, pictures real quick you hit the button it opens up the folder then you just drop the pictures in that folder. So let's go to form design and we're gonna grab a command button drop it right meow cancel the wizard and we're just gonna put on here open import folder and you can make that text smaller if you want.
I like to go to nine point for stuff that's not important and we can make the button a little bit smaller like that. Okay so this is our open the import folder button. Let's change the name of it to open import open import folder button and let's right-click on it go to build event. Okay what are we gonna do in here now we're gonna open up a folder in a file explorer window right that points to that folder location where our import folder is so we can just easily drop files in there.
So to run another program we use the shell command. Shell says I want you to just run something else you can use it to run Word, Excel, notepad whatever we're gonna use it to run Explorer. Now explorer.exe it's part of Windows it should be in your path you don't have to specify the exe there and then after that we're gonna put the full path to the import folder.
Okay but that's got to be inside of quotes so that means we need double double quotes in here right that puts an actual literal quote in the string. Close that string and current project dot path that's our database folder and import folder backslash and then we got to close the quotes in the string and then close the string. See what we're getting there? We're getting current project dot path so that's c colon backslash whatever database that is a slash import folder and that whole thing is inside of quotes and that's necessary.
Okay and then one more parameter out here comma how do you want to see it? I want to see VB normal focus otherwise you won't see it it just runs in the background hidden so make sure you put VB normal focus there. Okay save it give it a debug compile I'm gonna close that let's close it close it open it and hit the open import folder button and it opened on my other screen but it opened up down there I got a two monitor setup so this opened up to the right folder but on my other screen and yeah you can control that and move Windows around but that's a whole different video.
Okay so here it is right there good to go now we got a spot where we can very easily drop our files into in fact while I'm at it we're at that point now let me open up the import folder again and I'm gonna copy a bunch of images to it so we got some stuff to work with and there we go got a bunch of pictures in here that my routine can then import okay we don't need this let's close that now all right now we're ready to make a button to actually perform the import so copy this button copy paste right and then we're gonna put on here import images yes I know import is not exactly the right technical term because we're not importing stuff into the database but as far as our user's concerned the database is importing the information for the images okay okay it's a training issue all right this will be our import button right click build event.
Alright here's where the code gets handy and I'm gonna turn off this just to save some space. The first thing is we're gonna set variables to where our images path and our import path is so we need dim images path as a string and import path as a string and we'll set those up as images path equals current project dot path and images and our import path where the stuff ghost or where the stuff comes from current project dot path and import folder. And it's nice to use current project dot path because if your database moves everything's relative right if you're you know if you if you're developing locally and it's in C colon backslash database then you move it up to the server well now the database path is you know up there you can you can change you can change this current project dot path to something else you could put it in a you know a global constant or whatever.
Alright we're gonna start off by telling the user something's happening right status importing images I'll put it in the status box. I'm gonna want to counter so I know how many images were imported so I could tell the user hey you know six images imported so let's make a counter variable right counter as long and we'll start that off by saying counter equals zero before anything happens always initialize your variables.
Alright now the next thing I want to do is get a list of the files in the import folder and to do that I'm gonna use the dir command. Now if you're familiar with DOS and Windows command prompts at all you type in dir and it gives you a list of the files and folders that are in the file folder that you specify right. I open up a command prompt type in dir and there's all the files and folders in that folder.
Okay same thing in VB dir will just loop through all the files and folders in the current folder and bring them back to you one at a time so let's make another variable over here we'll call it file name as a string and down here I'm gonna say file name file name file name equals dir and then you want to specify the path that you want to search in so it's going to be my import path with a star dot star on the end of it that says give me all of the files and yes I know I spelled import path wrong and this is one of the reasons why when I'm typing I like to do all lowercase and I don't know why I haven't been because if I do this I can immediately see it didn't camel case right it didn't capitalize automatically and that would have just visually told me I typed it in I spelled it wrong I'd say 90 of my mistakes are spelling errors all right and yeah there are some other options for dirt I cover them in my access developer course you can look through directories you can look through hidden files all that kind of stuff, but this will just bring you back a list of the normal files that are in that folder one at a time.
Okay and we can do that with a while loop we'll say while file name is not an empty string because when you're out of files or if no files exist in the first place then the dirt command will return an empty string. Alright so you do your while loop you put your while end down here and in the middle when you're done doing whatever processing you're gonna do you say file name equals dir just dir by itself and that will go out and get the next file using the parameters that you specify it up top. Okay that's all you got to do and then in the middle here you'll do some stuff how about for now we just status that file name so we could see what we're getting right.
That's what I like the status for instead of message boxing it then you got us hit enter enter enter. Alright let's see what we get so far save it debug compile go back over here close it open it and let's hit the button click oh look at that I'm gonna zoom in so we can see it shift F2. Alright now remember the status box runs backwards so importing images goes first and then here is a list of all of the files in that folder that's pretty cool right.
Now we just have to process each one of these files we got to figure out what kind of a file it is JPEG GIF whatever make sure it's allowed right we don't want there to be a text file or PDF in there we're gonna ignore those to have to figure out the extensions and then we have to write some code to simply add that as a record in the contact table and we will start doing all of that in tomorrow's class. So tune in tomorrow same bat time same bat channel for part three or of course if you remember you can watch it right now because I'm gonna keep recording today and it'll be posted as soon as I'm done tonight.
But that is gonna be your tech help video for today. I hope you learn something live long and prosper my friends I'll see you tomorrow for part three. A special thank you and shout out to our diamond sponsor Juan Soto with access experts software solutions their manufacturing experts specializing in Microsoft access and SQL server Juan is a 13-time Microsoft access MVP check them out at access experts.For readability:
If you enjoyed this video, please give me a thumbs up and post any comments you may have below. I do try to read and answer all of them as soon as I can. Make sure you subscribe to my channel, which is completely free. Click the bell icon and select all to receive notifications when new videos are posted.
Want to learn more? Click the show more link below the video to find additional resources and links. YouTube does a pretty good job of hiding it. It's right down there, see this part of the description here, right? The name of the video is up here. There's a little show more down there, right down at the bottom. It's kind of hard to find, but once you click on that, you'll see a list of other videos, additional information related to the current topic, free lessons, and lots more.
YouTube no longer sends out email notifications when new videos are posted as they used to do. But if you'd like to get an email every time I post a new video, click on the link to join my mailing list. You can pick how frequently to get emails from me, either as they happen, daily, weekly, or monthly.
Now, if you'd like to become a paid member of my channel and receive all kinds of awesome perks, click on the join button. You'll see a list of all the different membership levels that are available, each with its own special perks, including my extended cut videos, access to my code vault (lots of VBA source code in there), template downloads, and lots more. I'll talk more about these perks at the end of the video.
Even if you don't want to commit to becoming a paid member and you'd like to help support my work, please feel free to click on the tip jar link. Your patronage is greatly appreciated and will help keep these free videos coming. I've got some puppies to feed, but don't worry. No matter what, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more, and they'll always be free.
Now, if you really want to learn Access and you haven't tried my free Access Level One course, check it out now. It covers all the basics of Microsoft Access including building forms, queries, reports, and more. It's over four hours long. You can find it on my website or on my YouTube channel. I'll put a link down below you can click on. And did I mention it's completely free? The whole thing, free. Four hours. Go watch it.
Okay, okay. A lot of you have told me that you don't have time to sit through a four-hour course, so I do now have a quicker Microsoft Access for Beginners video that covers all the basics faster, in about 30 minutes. And no, I didn't just put the video on fast forward. But I'll put a link to this down below as well.
Now, if you like Level One, Level Two is just a dollar. That's it, one dollar. That's another whole, like, 90-minute course. Level Two is also free for paid members of any level, including supporters. So, if you're a member, go watch Level Two. It's free.
Okay, want to get your question answered in a video just like this one? Visit my TechHelp page and send me your question there. Members get priority, of course. While I do try to read and respond to all of the comments posted below in the comment section, I only have time to go through them briefly a couple of times a month, and sometimes I get thousands of them. So send me your question here on the TechHelp page, and you'll have a better chance of getting it answered.
And while you're on my website, be sure to stop by my Access Forum. We've got lots of lively conversations about Microsoft Access and other topics. I have a fantastic group of moderators who help me answer questions. Shout out to Alex, Kevin, Scott, Adam, John, Dan, Juan, and everybody else who helps out on the site. I appreciate everything you do. I couldn't do it without you.
Be sure to follow my blog, find me on Twitter, and, of course, on YouTube. Yeah, I'm on Facebook too, but I don't like Facebook. Don't get me started.
Now, let's talk more about those member perks. If you do decide to join as a paid member, there are different levels: Silver, Gold, Platinum, and Diamond.
Silver members and up get access to all of my extended cut TechHelp videos, one free beginner class every month, and some other perks.
Gold members get all the previous perks, plus access to download the sample databases that I build in my TechHelp videos, plus access to my code vault where I keep tons of different functions that I use, the code that I build in most of the videos. You'll also get higher priority if you do submit any TechHelp questions. Now, answers are never guaranteed, but you do go higher in the list for me to read them. And if I like your question, you got a good chance of it being answered. You'll also get one free expert-level class each month after you finish the beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions. You get access to all of my full beginner-level courses for every subject. I cover lots of different subjects like Word, Excel, VBA, ASP, lots of different stuff, not just Access. These are the full-length courses found on my website. You get all the beginner ones. In addition, once you finish the expert classes, you get one free developer class per month, so lots of training.
And finally, you can also become a Diamond sponsor. You'll have your name or your company name listed on a sponsors page that will be shown on each video as long as you're a sponsor. You'll get a shout-out in the video and a link to your website or product in the text below the video and on my website.
So, that's it. Once again, my name is Richard Rost. Thank you for watching this video brought to you by AccessLearningZone.com. I hope you enjoyed. I hope you learned something today. Live long and prosper, my friends. I'll see you again soon.
TOPICS:
- Drop a status box on the form
- Copy and paste the status box
- Create a status subroutine in the form module
- Remove unnecessary elements from the form
- Add a button to open the import folder
- Write code to open a folder using shell command
- Create variables for images path and import path
- Display import status in the status box
- Initialize a counter variable for tracking imports
- Use the dir command to list files in the import folder
- Implement a while loop to process files in the folder
- Display file names in the status box
COMMERCIAL:
In today's video, we dive into part two of our series on loading multiple images into your database. We begin with setting up a status box to track what's happening, especially handy during development. Then, we add coding to import pictures directly from your hard drive. We walk through adding a button to open the import folder easily and another one to perform the image import. Along the way, we use the shell command and create a loop to handle file importing. It's all about making the process seamless and user-friendly. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.Quiz Q1. What was set up in part one of the video series?
A. Contact table to hold multiple images for a customer
B. A form to display customer details
C. A report to track customer transactions
D. A module to send emails to customers
Q2. Why does the instructor prefer using a status box over a message box?
A. Because status box has more color options
B. Because it allows displaying continuous messages without interruptions
C. Because it has a larger text size
D. Because message box is not available in the current version
Q3. Which command is used to open a folder in a file explorer window?
A. Run
B. Execute
C. Shell
D. Launch
Q4. Why is it important to use double double quotes in the shell command's path string?
A. To avoid syntax errors in VBA code
B. To separate folder paths with spaces
C. To include literal quotes inside the string
D. To form a valid directory path
Q5. What variable type is used to count the number of images imported?
A. Integer
B. Double
C. Long
D. String
Q6. What is the function of the dir command as explained in the video?
A. To delete files from a directory
B. To rename files in a directory
C. To list files and folders in a specified directory
D. To move files to another directory
Q7. How does the program process files sequentially in the import folder?
A. Using a for loop
B. Using a while loop
C. Using a do-while loop
D. Using a for-each loop
Q8. What does the 'current project.path' refer to?
A. The current working directory of the database
B. The server address where the database is hosted
C. The internet URL of the database file
D. The software version of the project
Q9. Why would the function return an empty string when using the dir command?
A. When the file name is too long
B. When there are no files in the specified folder
C. When the specified path is incorrect
D. When the system clock is out of sync
Q10. What will the provided status box show during the import process?
A. Progress bar of the import
B. The name of each file being processed
C. Detailed information about each image
D. The format type of each image
Answers: 1-A; 2-B; 3-C; 4-C; 5-C; 6-C; 7-B; 8-A; 9-B; 10-B
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 continues our discussion on loading multiple images into your Access database. If you have not watched part one yet, I strongly recommend doing so before moving forward with this lesson to ensure everything makes sense.
In the previous session, we set up our contact table to handle multiple images for each customer and added a field to the contact form for displaying these pictures. Now, we are ready to write the code necessary to actually import those images from files stored on your hard drive.
To begin, I like to use a status box on my forms so I can see ongoing processes during development. The status box acts as a replacement for the standard message box, which tends to be disruptive with its pop-up style. I prefer having messages appear directly on the form, keeping the user informed without interruption. I positioned the status box at the bottom of the form, making it fairly large for development and then scaling it down after everything is finished, so it only displays updates essential to the user.
The next step involves copying a subroutine that controls our status box from the main menu form module. This subroutine handles posting messages to the status box. By placing this in the contact form's module, each form can independently display messages relevant to its tasks.
I also remove any unnecessary form elements to clean things up, such as hiding fields like the customer ID if they do not need to be seen by users. For organizational purposes, I typically color such fields red to indicate that they are meant to be hidden.
Now that setup is finished, I add two buttons to the form. The first opens the import folder in Windows File Explorer, making it easy to quickly drop new images into the right directory. After creating the button, I change its label and adjust the font size to keep it in line with other less important controls. The code behind this button uses the shell command to open explorer.exe, pointing directly at the import folder path. I use CurrentProject.Path to ensure the path remains relative, which is helpful if the database ever moves to another location. Adding VBNormalFocus as a parameter ensures the folder window comes to the front rather than running hidden in the background.
Once this is in place and working, I test out the button to make sure it opens the correct folder. This is also a good time to drop some sample image files into the import folder so we have something to work with in the next step.
Next, I add another button labeled "Import Images." While the term "import" might not be technically precise since we are not putting images directly into the database articles, from a user's perspective it is the most straightforward action. After creating the button and setting its name, I get ready to write the code that handles the actual import process.
The key here is to establish string variables that store our images path and import folder path, both built with CurrentProject.Path, which as mentioned, makes it easy to move the project to a new folder or server without changing hardcoded locations.
Before starting the import loop, I set the status box to let the user know that images are being imported. I also create and initialize a counter variable so I can track how many images are brought in during this process.
To read the files in the import folder, I take advantage of the VBA Dir command, which operates similarly to the DOS Dir instruction. Dir retrieves the list of files in a specified directory one at a time, which you can process in a loop. I create a filename variable and start off the loop by calling Dir with the import path and a wildcard to grab all files. It is important to keep an eye out for spelling mistakes when declaring variables and using them, as this is a frequent source of errors.
To process all items in the folder, I use a While loop. The loop continues running as long as Dir returns a non-empty string, meaning there are still files to process. After handling each file, I call Dir again without any parameters to move on to the next file. For this demonstration, I simply display each filename in the status box, giving instant feedback without cluttering the screen with message boxes.
Testing at this stage confirms that the status box accurately lists all files found in the import folder. The next logical steps, which will be addressed in the following session, involve checking each file's extension to be sure they are images that the application recognizes and writing additional code to add each as a record in the contact table.
That wraps up today's lesson. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below.
Live long and prosper, my friends.Topic List Add a status box to the contact form
Copy and paste the status box onto the form
Import the status subroutine into the form module
Add a button to open the import folder
Change the button name for clarity
Write VBA code to open the import folder with Shell
Use currentproject.path for folder locations
Add a button to trigger image import
Create and set variables for image and import paths
Display messages to the user in the status box
Initialize a counter to track number of imports
Use the dir command to retrieve files from the import folder
Loop through files in the import folder with a while loop
Display file names in the status box during import |
