Extract OLE Image 3
By Richard Rost
2 years ago
Extracting OLE Image from MS Access Field, Part 3 In this Microsoft Access tutorial, I will show you how to automate OLE image extraction and deal with common pitfalls during the process, saving images efficiently to a folder from your database. This is part 3 of 3. MembersIn the extended cut, I will show you how to create a loop that processes all records with an image, extracting and saving each one, and then updating the customer table with the new file name, allowing you to delete the OLE object field and recover database space. 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, extract OLE image, Access table, VBA editor, SendKeys function, save image, JPEG format, file menu navigation, debug compile, code customization, DoEvents subroutine, loop through records, image extraction loop, image file naming
Intro In this video, we continue working with Microsoft Access to extract OLE images from your database tables, automate saving these images from Paint as JPEG files, and improve the reliability of the process using VBA. You'll see how to send keystrokes programmatically, introduce pauses and DoEvents for better timing, customize the SendKeys routine for stability, and debug and test your code. We also talk about adjusting delays based on your system's speed and walk through saving extracted image file paths to your database. This is part 3.Transcript This is part three of my series on extracting an OLE image from a field in your Access table. Let's continue. If you haven't watched parts one and two, what are you doing? Go watch those first. Get back here when you're done. All right. So, here's where we're at after part two. We got Spock in here. We hit extract image. It opens up Paint and then after a second it pastes them in there. Now, we have to save this image to our folder. So let's close this. See, it's going to ask if you want to save, just say no for now.
Let's continue with the automation. All right, I always keep a little button up here on my quick launch toolbar to go back to my VBA editor. I got a video on that somewhere.
All right, so we've pasted in the image. We shouldn't have to wait after that, but I like to. Just a tiny bit, so maybe sleep 100, that's a tenth of a second, like 100 milliseconds. Just give it a chance to breathe. All right, so now we're going to save the file and we're going to do that by first hitting Alt F to go to the file menu. There is no quick shortcut for "Save As." Yeah, in some applications Ctrl Shift S, I do believe the Paint supports F12, but I don't like using that. I like to issues all that uh... is it? I don't, just I've had experiences trying to use function keys with SendKeys, especially if what we do you got a laptop. A lot of people with laptops, you know you got those special function keys that are laptop-related, so this just, this works all the time. But we're gonna send keys Alt+Fn, Alt is that guy, the percent sign, and then a lowercase 'f'. All right, don't forget to keep it lowercase. If you make it capital, you're going to get Alt Shift F.
All right, now we got to go down five times. All right, send keys. What's the down arrow? It's down, like that. We got to do that five times. Now, if you want, you can make a loop, but I'm just going to do it for simplicity's sake. One, two, three, four, five.
Okay, now we got to go right once which is "right", and then we have to go down one more time to JPEG. Alright, and you can comment all this if you want to. This is "right the file on the menu go down, down, down, right, right, right", and now we're on JPEG format. Okay, now we're going to wait now. Sending those arrow keys, usually you don't need a delay in there because it's usually pretty quick to respond to those, but when you are opening up a dialog box, sometimes that takes a bit. So we're going to sleep half a second. And again, I've done this a dozen times. I've tested it and this is what works on my system. You might need to add more delays. If you've got a really slow system, you might need a delay between each one of these keystrokes here.
Okay? Now, once we hit that JPEG and wait, it's going to open up the Save As dialog. Now, here's where we have to send keys the file name. Okay? And you get to watch this on your system. If you're only getting a piece of that file name, like the last half of it, put a longer delay there before it opens because it's taking its time to open and you're not waiting long enough to send the full keys. In fact, you know what? I think I'm going to wait a full second just to make sure.
Now we've got the Save As dialog, so we're going to send keys our file name. Send keys file name. That'll send this whole thing. And again, you might need to break this up and pause and all that stuff. I'm going to talk about that more in the extended cut. I'm going to break this up into actual keystrokes. But this will work 99% of the time.
Okay? Now here's where you want to press enter again. So we're going to send keys "enter" after you put the file name in there. And we're going to sleep again. This time I'm going to sleep a good two seconds. Because this is where it's saving the file and we want to wait before we close Paint. How do we close Paint? Send keys Alt F4. So that's going to be Alt is that guy and then F4. Yeah, I know I don't like function keys, but F4 tends to work. I believe it's on the Alt F menu too. Let me see. Yeah, if you've got a laptop or whatever and it's not working, you can go Alt F, and it comes down to exit. And keep in mind anytime they make an update to this program it's going to change. That's why I'm saying this is kind of a one-and-done thing. You're going to customize this for your system, your version of Paint, okay? And then when you're done, you're probably never going to have to see this code again. But if you've got 5,000 images or 2,000, right, in your database and you want to get them out once, this is going to work just once for you. Once you customize it, then you're done with it.
All right, debug compile. Let's come back out here. I always like to close my forms and reopen them before I run any code. That's just me.
All right, let's try extract and get you ready. Click and let's see what happens. All right, it's doing something here. What's going on? There's that. You know okay see all right so we need a bit more of a delay. If you see weird things happen like this, it tried doing too much. We don't have as much delay as we need between these keystrokes. So here's what I'm going to do. Here's what I like to do. Instead of using raw SendKeys, I'm going to make my own SendKeys so they don't all bunch up on each other. Here's what I like to do. I'm going to come down here. I'm going to create my own subroutine.
All right, so private sub, come on, I can't type today, mySendKeys. OK, we're going to send it what we're going to send, so 'S' as a string. And in here, we're going to SendKeys whatever was sent in, comma true, make sure it waits. We're going to put a brief delay in here between each key. So I'm going to put a sleep, maybe 100 milliseconds is good, and then a DoEvents that frees up the processor to keep processing things. I've got a whole separate video on DoEvents if you want to go watch that.
So now instead of using SendKeys everywhere, we're going to use mySendKeys. I have to do this once in a while. So starting from the top, we're going to replace every SendKeys with mySendKeys. And if you think you need those longer delays in there, you can leave those there too. You can't go too wrong with having long delays. It's just going to take longer for the process to run. But if you want to set this and forget it, if you want to just click go and then go have lunch and come back, which is how long you're gonna probably have to wait for it to do 2,000 images, that's what you want. You want it to be sure of itself and wait between each keystroke.
Alright, so now mySendKeys is going to send that key. Okay, it's gonna put a brief pause in there, DoEvents, free up the processor, and then return back where it was. This usually has a better success of working. Let's debug compile. Come back out here. Let's close you down. Open it back up again. Let's give it another try. Click. Let's see. You should see it actually happen. Yeah, see there. We can actually see it working now. All right. That's much, much better.
All right. So it seems like it worked. Let's go check our folder. Where's the folder now? There it is. Ah, there's PIC1. And look, it's an image. It extracted it perfectly. I think I'm going to put a little bit more of a delay in there again this, you have to adjust the space in the speedy computer. Michael three hundred cities here. I would rather, like I said there on the side of waiting. Too all right, extracted good good good to see if they keep slower with the keystrokes. Now, and with the report.
All right. And let's see, where's my folder? There it is.
All right, let's try another one. OK, ready for Kirk. Go.
Okay, there's Kirk. Save as. Good. Send those keys there. Good. Probably put less of a delay in the save. All right. Maybe throw a beep in here too, so we know when each one's done. All right, beep. Let's go check the folder. There we go, there's Kirk. So we're on the way now to be able to extract all of these.
Now the only piece that's missing is a loop. We want a loop to start from here and then when this one's done, go to the next one. When this one's done, go to the next one and go through all the images, go through all the records that actually have a picture in it. I've shown you 99% of what you need to do to be able to do that. We're going to finish it in the Extended Cut for the members. Silver members and up get access to all of my Extended Cut videos. What we're going to do is make a loop. We're going to start with record one. We're going to just go down all the records. If it finds an image, it's going to extract it and then save it and then move on to the next one and continue until we're done. And then when you go to lunch and you come back, all your images will be in a folder and, oh, I forgot one thing, and we'll save that file name in the customer table in a field so that now we've extracted the image. Now we've got the file name where that image is in the table, we can point to it like a normal picture, like I show in the images video, and then you can delete that OLE object field. You don't need it anymore. Right? And then compact your database, and you can get all your space back. It's going to be a wonderful day. Cats will be marrying dogs. It'll be raining fire from the sky. But at least our database will work. So, there you go. If you like learning with me, if you enjoy this stuff, if you want to learn more, I got tons and tons of developer lessons. I'm actually working on developer 45 right now. Well, not right now, but in the middle of it, kind of. But come to my website, check it out. Tons of stuff. I teach stuff in the order you should learn it. Unlike in my "TechHelp" videos where I kind of jump around because "TechHelp" videos are really more just to solve a specific problem, whereas in my developer lessons we teach things in the right order. We're going to learn a little bit about this, a little bit about that, a little bit about this, and every lesson builds on the one before it. So come to my website, check it out, there's a link right there. But that's it. That's part three, that's the end of this series for now. I always, sometimes I come back and later I add part four. 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 next time.
TOPICS:
Extracting OLE images from Access
Saving images from Paint
Using SendKeys with VBA
Creating a sleep delay in VBA
Navigating menus programmatically
Saving files with specific format (JPEG)
Creating custom SendKeys subroutine
Ensuring VBA waits for processing
Adjusting delays for system speed
Debugging and testing code
Looping through records in Access
Storing image file paths in Access database
Deleting unnecessary OLE fields
Compacting Access database after changes
Implementing and customizing automation codeQuiz Q1. What application is the tutorial using to extract an OLE image from a field in an Access table?
A. Microsoft Word
B. Microsoft Paint
C. Adobe Photoshop
D. Microsoft Excel
Q2. In the video, what keyboard shortcut does the presenter use to open the VBA editor?
A. Ctrl + V
B. Alt + F11
C. Alt + F
D. F12
Q3. What keystroke does the presenter use to open the File menu in Paint?
A. Alt + F
B. Ctrl + S
C. F12
D. Ctrl + Shift + S
Q4. How does the presenter suggest sending the "down" arrow key input using VBA's SendKeys function?
A. "{DOWN}"
B. "DOWN"
C. "{ARROWDOWN}"
D. "ARROWDOWN"
Q5. What function key does the presenter reluctantly agree works with SendKeys in Microsoft Paint, despite their general aversion to using function keys?
A. F2
B. F4
C. F8
D. F12
Q6. If unexpected behavior occurs during execution of the VBA automation, such as incomplete file name input, what does the presenter suggest might be the solution?
A. Increasing the delay before sending keystrokes
B. Decreasing the delay before sending keystrokes
C. Removing all delay
D. Changing the VBA editor theme
Q7. What does the presenter create to replace the standard VBA SendKeys command and to add a delay between each key sent?
A. mySendFunction
B. mySendKeys
C. customSendKeys
D. quickSendKeys
Q8. How does the presenter suggest closing the Paint application through VBA's SendKeys function?
A. SendKeys "Esc"
B. SendKeys "Alt + F4"
C. SendKeys "Ctrl + W"
D. SendKeys "F12"
Q9. What is the final step of the process before compacting the database, as mentioned at the end of the video?
A. Deleting the VBA code
B. Increasing the database size
C. Saving the extracted image file name in the customer table
D. Changing the OLE object field to a text field
Q10. According to the presenter, who can access the Extended Cut videos where the process of automation will be completed?
A. Silver members and up
B. Anyone who subscribed to the channel
C. Gold members exclusively
D. Developers with over five years of experience
Answers: 1-B; 2-B; 3-A; 4-B; 5-B; 6-A; 7-B; 8-B; 9-C; 10-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 continues the series on extracting OLE images from fields in your Access database tables. If you have not already watched parts one and two, I recommend pausing here and reviewing those first, since each part builds on the last.
At the end of part two, we successfully managed to get an image for Spock loaded into Paint using an automated process from Access. Now, the goal is to save that image directly to a folder on your computer. When you close Paint after pasting in an image, it will typically prompt you to save, but for this demonstration, let's move directly into automating the saving process.
In my workflow, I keep a shortcut on the quick launch toolbar to return instantly to the VBA editor. It's a small detail, but it does speed up development. Now, after pasting the image into Paint, I like to include a very brief pause or delay - about 100 milliseconds - just to ensure everything is in sync before moving to the next step. Delays like this are not strictly required, but they can help maintain stability, especially on slower machines.
The next task is to automate the "Save As" process using keyboard shortcuts. While Paint supports F12 for "Save As", I've found that using Alt followed by traditional menu navigation keystrokes is more reliable across different systems. Function keys can cause issues on laptops due to special functions assigned by manufacturers, so to avoid complications, I send Alt+F via VBA's SendKeys function, using the percent sign and a lowercase "f" to represent Alt+F. Be careful with the case, as capital letters will add a Shift key into the mix.
Navigating the File menu in Paint to select the JPEG format requires a series of down and right arrow keystrokes. Specifically, you would need to send five down arrows, a right arrow, and then another down arrow to highlight JPEG. While you could loop through these steps, manually sending each keystroke works just as well for this purpose. Once you're at the JPEG format, it's wise to insert a longer delay - about half a second - before the Save As dialog appears. Depending on the speed of your system, you may need to adjust this timing. If the filename appears incomplete, simply increase the delay before entering the filename.
With the dialog open, it's time to send the desired filename, followed by pressing Enter to begin saving. Another generous delay, such as two seconds, ensures the process has time to finish before closing Paint. Closing Paint can be accomplished with Alt+F4. Although I usually avoid using function keys, Alt+F4 is generally reliable and is also present within the File menu as an exit shortcut. Remember, any future updates to Paint could alter these menu actions, so treat this as a one-time automation you adapt to your specific setup. Once you've extracted all your images, you likely won't need this process again.
Before testing your code, it's a good habit to close and reopen any open forms in Access to make sure you're starting fresh. Once I tried running the automation, I noticed unexpected behavior - most often because of insufficient delays between keystrokes. If the process falls apart, increase your delays until everything works smoothly. Relying on the raw SendKeys method can sometimes queue up keystrokes too quickly for Paint to process. To address this, I recommend creating a custom subroutine that sends keystrokes one at a time with a built-in delay and a call to DoEvents. DoEvents allows Windows to process any background events and generally improves reliability with SendKeys-based automation.
Once you have your custom SendKeys subroutine, swap out every instance of SendKeys in your code. If you anticipate needing longer delays at specific steps, you can include them as well. It's better for the process to run slowly and reliably than to break and require intervention when handling hundreds or thousands of images.
After making those adjustments, compile and test your project again. You should now observe Paint operating at a measured, predictable pace, allowing each keystroke to complete before moving to the next. With the correct delays, the save process completes as expected. Confirm by checking the designated folder to find the extracted image files, such as PIC1 for Spock and another for Kirk.
At this stage, automation is working for single images. The next logical step is to loop through all records in your Access table that contain images. For each record, the code should check for an image, extract it, save it to the designated folder, and continue until reaching the last record. With this loop in place, you can leave the process running unattended while it extracts as many as needed.
In the Extended Cut for members, I cover how to implement this looping logic, ensuring only records containing images are processed. In addition, I demonstrate how to store the filename in the customer table as a new field, allowing you to access the image as a standard external picture file. This also allows you to remove the OLE object field entirely, reclaim space, and improve overall database performance. Once that's done, you can compact your database for final optimization.
As a final note, my developer lessons go far beyond TechHelp videos, covering every topic in a logical order, building from the basics up to advanced techniques. If you're looking to deepen your Access skills, you'll find everything you need on my website.
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 Extracting OLE images from Access tables
Automating Save As process in Paint
Using SendKeys in VBA for menu navigation
Adding sleep delays with VBA for reliability
Navigating Paint menus via keyboard shortcuts
Selecting JPEG format in Save As dialog
Sending file names to Save As dialog with VBA
Creating a custom SendKeys subroutine
Ensuring proper processing with DoEvents
Adjusting VBA delays to match system speed
Testing and debugging the extraction process
Verifying saved images in the target folder
Adding auditory feedback with the Beep function |
