Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
 
Home > TechHelp > Directory > Access > Extract OLE Image < Immediate Refresh | Extract OLE Image 2 >
Back to Extract OLE Image    Comments List
Transcript Pinned    Upload Images   Link   Email  
Richard Rost              
7 months ago
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 tutorials

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Extract OLE Image.
 

 
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 11/10/2024 3:47:06 PM. PLT: 1s