Load Multiple Images 3
By Richard Rost
2 years ago
Auto-Load Multiple Product Images in MS Access Part 3 In this Microsoft Access tutorial, I will show you how to automatically load multiple images and process different file types, such as JPEG, GIF, and BMP. You'll learn how to identify valid image extensions, handle errors, and generate unique file names based on date-time stamps. This is part 3. PrerequisitesLinksRecommended CoursesUp Next
Keywords TechHelp Access, How to Automatically Load Multiple Images in Microsoft Access, Load Multiple Images series, Microsoft Access Import Images, Access VBA tutorial, Loop through files Access, Access file extension check, Access InStrRev function, Error handling Access VBA, Rename files Access VBA, Copy files Access VBA, Image import Access VBA, Debugging Access VBA
Intro In this video, we continue with part three of the Load Multiple Images series for Microsoft Access. I will show you how to determine the file type of each item in your import folder, handle files with missing extensions, and ensure that only valid image files like JPEG, GIF, BMP, and PNG are processed. You'll learn how to use string functions to extract and verify file extensions, generate unique filenames using date and time stamps with a counter, and copy the approved files into your images folder. This is part 3.Transcript This is part three of my Load Multiple Images series. If you haven't watched parts one and two yet, go watch those first and then come on back.
Alright, so at the end of part two, we got our code to the point where we can loop through all of the files in the import folder and display what they are, and that's a really easy loop right there. Okay?
Alright, next up, we have to figure out what kind of file it is. Is it a JPEG? Is it a GIF? Is it a bitmap? Only certain types of files will work as images in your Access database. You don't want to allow Word documents or Excel spreadsheets.
So we'll need two more variables. We'll need dot position because we have to look at the file name and find where the dot at the end is. So we find the file extension, right? The dot position as a long, and then knowing the dot position, we can get the extension as a string. Okay?
Alright, so inside our loop right here, we're gonna say dot position equals, now I want to find the period, but I want to start from the end of the file name. Because we start from the beginning, there could be periods in the file name itself. So we're gonna use the in string reverse function. So it's in string reverse, in the file name, and I'm looking for the period. The first period starting from the right.
Now if the dot position is greater than zero, then we have a valid extension. So extension equals, now I want to take the L case. It's just easier if everything is lower case for doing comparisons and stuff. And I want to find the mid of file name comma dot position plus one. Okay, let's unpack this.
Now recently, I just personally discovered that you could use the mid and not specify the third parameter to start in the middle of a string and get the whole right part of that. It blew my mind. I used to have to use the right function and then calculate the length and subtract the, that was just a pain. I talk all about that in this video. Go watch this. It might blow your mind too.
It was news to me. So this will basically say go to that dot position plus one so we don't want the dot. Give me the mid of the file name from that position to the right all the way to the end of the string and then give me the L case of that. So that should give you dot gif, dot jpeg, dot whatever, okay?
Otherwise, else, now this is where we do have a problem. That means that there's no extension files. You might have a bad file in here. You want, you know, the human to take a look at it. So I'm going to actually message box this. Sometimes for critical errors that, you know, you want to stop processing, I will use a message box and then exit the sub. So cannot determine file extension and tell them what the file name is. And then exits up. We'll get out of town. All right. You got a bad file in your folder there pal.
Alright. So let's see what we got now. Status the file name and let's put in here the extension extension and we'll put in here extension like this. We'll see what it looks like. Let's debug compile and let's take a peek. Ready? Click.
Alright. There's all of our extensions. Let's zoom in so we can see it better. Alright. Found the jpeg, found the gif, found the gif, found the jpeg. Everybody looks good. Okay. Let's throw a bogus one in there just to test our error checking. Where's my folder? I think I closed my folder. Let's open it back up again. Open. And let's make, let's make Kip not have an extension in here. I'll just do that. Kip. Yes. Yeah. Of course, Windows is going to yell at you.
Alright. Let's see what happens in our code. Ready? Go. Cannot determine the file extension of Kip. Yes. And that stops execution. That's exactly what I want. And let's put Kip back. So he's Kip. Yes. Come on. Come on. There it is. That gif. Are you sure? Yeah. Okay.
And while we're at it, before our code gets too complicated, let's put some commenting in here. Determine file extension. So we know what kind of file we're dealing with. And here we go. Okay. We'll put in here. Valid extension found. No extension found, right? And these aren't necessarily for other people. These are for you in the future. I wish 10 years ago me would have commented my code as good as I do now. I used to be like, oh, no one else is going to have to read this. But then you go back and try to read something that you read 10 years ago and you're like, what is this? Okay.
Alright. Now we're going to only do stuff with this file if it's of one of the extensions that we like. Okay. So only process valid extensions, valid image extensions. And for this, we're going to use a select case. Why? Because it's easier to write than an if, then, if, then, else, if, then, else. Right? It's just like this. Select case extension. Okay.
We're going to say case and you could put all the extensions that you want in a single line. So BMP, comma, GIF, comma, JPEG, comma, for you Apple users, JPEG, like that, Alex. And whatever else you want, PNG. Okay. And then down here, case, else, anything else, we're just going to do nothing, not a valid image. And we'll, we'll status unknown file type and put the file name in there if it's something like a text file or something else that doesn't match. End select.
Alright. So let's move this up into here now so we can only see this get displayed if it's a valid extension type. Alright. We'll put in here a valid extension. Alright. Save it. Let's give it a debug compile. Let's come back out here. Let's throw something else in here that we shouldn't have in here, like a text file. Alright. There, I just copied a readme.txt into that folder. Okay.
So back out here and import images. Okay. Let's see what we got. Started here. GIF, GIF, valid, valid, valid, valid, valid, valid. Up there we go. Unknown file type, readme.txt. Okay. So the user's going to be notified that they got an invalid one in there. So that's working.
Alright. That all looks good so far. Close that. What's next? Well, let's clean this up a little bit instead of saying valid extension. Let's say, let's just do this. Let's do, let's do importing and file name. That's clean enough.
That's good enough. We know we want to increment our counter at this point. Counter equals counter plus one because we're importing another one. Okay. Now, I want to, when I copy it, I want to generate a new file name for this file because what if I've already got a Kirk.jpg in there? And it's a different picture. So what I like to do is I like to assign either a random file name or something based on a time and date stamp to make sure that you can't have multiple files with the same file name. Alright.
So we're going to make a new file name. Random numbers are fine too, but I like my method. Watch my method here. So we're going to need another variable for new file name as a string. I've been trying to get in the habit of maybe declaring these closer to where they're first used, but old habits die hard. I've always declared all my variables at the top of a subroutine. There's benefits to both ways, but that's just how I do it. Okay.
So in here, we're going to set up a new file name. And I'm just going to make it so it's a date time stamp with the counter on it. So it's going to look like this format. Now, right now, right in this format, year, month, day, hour, minute, seconds. Okay. So that's the exact time stamp. And then I'm going to add on to that counter. So even if you're doing an import, and there's, you know, eight files, you're going to have the same date time stamp because all eight of them might import in less than a second. Right. So you add the counter on the end of it to make sure those are all unique.
Chances of someone else doing this again with a second run within a second are astronomical. So if you want to add a random number onto the end of that, go right ahead. I think this is perfectly fine. Okay.
Then on to the top of that, we have to also add dot and then our extension. We know what type it is. A GIF, a JPEG, whatever. Okay. So that's our new file name. So let's put it right here. Right. Create new file name based on date time stamp. Okay. Now we're going to copy the file to the images folder with that new file name.
Okay. And that's going to look like this file copy, import path and file name comma images path and new file name. Right. We know where the import path is. It's this thing. Import path. Where are you? Right there. It's the current database import folder and the new place is going to be the images. Images folder. And so we just tack on the old file name, which we got up here and the new file name, which we just made. So we're going to copy database folder, you know, image import folder, Kirk.jpg is going to become images folder, you know, 202408, whatever.jpg. That's what that's going to do.
Alright. You ready to test it? See if it works. Save it. Debug, compile, come back out now and ready, click. Alright. Let's see what happened. Let's open up the import folder. Alright. These are still here. We're going to delete these eventually. We'll get to that, but let's go check. Let's check out the images folder now. And oh, look at that. There's all the files and they got their newly assigned file names. See that? They're all based on the timestamp with the counter at the end and Picard's in there too.
Okay. Now I'm going to delete these guys. Except for, I'm not going to delete Picard because I want Picard in there. Alright. Delete those because we're not done. We're going to be working with these again. Okay. We're going to be copying them over again.
Next up, after we copy it, we're going to delete it. But before we do that, we want to verify that that file is there. Alright. We'll do a little error handling to make sure that the file copy operation was a success. Then we'll delete it and we'll check to make sure the delete actually works too. Right. And then after all that, we got to add this as a record to the database and we'll start doing all of that in tomorrow's video.
Actually, today, this is being released Friday, August 23rd, 2024. So the next video will be on the 26th, which is Monday. So tune in Monday, same bat time, same bat channel. Or again, members, you can watch it right now. That is going to be your TechHelp video for today.
I hope you learned something. Live long and prosper, my friends. I'll see you on Monday. Enjoy your weekend and take care.
TOPICS:
Finding the dot position of a file extension
Using InStrRev function to find the dot
Extracting file extension with Mid function
Converting extension to lowercase with LCase function
Handling files with no extension
Using MsgBox for critical errors
Displaying file extension results
Debugging and compiling the code
Testing error handling with files lacking extension
Commenting the code for clarity
Using Select Case for valid image extensions
Processing only valid image files
Handling unknown file types
Generating new file names with Date and Time stamp
Appending counter for unique file names
Appending file extension to new file name
Copying files to the images folder
Verifying file copy operation success
Deleting original files after successful copy
COMMERCIAL:
In today's video, I will guide you through part three of our Load Multiple Images series. We'll build on where we left off in part two, looping through files and figuring out their types like JPEG or GIF to ensure only valid image files are imported into your Access database. I'll show you how to determine file extensions using string manipulation techniques, handle unknown file types gracefully, and assign unique, timestamp-based file names for your images. Finally, we'll copy the files to a new folder and prep for the next steps in our process. 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 is the purpose of finding the dot position in the filename?
A. To determine if the file is corrupt
B. To find the file extension
C. To identify the file creator
D. To determine the file size
Q2. Why is the `InStrRev` function used to find the dot position?
A. Because it is faster than other functions
B. Because it searches from the beginning of the filename
C. Because it searches from the end of the filename
D. Because it can replace the period
Q3. What happens if a file without an extension is found?
A. The file will be automatically renamed
B. The program will continue without processing that file
C. A message box will display an error and the program will exit the sub
D. The file will be deleted
Q4. Why is the extension converted to lowercase using the `LCase` function?
A. To enhance readability
B. To ensure consistency for comparison
C. For faster processing
D. To convert all alphabets to uppercase
Q5. Which of the following file extensions is **not** mentioned as valid in the video?
A. BMP
B. TIFF
C. JPEG
D. PNG
Q6. What is used to handle different file extensions in the video?
A. If-Else statements
B. For loops
C. Select Case statements
D. While loops
Q7. What is the purpose of the counter when generating new filenames?
A. To keep track of processed files
B. To include it in the filename to avoid duplicates
C. To count the number of invalid files
D. To sort the files in a specific order
Q8. What format is used for the new filename?
A. Date with a random number
B. Original filename followed by the date
C. Full date and time stamp with a counter
D. Alphabetic prefix with a counter
Q9. What does `FileCopy` function do in the code?
A. Copies a file from the database to the import folder
B. Copies a file from import folder to the images folder
C. Renames a file in the same folder
D. Deletes a file from the images folder
Q10. What will be checked after copying the file successfully?
A. The file size
B. The new filename
C. The presence of the file in the images folder
D. The last modified date of the file
Q11. What is the suggested course of action if the file copy operation fails?
A. Retry the copy operation
B. Display an error message
C. Ignore the error and continue
D. Restart the program
Q12. What will happen to the original files after they are successfully copied?
A. They will be renamed
B. They will be left unchanged
C. They will be moved to the database folder
D. They will be deleted
Answers: 1-B; 2-C; 3-C; 4-B; 5-B; 6-C; 7-B; 8-C; 9-B; 10-C; 11-B; 12-D
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 Load Multiple Images series, picking up with part three. If you have not yet reviewed parts one and two, I recommend you start there to familiarize yourself with the process so far.
At the conclusion of part two, we set up our code to loop through all files in the import folder and display each file's name. Now, the next essential step is to identify the type of each file. We need to ensure that only compatible image types, like JPEG, GIF, or BMP files, are processed by your Access database. Other formats, such as Word documents or Excel files, should be excluded.
To achieve this, I introduce two new variables. The first is used to determine the position of the period in the file name, marking where the extension begins. The second variable stores the file extension itself as a string. Inside our loop, we utilize the InStrRev function, which allows us to search the file name for the period starting from the end. This is important because file names may contain multiple periods, but the extension is always the part after the last one.
If we locate a period, then we know we have a potential extension. At this point, I apply the LCase function so everything is in lowercase, which makes comparing file extensions much simpler and avoids any issues with case sensitivity. The Mid function is then used to extract the extension from the file name, starting just after the period to omit it and going to the end of the string. I mentioned in the video that I recently discovered you can use the Mid function without specifying the third parameter to grab everything from a certain point to the end of a string. This saves us from having to use extra functions like Right and calculating string lengths, which can get cumbersome.
If the period is not found, this means the file lacks an extension entirely. This is something we need to catch, as attempting to process files without extensions could lead to errors. For such cases, I use a message box to alert the user that the file's extension could not be determined, displaying the file name and immediately exiting the procedure. It is critical to flag and halt processing for these errors, so they can be addressed before proceeding.
Once we have the extension, I display it alongside each file name so we can see which extensions have been detected. At this stage, I recommend thorough commenting in your code. Mark where you determine file extensions and handle cases for files with and without extensions. As I remark in the video, good commenting habits make your code much more understandable when you revisit it in the future.
Now, we only want to process files if their extensions are among the valid image types we are supporting. To do this, I employ a Select Case statement, which is more concise and readable than stacking multiple If-ElseIf commands. In this structure, you can list all valid image formats you want to accept, like BMP, GIF, JPEG, JPG, and PNG. Any other type will fall to the Case Else, and you can display a status message indicating that an unknown file type was encountered. This lets users know if, for example, a readme.txt file somehow made its way into the import folder.
Once we are processing only valid image files, we can clean up our status messages. Rather than stating simply "valid extension," I move towards a message indicating that the file is currently being imported, and I increment our import counter accordingly.
Creating new file names for these images is crucial, especially to avoid overwriting existing files with the same name. My preferred approach is to use a timestamp that includes the current year, month, day, hour, minute, and second, and then append the current value of the counter variable at the end. This guarantees each file name is unique, even if multiple images are imported within the same second. For added uniqueness, you could append a random number if desired, but the timestamp and counter approach is typically sufficient.
The next step is to append the file's extension to the end of this new name, ensuring it remains recognizable as a valid image type. After defining the new file name, we proceed to copy the file from the import path into the images folder, using the newly generated unique name.
Testing this process is important. After copying, open your images folder and verify that all files have been renamed appropriately and maintain their respective extensions. At this stage, I generally delete any unnecessary files left in the import folder, though I may save one or two for further testing.
In the next segment, after confirming that the file has been copied successfully, I will add error handling to ensure the copy was successful before deleting the original. Additionally, I will check that the delete operation works as intended. Finally, after these file management steps, we will add a new record to the database to reflect the imported image. All of this and more will be covered in the next video.
This installment in the Load Multiple Images series was released on Friday, August 23, 2024. The next part will become available on Monday, August 26. Members can access it immediately, but all others can look forward to it on Monday.
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 Finding the dot position in a file name
Using InStrRev to locate the file extension
Extracting file extension with Mid
Converting file extensions to lowercase with LCase
Handling files without an extension
Displaying error with MsgBox for missing extensions
Showing extracted file extensions during the loop
Testing error handling with files missing extensions
Adding comments in code for clarity
Filtering files for valid image extensions
Using Select Case to check image extensions
Processing only valid image files
Handling unknown or invalid file types
Generating unique file names using date and time
Appending a counter to generated file names
Constructing file names with proper extensions
Copying images to a destination folder with new names |
