Load Multiple Images 4
By Richard Rost
2 years ago
Auto-Load Multiple Product Images in MS Access Part 4 In this Microsoft Access tutorial, I will show you how to handle file copy success verification, delete original files with error handling, and log records into the contact table with record sets. This is part 4. MembersIn the extended cut, we will cover how to use the standard Windows file dialog to select multiple files and import them directly without needing a dedicated import folder. I will show you how to specify file types and handle the browsing process for importing photos. 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!
PrerequisitesLinksRecommended Courses
Keywords TechHelp Access, Load multiple images in Microsoft Access, Microsoft Access image import, image automation in Access, managing images in Access database, Access VBA file operations, copying files in Microsoft Access, Access VBA file copy, Access VBA kill command, VBA error handling in Access, Access Visual Basic tutorial
Intro In this video, we continue with part 4 of the Load Multiple Images series in Microsoft Access. I'll show you how to implement error handling in Visual Basic when copying files, create error response locations in your code, and properly clean up after failures. We'll also cover deleting the original files with error checking, updating your contact table by adding records using record sets, and refreshing and reordering your contact list so new entries appear at the top. A step-by-step example demonstrates the process, including how to handle multiple users and verify your results. This is part 4.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost.
Today is part 4 of my Load Multiple Images series. If you haven't watched parts 1 through 3 yet, what are you doing here? Go watch those first and then come on back.
Alright, at the end of part 3, we were able to copy the files from the import folder to the save images folder. But we want to make sure that this file copy operation was a success. Now, there are a couple of ways you can check. You can simply check to see with the dir command if that file is there, but I don't like doing that; it messes up things with the dir, there are just a lot of reasons why.
But what we're going to do is we're going to just simply check to make sure that this guy finished without an error. Unfortunately, file copy itself doesn't return an error level like many functions do, but you can use basic error handling to check and see if an error was raised in VB. I've got another video about basic error handling; you can go watch this Fast Tips video. I also cover a lot with error handling in my full developer course. I'll put links down below. But today I'm going to show you the basics of what you need to do.
So, before that, we're going to say "on error go to copy failed." That means if this guy throws an error, we're going to jump down to a location down here called copy failed. We'll set that up in a second. But also, don't forget, right after that, turn error handling off "on error go to zero." Alright, because if any other errors happen after that, I want to know what they are. This one I expect may throw an error.
So, now down here at the bottom, let's copy this, copy failed. Down here at the very bottom, I'm going to put a location called copy failed with a colon. That's a special location you can go to; you can jump to that location. And here we're going to say status error copying file, and then we'll give them the file name. I also like to give them the error.description. This is the error from VB that will actually, literally tell you what the problem is: right out of disk space, or something's corrupt, or blah, blah, blah, whatever.
Now, if this happens, I want to exit the subroutine, but I always like to issue a resume and clear the error as well. So, we're going to say resume, do cleanup, we'll call it, and we're going to make a location right up here called do cleanup, just like this. And do cleanup will be all the stuff that we have to clean up when we're done. You'll see there's going to be some other stuff we're going to add in here, okay, but on top of that, we're going to beep, okay, we're going to error.clear; that'll clear any errors that might be flagged in memory, okay, and then we're going to exit sub.
So, normally if an error is not raised, it's still going to finish and come through here. It's going to beep so we know we're done, clear any error messages, which there won't be, but that's okay, and then exit the subroutine before it gets to this stuff down here. Okay, that's how basic error handling works in Visual Basic. Is it the best? No, but it works. It'll get the job done. There are other languages that are a lot cleaner with the way they handle debugging and stuff, but this is fine, this is fine, I'm used to it.
Now, it's very hard to replicate an error with the file copy, and I'm not going to like fill up my hard drive and do some other wacky stuff. So, just trust me, this is how it works, trust me, okay, okay, if there's an error, you'll get this. Now, after we've copied it, and we've made sure that everything is fine, we're going to delete the original file, and we're going to do something very similar to what we just did with the error handling a minute ago. I want to know if this delete operation fails.
So, it's going to look like this, "on error, go to kill failed," or "delete failed if you don't like using the word kill," okay, and this is going to be kill because kill is the command to delete a file, kill, alright, import path, and file name. So, we're going to delete the original file, and then turn off error handling, "on error, go to zero." And guess what, down here, same thing, in fact, I'm just going to copy this, copy, and paste, and we'll call this kill failed, error deleting file, and then the same thing, the rest of it's exactly the same.
Error deleting file, and then go to clean up, and it'll exit out. Okay, that's your basic error handling right there. Now, when we get done with all of that, and we exit the while loop down here, we're going to tell the user how many files were copied, right, that's just polite to do, okay, so we're going to say tell user how many files copied, or imported, imported, right, we're going to say if counter is greater than zero, then status, success, and we're going to say counter, images, imported, just like that, otherwise counter zero, it didn't find any valid files to import, nothing happened, so status, no images, found, right, end if, and then it'll hit our do clean up.
Now, with all of this having been done, we still have not added a record to the contact table to put this guy in the database. We literally just went through and copied the files to the images folder, but we didn't log them in the database, right, so now we're going to make a record to put this in the contact table, and this is where record sets come in handy. So, up top here, we need one more variable RS as a record set, and at the very beginning before the while loop, I'm going to say set RS equals current DB dot open record set contact T.
And if you're not familiar with record sets, go watch the record sets video that I told you to watch before video one, okay. Now, at whatever point you want to add the record, you could do it after the delete. I like to do it right here after the copy operation, but you could put it really anywhere inside this loop once you know you have a valid file, okay. I'm going to put it here, add to database, and this is, and you could do this with an SQL statement if you really want to. I like using record sets, so that's my personal choice. You could use an import or an append query. RS add new, you'll see how easy this is, add a new record.
RS customer ID equals customer ID, that's the customer ID on the contact form, that's this guy, where was it? That's this little hidden guy, okay, and that's part of the contact table, so you know which customer it is, okay. Alright, what field is next? The date, RS contact date equals right now, I believe that's defaulted in the table, but it never hurts to add it and change it manually, okay. RS description, this is the short text that goes with it; I'm just going to put in here image, you could put in whatever you want, right, that goes in the text field.
And then here's the biggie, RS my image equals new file name. Okay, this new file name we just created that we copied to the images folder, that's what we want to put in there, okay. RS update saves the record, and now when you're all done with your loop, we got to remember to close the record set, and that also is going to go in this do cleanup section. So, even if we have an error, it's going to come back up here and close the record set. RS close, whether it's successful or not, and what I'm also going to do is I'm going to reorder the contact list and requery it, because if you look at this thing, right, when it opens up, it's in no particular order. I think this is sorted in straight chronological order ascending; I want this to be in chronological order descending with the new stuff at the top, okay.
And just because the user can come in here and change and filter stuff, I'm going to force the order by in here. So, we're going to say, we're going to say me.order by equals contact date descending, so it's going to sort in reverse order, so the new stuff's up at the top, we've got to make sure order by is on, so me.order by on equals true to make sure you're sorting, right, and then me.requery, so requery the list so everything is fresh and updated. And that should do it, there is your code right there, and that's not terribly difficult, this isn't rocket science, right, but you just got to walk through it step by step, and examples like this will make you a better programmer if you do it yourself.
Alright, so let's give it a try, here we go, ready, and import, boom, there we go, there's all the images, er, er, er, Koi, Kirk, there's Mr. Data making it so, see, and they're all added in here. And now if this was your product database, now you could use all these images and make your product catalog, or your gift cards, or whatever you want to use these images for. And let's go open the import folder, and they're all gone from the import folder, that readme.txt was left alone because it's not a valid image. Go back to here, go to the images folder, and there they are.
Alright, want to import something from someone else? Well, let's close this, let's close this, let's save changes, yes, of course, let's go to somebody else, let's go to Malcolm Reynolds, and go to his contacts. Let me move this back over here where it belongs. I tend to center stuff when I'm working on it, so I got a bunch of images I want to import for Malcolm. I'm going to hit open the import folder, I'm going to drop those images in here from wherever you got them stored now. I'm going to delete that readme.txt first. There we go, let me go grab my Malcolm images, click and drag, drop them there.
There we go, now they're in the import folder. Now I can close this, come back here, hit import, boom, there they are, they're in the database, they're copied to the images folder. Let's check, images, and where are they, there they are, right, it's a beautiful thing.
Now I happen to mention in the beginning of video one that there is a way to use the standard Windows dialog pick file thingy to hit your import photos, and then browse to wherever your photos are, let's say here, right, and then pick these, click, and then hold down the control key, click, click, click, and then hit okay, and then that would handle it without having to go through using that import folder. And I will cover that in the extended cut for the members, just like we did the browse button in the images folder, the original one way, way back in the images video. We'll do the same thing today, for the members, we're going to do multiple files with the file dialog. You can specify the file types and all that; before you even browse when it opens it up, it'll only show you JPEGs and bitmaps and whatever else you want, a lot more advanced, and it doesn't require a custom import folder.
Silver members and up get access to all of my extended cut videos, not just this one, all of them. There are hundreds of them by now. Gold members can download these databases that I make in the TechHelp videos, and you get access to my code vault and everybody gets free classes and a whole bunch more. So what are you waiting for? Join today.
But that's going to do it. That's going to be your TechHelp video for today. That's the end of this series for now. I hope you learned something. Live long and prosper my friends, I'll see you next time.
I'm going to 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, the name, the video's up here, there's a little "show more" down there right 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. And YouTube no longer sends out email notifications when new videos are posted like 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 and 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 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.
And 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 comments 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 on the list for me to read them. And if I like your question, you have 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 it. I hope you learned something today. Live long and prosper, my friends. I'll see you again soon.
TOPICS:
Copying files from import folder to save images folder
Checking file copy operation for success
Error handling in Visual Basic
Using "On Error GoTo" for error handling
Creating a "copy failed" location in VB code
Setting file copy error messages with error descriptions
Exiting subroutine with error handling
Adding and clearing errors using "Error.Clear"
Deleting original files after copying
Using "On Error GoTo" for file deletion
Creating a "kill failed" location in VB code
Logging error messages for file delete operation
Notifying user of completed file operations
Adding records to contact table with record sets
Using OpenRecordSet method for accessing database
Inserting records into database after file copy
Using record sets to add new records
Updating and closing record sets in the cleanup section
Reordering and requerying contact list in database
Sorting records by descending order on contact date
Using Me.Requery to refresh form data
Demonstrating the full process with an example
Handling image imports for different users
Deleting specific files from import folder
Using standard Windows file dialog for importing files
Advanced file dialog usage for multiple file types
Membership benefits and extended cut video topics
COMMERCIAL:
In today's video, we're diving into part 4 of our Load Multiple Images series. We'll start by ensuring your file copy operations work smoothly without errors. I'll guide you on using basic error handling in Visual Basic to flag any issues during the copy process and clean up properly. Then, we'll move on to deleting the original files and handling potential errors there too. Once errors are handled, I'll show you how to add records to your contact table using record sets, ensuring your images are logged correctly in the database. Finally, we'll requery and sort our list to keep everything organized. 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 method does Richard suggest using to handle errors during the file copy operation?
A. Checking for the file using the dir command
B. Advanced debugging tools
C. Basic error handling in VB
D. Manual verification after file copy
Q2. What should be written right after "on error go to copy failed" to turn off error handling?
A. on error go to end
B. on error reset
C. on error continue
D. on error go to zero
Q3. What action is recommended to be taken after confirming a file copy is successful?
A. Deleting the output file
B. Deleting the original file
C. Renaming the original file
D. Moving the output file to another directory
Q4. What Visual Basic command is used to delete a file?
A. Remove
B. Delete
C. Clear
D. Kill
Q5. Where does Richard recommend placing the record set variable initialization in the code?
A. Inside the while loop
B. Before the while loop begins
C. After deleting the original file
D. At the very end of the code
Q6. How does Richard suggest handling the order of entries in the contact list after importing images?
A. Reordering in ascending order by contact date
B. Manually reordering entries afterwards
C. Using me.order by to sort descending by contact date
D. Sorting using an SQL statement
Q7. What should be done if an error occurs during the delete operation?
A. Retry the delete operation immediately
B. Skip the file and continue
C. Jump to a specific location and handle the error
D. Log the error and proceed without alerting the user
Q8. After successfully processing the files, what does Richard suggest telling the user?
A. The number of files copied or imported
B. The names of the files handled
C. Any errors encountered
D. The total size of the files copied
Q9. What's the main advantage of using record sets according to Richard?
A. It's simpler than writing SQL statements
B. Provides better error handling
C. Automatically includes all necessary data fields
D. Reduces the amount of code needed
Q10. Why does Richard prefer using basic error handling in Visual Basic?
A. It's the only available error handling method
B. It's more efficient than other error handling methods
C. Despite its simplicity, it gets the job done
D. It's fully automated and easier to implement
Answers: 1-C; 2-D; 3-B; 4-D; 5-B; 6-C; 7-C; 8-A; 9-A; 10-C
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 focuses on part 4 of the Load Multiple Images series for Microsoft Access. If you have not seen parts 1 through 3 yet, I strongly recommend starting with those to provide important context for what we cover here today.
At the conclusion of part 3, we had set up the process to copy files from the import folder into the designated save images folder. Now it is essential to make sure that the file copy operation both completes and succeeds. There are a number of approaches to checking for a successful copy. While you could use the Dir command to check if the file exists, I do not find this method ideal for several reasons.
Instead, the approach I recommend is to use error handling to detect any problems that occur during file operations. Although the file copy command in VBA does not return a status indicator, you can catch errors using simple error handling techniques. If you are new to error handling, I cover the basics in a separate Fast Tips video as well as more advanced methods in my developer course, both of which you will find linked on my site. For this lesson, I will walk you through the basics required for our process.
Before attempting to copy a file, we use the On Error GoTo statement, which instructs Access to jump to a labeled section in the event of an error. After the file copy command, we immediately return error handling to normal with On Error GoTo 0, because we only want to trap the expected error and have Access handle any unexpected ones.
At the bottom of the routine, I establish a labeled location for handling file copy failures. In this section, we inform the user that there was an error copying the file and include the file name and a description of the error provided by VBA. This can tell you exactly what went wrong, such as a full drive or a corrupt file. After reporting the error, the routine exits, but before doing so, we clear the error and perform any required cleanup.
To keep things tidy, I also create a do cleanup section in my code. Here, any additional necessary cleanup can be performed, and again, we clear any remaining error states before fully exiting the subroutine.
Error handling should also be added to the file deletion step after the copy operation. If something goes wrong when attempting to delete the original file, this too should be detected and handled gracefully. The structure is very similar: use On Error GoTo to redirect to a kill failed section if an error occurs, provide a clear message with details for the user, and make sure cleanup is performed and the process ends safely.
After the entire process is complete, I always like to give the user feedback—letting them know how many files were successfully copied or, if none were imported, making sure they are aware of that outcome as well. It is a small but important step for user experience.
Up to this point, while we have copied the image files into the images folder, nothing has yet been added to the contacts table in our database to track these files. This is where record sets come into play. At the start of the code, I declare a variable to hold a record set. Before beginning the loop that processes each image, I open a connection to the contacts table using the OpenRecordSet method.
Whenever you are ready to add a record, most commonly after a successful copy and delete operation, use this record set to add a new entry to the contacts table. I like using record sets because they are straightforward and give you easy access to each field. Set the customer ID to link the image to the correct contact, set fields like the contact date, add a description as needed, and assign the new file name to the image field. After updating the new record, do not forget to close the record set when finished. This should also be done in the cleanup section to ensure it is always handled regardless of any errors.
Additionally, for organizational purposes, it is helpful to sort the contact list so that the newest items appear first—chronologically descending by contact date. I force the order using the OrderBy property of the form, make sure that property is enabled, and finally issue a requery to refresh the display so the user immediately sees the new entries in the desired order.
Once all of this has been implemented, the routine allows for a smooth workflow. For example, you can import images for one user, verify that files have been added to the database and moved to the images folder as required, and check that no image files remain in the import folder except for non-image files which are ignored. The same procedure works for other users as well. Just update the contact record as needed and run the import for their set of images.
I mentioned in the first video of this series that it is possible to use the Windows file dialog to browse for photos, select multiple files at once, and import them directly—without the need for an intermediate import folder. This more advanced, user-friendly solution is covered in the Extended Cut of today's lesson. If you are interested in handling multiple files with the file dialog and want to set file type filters for your selections, I show you exactly how to set that up, along with several other useful techniques.
As a reminder, Silver members and above get access to all of my Extended Cut videos, not just this series, but hundreds of others. Gold members can also download the sample databases built in these tutorials and take advantage of my code vault. There are many other benefits to membership, which you can read more about on my site.
That concludes this tutorial and the Load Multiple Images series for now. If you would like to see the full video with step-by-step instructions and demonstrations of everything discussed here, visit my website at the link below.
Live long and prosper, my friends.Topic List Copying files from import folder to save images folder
Checking for successful file copy operations
Implementing error handling for file copy in VBA
Using On Error GoTo for file operations
Creating and handling a copy failed error location
Displaying error descriptions to users after errors
Exiting subroutines properly following errors
Clearing errors in VBA using Error.Clear
Deleting original files after copying
Implementing error handling for file deletion
Creating and handling a kill failed error location
Logging errors for failed delete operations
Notifying users how many files were imported
Adding records to a contact table with record sets
Opening a record set with OpenRecordSet
Adding new records to the database after copying
Assigning fields in a record set (customer ID, date, description, image file name)
Updating and closing record sets in VBA
Including record set closure in clean up processes
Reordering and requerying the contact list after imports
Sorting records by contact date descending
Refreshing form data using Me.Requery
Demonstrating a full import and database update process
Importing images for multiple users by changing context
Deleting non-image files from the import folder |
