Extract OLE Image
By Richard Rost
2 years ago
Extracting OLE Image from Microsoft Access Field
In this Microsoft Access tutorial, I will show you how to extract an OLE image from a field to convert and save your embedded pictures as JPEG or BMP files using VBA, even when facing the 'format not supported' error.
David from Queensland, Australia (a Silver Member) asks: I've been following your YouTube tutorials and learned that storing images directly in a database isn't efficient. As a coin collector, I've accumulated over 2,000 images in my database. I tried to extract these images into .jpg or .bmp formats using a VBA script, however, I can't view the images produced by the script; they appear to be the correct size but the format isn't supported when I attempt to open them. Could you advise on how to fix this issue to avoid manually saving each image?
Prerequisites
Links
Recommended Courses
Up Next...
Keywords
TechHelp Access, extract OLE images, save images Access database, VBA script image extraction, Access database VBA, OLE object field, JPEG BMP conversion, extract OLE objects VBA, Microsoft Access tutorial, OLE field images, converting OLE to JPG, extract images OLE object field, VBA code image extraction
Intro In this video, I will show you how to extract images stored in OLE object fields in Microsoft Access and discuss some of the challenges with using VBA to automate this process. We will talk about why storing images directly in your database is inefficient, how Access packages OLE objects, and why many extraction scripts fail to produce usable image files. I will demonstrate a practical workaround using SendKeys and the Sleep function in VBA to help get your images out of Access, as well as cover best practices for handling images and setting up OLE fields if you still need them.Transcript In today's video, I'm going to show you how to extract an OLE image out of an OLE object field in Microsoft Access. Today's question comes from David in Queensland, Australia, one of my Silver members. David says, "I've been following your YouTube tutorials and learned that storing images directly in a database isn't efficient." That is correct. As a coin collector, I've accumulated over 2,000 images in my database. I try to extract these images into JPEG or BMP formats using a VBA script. However, I can't view the images produced by the script. They appear to be the correct size, but the format isn't supported when I attempt to open them. Could you advise on how to fix this issue to avoid manually saving each image?
Well, David, I will say that I've played with this issue on and off for years now. And I've spent hours Googling it and trying different VBA programs I found online. There are a bunch of different pieces of VBA code that you could try in your database. And they will extract something. They'll create a file, but when you try to open that file, you get an "image not supported" error with whatever you try to use to open it. The reason why I believe this happens is that when Access stores something, whether it's an image or a file, in an OLE object, it packages it; it bundles it; it puts a header and possibly a footer on it. And unless you know how to strip that off to get to the raw image data – which I've tried doing and I can't seem to do – the file that you create is not going to be in the right format.
Now, if any of you out there have a solution that's straight VB code, let me know because I've been looking for it for a while myself. What I am going to show you today is a technique that will work, and it's good enough for if you've built your database already and you've been, you know, let's say you inherited it from someone else or you started it years ago and you didn't know any different, and you've got a bunch of images in your OLE objects in your database, and you want to pull them out. This is good enough to do like a one-time job to fix it, okay? I wouldn't recommend using this on a continuing basis, but at least this will help you get your images out of your database without having to do them all manually.
So here's the solution that I've come up with, and it works. I'm not 100% proud of it because, as you can see, we're going to be using SendKeys. I usually try to avoid SendKeys. Now, I got a whole separate video on SendKeys. It does have its uses. Oh, I just realized my logo was off-center. SendKeys does have its uses, and this is one of them. When you can't get something to work perfectly with VB code, it's a cheesy solution, folks, but I've tested it a couple of times, and it works, and I've used this technique before in the past.
So, if you've got a database and you just want to pull all the images out of it and create files out of them, this will work for you, and I'm going to show you how to do it. Before we get started, though, this is a developer-level video. What does that mean? Well, that means if you don't know anything about VBA, go watch this video first. It's about 20 minutes long. It will teach you everything you need to know to get started. Also, go watch my Send Keys video to learn more about SendKeys. As you can see there, it's the "good enough sometimes" function.
I try not to use it, but sometimes you're left with no other choice. We're also going to use the Sleep function because oftentimes with SendKeys you have to put a little delay in there because you can't just send a whole bunch of keys at once. You've got to send a key, wait a second, like when you're waiting for Paint to load, and then continue on with the code. So, go watch the Sleep function video and go watch my Delete Files video too. We're going to try deleting the file before we try saving it because if you go to save it and it's already there, Paint's going to go, "Hey, this file is already there. Do you want to save it?" and it'll mess everything up. So we'll delete it first, and then we'll save it, just in case. So go watch this video too.
These are all free videos. They're on my website. They're on my YouTube channel. I'll put links down below in the description. Go watch them, and then come on back. Alright, here's my TechHelp free template. This is a free database. You can grab a copy off my website if you want to. I'm going to make a folder on my desktop to put this stuff in, because I want to put all the images in the same folder. So I'm just going to call it "new database" or whatever you want to call it. Alright, we'll put our database file in there. You don't have to; this is just what I'm doing. Alright, open her up and let's start up the database.
Okay, now the first thing we need is to make an OLE object field, which I normally recommend against. But I do run into a lot of students who use them. Because this is how we used to have to do it in the old days. And I'm guilty of it too. I used to use these back in the day because there was no better solution. But now we know better. And if you aren't familiar with the new way to do it, then go watch my Images video. Basically, you don't want to store images in your database because it's extremely inefficient. And if you've got big images and lots of them, your database will fill up in no time. So in this video, I show you how to store the path and file name of the image in the table and then you store the actual file in a folder, which is how you should do it, okay? Alright. So go watch this if you want to learn more about that.
But for the sake of class, let's go into our customer table, and I'm going to add a field down here. We'll call it "profile picture," and that will be an OLE object. "Object Linking and Embedding" is what it stands for, and it was a good idea back in its day, where you know you could take any kind of image like a PDF file or a Word document or a spreadsheet, and you could paste it in there, and it would save it. But it's just not efficient; it's not a good idea to use them. Alright, save your table, close it, and let's throw that on our customer form. And I am just going to delete all of this stuff because we don't really need it for now, goodbye. And I'm going save one of these buttons because I'm going to repurpose it in a second, but right here, let's put our OLE object. Okay, so go to Form Design, go to Add Existing Fields, find your profile picture, click, drag, drop, there it is right there.
I'm going to delete that little text box that comes with it. We can now close the field list and let's make this a decent size for a profile picture, like that. Okay? All right, we're looking good so far. Save it, close it, open it. Now there are lots of different ways you can put stuff in here. You can right-click, and you can insert an object, but generally, if you can see the picture here, the best way to get it in there is to copy and paste it. In fact, let me just show you, if you do go to insert object, this is the way you get your stuff stored. If you go create from file and let's browse for a file. All right, let's say I pick this one here, "Picard and Riker," all right, and I insert it. Okay, if you see your pictures like that, then this technique that I'm going to show you isn't going to work. Only if you can actually see the picture in there will this work, and that's usually resolved to just bitmaps. Alright, I'm going to hit escape. If you try to add a bitmap that way, you'll actually see the picture here. Or if you copy and paste the image, then you'll see it here as well.
Alright, if I open up "Spock" here for example. Alright, here's Mr. Spock. It's an animated GIF, but I'm going to take a screenshot of it using my screen capture tool, and I'll just grab it like this. And then I'll switch over to my database, click on that field, paste it in there, and there you go. That's traditionally the way that you store images in your OLE objects. Go to another one. Let's grab "Kirk." Here's a copy of Kirk right there. Open that up. Handsome W. I just watched him last night on, I think it was Bill Maher. And he's 93 years old, and he still looks fantastic. Alright, one more. And he still talks like he's, like, you know, in his 60s. He's a young chicken. I don't think I have a picture of Troy. Let's skip. Let's do, I'm going do a couple more so we have them in here. "Jean-Luc," let's find Jean-Luc. There he is right there. Open him up. And you can see how and nicely it stretches or resizes.
I will do one more, "Riker with the beard." I love this one, and I'll just grab Riker like that, so we got Riker. Alright, perfect. So we got four pictures in here. We'll pretend it's 4,000. And I want to be able to extract them. We're just doing one at a time for now. Which, by the way, I apologize. I usually assume people that are working with Microsoft Access know the Windows basics. So if you don't know how to take a screenshot, you can use the Windows Snip and Sketch tool, which comes with Windows. I'm using HyperSnap myself. I've been using it since, I think, the '90s. So that's my favorite tool. But Snip and Sketch works almost the same way.
All right, so now we've got this image here. Let's make this button so we can save this to our hard drive, and we will cover that tomorrow in part two, so tune in tomorrow, same bat time, same bat channel, or if you're a member, you can watch it right now because I'm going to record it in just a few minutes. So either sign up and be a member, and you can watch it right now, or come on back tomorrow for part two. 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
OLE object fields in Access
Inefficiency of storing images in databases
VBA script issues with image extraction
Access OLE object storage mechanism
VB code for extracting images
Using SendKeys in VBA
Using the Sleep function in VBA
Deleting files before saving new ones
Creating an OLE object field
Alternatives to storing images in Access
Inserting images into OLE object fields
Copying and pasting images into Access
Handling different image formats in Access
Screen capture methods for images
Saving images to a hard drive from Access
Preview of part two of the tutorial
Membership benefits for video tutorialsQuiz Q1. Why is it not efficient to store images directly in a Microsoft Access database?
A. It makes the database aesthetically unpleasing
B. It's difficult to extract images later
C. It increases the database size significantly and affects performance
D. Access only supports storing images in JPEG format
Q2. What is the result of trying to extract images stored in an OLE object field using VBA script according to the user's experience?
A. The image is extracted perfectly
B. The script fails to run
C. The image appears corrupted and cannot be opened
D. The format of the image is inverted
Q3. What does OLE stand for in the context of Microsoft Access?
A. Object-Limited Embedding
B. Object-Length Evaluation
C. Office Linking Experience
D. Object Linking and Embedding
Q4. Which of the following is NOT a suggested prerequisite video mentioned before attempting the OLE image extraction technique?
A. VBA for Beginners
B. SendKeys Function
C. Sleep Function
D. Network Configuration in Windows
Q5. The video tutorial is aimed at which level of users?
A. Beginner users with no prior Access experience
B. Intermediate users with some Access knowledge
C. Developer-level users with VBA knowledge
D. Access experts who specialize in database administration
Q6. According to the video, what method will be used in the offered solution to extract images from the OLE object field?
A. A VB code that accurately strips off the headers and footers
B. A high-level API call to Access's internal functions
C. The SendKeys method, which is generally avoided
D. SQL queries to manipulate the image data directly
Q7. Why is the SendKeys method typically avoided, according to the tutorial?
A. It's deprecated and no longer available in the latest Access versions
B. It has significant chances of introducing security vulnerabilities
C. It's unreliable and only considered as a last-resort option
D. It's too complex and requires extensive programming knowledge
Q8. Before attempting to save an extracted image file, why does the presenter suggest deleting any previous instance of the file?
A. To prevent the possibility of the file being too large to save
B. To ensure the image is appended to the existing file
C. To avoid file overwrite confirmation prompts that can disrupt automation
D. Because duplicated images are known to cause database corruption
Answers: 1-C; 2-C; 3-D; 4-D; 5-C; 6-C; 7-C; 8-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 deals with how to extract images stored in OLE object fields in Microsoft Access. This question comes from a user who, as a coin collector, has accumulated thousands of images in his database and is trying to extract them as JPEG or BMP files using VBA. He finds that the images created by his script are not viewable; although the file sizes look correct, the images cannot be opened in standard viewers.
I want to start by acknowledging that this is a common issue, and something I have revisited many times over the years. If you spend any amount of time searching online, you'll come across multiple VBA samples that attempt to solve this. The trouble is, while most of these scripts will produce a file, when you try to open that file, most image viewers will say that the format is not supported. The underlying issue is that Access packages anything stored in an OLE field, not just the raw file but with extra header and possibly footer data. Without knowing precisely how to strip that data, which has proved quite elusive, your extracted files will not be proper images.
If anyone out there has a pure VB or VBA solution to properly extract images from OLE object fields, I would be interested in seeing it, because I've never found a clean answer, and I've looked pretty extensively. However, there is a workaround that I know will at least get your images out of your database if you only have to do it once. It is not the most elegant solution, and I typically avoid it, but it gets the job done when you are facing a large set of images stuck in your database.
The workaround involves using SendKeys in VBA to automate the process of extracting an image. While SendKeys is not my preferred method in general, there are cases where nothing else will work conveniently. I have an entire video dedicated to SendKeys, if you want to learn more about its pros and cons. In this scenario, SendKeys is useful to automate manual steps when no better programmatic solution is available.
We will also leverage the Sleep function in VBA. When automating processes with SendKeys, you often need to allow time for actions to complete, like letting Microsoft Paint load before sending the next set of keystrokes. Delays help avoid errors or mishaps from commands sent too quickly. Another best practice is to delete any existing file before trying to save a new one with the same name. For this, I have a separate tutorial on deleting files with VBA, which helps prevent dialog boxes from interrupting your flow should the file already exist.
If you need background on VBA, working with SendKeys, using the Sleep function, or deleting files in VBA, those videos are freely available on my website and YouTube channel. I encourage anyone unfamiliar with these concepts to check them out first. Links can be found below the video.
For demonstration, I'm using my TechHelp free template database, which you can download from my site. I set up a folder on my desktop for all the images we'll extract, keeping everything together in one place. The first step is to create an OLE object field in your table. Normally, I advise against using OLE objects for storing images due to serious inefficiencies, but many users still encounter or inherit databases that use them.
If you are unfamiliar with the modern approach to handling images in Access, which is to store only the file path in your table and keep actual image files in folders, I recommend watching my video on this topic. It shows how much more efficient and scalable this method is compared to embedding objects directly in your table fields.
Returning to our example, I add a "profile picture" field as an OLE object in the table, save and close the table, and add the field to the customer form. OLE (Object Linking and Embedding) fields made sense years ago when there were fewer alternative ways to manage files, but with today's options, they are rarely the best approach.
After preparing the form, we look at how images get into OLE object fields. The most reliable method is to copy and paste the image directly. If you use the "insert object" option to create an object from file, you may end up with a link or package that does not render properly as a simple image, and this technique will not work in that case. The process outlined in this tutorial is only effective if you can actually see the picture displayed in the field, which is typically how bitmaps or directly pasted images appear.
For example, I take different Star Trek character images, use a screen capture tool to create images, then copy and paste them into the OLE object field on the form. Windows comes with the Snip and Sketch tool for such captures, but I favor HyperSnap myself.
Once you have a table with images stored this way, we are ready to automate extracting them. I will show you how to create a button on your form to save an image to your hard drive using the technique discussed, but this part will be covered in the next video. If you are a member of my website, you can watch the new lesson right away; otherwise, tune in tomorrow for part two.
This covers the basics of the challenge with extracting OLE images from Access databases, why the usual VBA code does not produce usable images, and the overview of a workaround using SendKeys.
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
OLE object fields in Microsoft Access
Inefficiency of storing images in databases
Common VBA script issues for OLE image extraction
How Access stores images in OLE fields
Workaround for extracting images with SendKeys
Using Sleep function to add delays in VBA
Deleting existing files before saving images
Creating an OLE object field in a table
Adding OLE object controls to a form
Copying and pasting images into OLE object fields
Inserting images using Insert Object dialog in Access
Handling different image formats in OLE fields
Using Windows Snip and Sketch tool for screenshots |
