Enter the size of a picture stored in an OLE Object field

We have an ACCESS 2000 database with a TABLE, with an OLE Object field that stores photographs.  These photographs are supposed to be taken at a low resolution and cropped so that the file size doesn't get huge.

Problem is people are occassionally lazy and don't CROP and/or take the pic at the right RESOLUTION.  
So what should be an 8 or 9 meg data file jumps up to 200 meg because people insert a few dozen 5 megapixel jpg's.

Is there a command or some code in ACCESS that will return a value representing the picture file size for each record?   And is there something that will return the picture file type (bmp, jpg, etc...)

I was thinking I would create two new fields to hold this information.  That way I could look through the database & see which files are so big.
Who is Participating?
rockiroadsConnect With a Mentor Commented:
If u link a picture, then no it does not as you are not storing the image, just the link

have a look at this, if u want the ole image saved

perhaps this may help

so on point of getting it into access, if u create the file, get the size and resolution, then delete the file

I know its a workaround, but I dont know how to get the size of a ole object
nico5038Connect With a Mentor Commented:
When you have A2000 make sure that the Service Packs have been installed as otherwise there's a "bloat" problem:

For storing images in a database also check the sample .mdb,s BLOB's from: http://www26.brinkster.com/alzowze/home.asp
(Doesn't load fast)

TechGuiseAuthor Commented:
We do have 100% of the updates Microsoft lists for Access 2K.  I'm pretty sure the file size is growing because of the very large jpeg's people are taking.    In a controlled environment (using the correct res files), the database doesn't grow hardly at all.

We've begun getting the pictures via a little Logitech WebCam, (see my other question)which has helped a lot in the ease of getting the picture into the database, but they can still take a slightly higher res picture than I would like.
I noticed there was a "getFileProperties" command in the VB code of the BLOB script.   Is there something like that for an OLE control (getOLEProperties... or something)?
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Hmm, I'm also thinking about using the browser control like the sample .mdb does and then extracting the image from there.
Do you have the possibility to show the image in e.g. IE ?

TechGuiseAuthor Commented:
If I'm understanding you..... No, IE would not work.  We are trying to keep it as few steps as possible (and as simple as possible) to get the picture into the database.  

Right now, we have a form with a bound OLE control in which the user RIGHT CLICKS, selects INSERT OBJECT, then chooses CREATE NEW, scrolls down and selects Microsoft Photo Editor 3.0 Scan, then clicks OK.
This then opens up Microsoft Photo Editor with a capture popup from the Logitech Camera software where they capture the picture, Crop it, then go up to the FILE menu and choose update.   The captured pic is inserted into the OLE control.

Problem is, the res settings on the camera can be changed, and the user could forget to crop.
Jim P.Connect With a Mentor Commented:
As always -- storing the pictures (and other documents) in the database is rated as a VERY BAD IDEA.  We have a delivered apps that handle images with a SQL back end and they don't store the images in the DB.
The best method is to store the file to disk and then store a link/path to the file and bring the image up that way.

In your case it would solve several problems quickly.
1) You could just scan the directory listing to spot the uncropped images.
2) Your users can just save the image to the disk and then using the FSO/common dialog/custom
       grown VBA to get the file names.
3) You can control the bloat by just expanding disk space on the server and not worry about the Access' 2GB limit.
TechGuiseAuthor Commented:
Could you explain how I would easily take them to a specific spot to look for saved pics?  

FSO/Common Dialog/custom grown VBA?
I mentioned IE as it can be "embedded" in an Access form (add just the browser control) and grab the picture from there.
Check out the use in the sample .mdb's at: http://www26.brinkster.com/alzowze/home.asp (Takes a while to load..)

Jim P.Commented:
follow Nico's link above.
TechGuiseAuthor Commented:
That is cool.   Everyone is happy with the way we are getting the pics (from webcam, straight to ACCESS).  But if no one comes up with a way to show OLE object properties then I might have to do the IE route.
TechGuiseAuthor Commented:
I must be doing something wrong.  I can't get the embedded web browser to work.  Does it work for your guys?  I saved it to disk & it just locks up when I open the file.   If I don't save it to disk, it remains read-only & won't list any files that I browse to.
Just place the Microsoft WebBrowser from then toolbox on your form.
Next place in the form's OnOpen event:
Me.webbrowser1.Navigate URL:="www.ee-stuff.com"

this will cause the webbrowser to open with that URL.

From the original question, this snippet of code (place in VBA module) gets the image resolution and filesize of a image

Option Compare Database

Private Type BITMAPINFOHEADER '40 bytes
    biSize            As Long
    biWidth           As Long
    biHeight          As Long
    biPlanes          As Integer
    biBitCount        As Integer
    biCompression     As Long
    biSizeImage       As Long
    biXPelsPerMeter   As Long
    biYPelsPerMeter   As Long
    biClrUsed         As Long
    biClrImportant    As Long
End Type
    bfType            As Integer
    bfSize            As Long
    bfReserved1       As Integer
    bfReserved2       As Integer
    bfOhFileBits      As Long
End Type

Public Sub GetImageInfo(ByVal sFile As String)
    'create some working variables
    Dim hFile As Long
    Dim tmp As String
    'create the variables to hold the bitmap info
    'read the file header info
    hFile = FreeFile
    Open sFile For Binary Access Read As #hFile
        Get #hFile, , FileHeader
        Get #hFile, , InfoHeader
    Close #hFile
    MsgBox "Image Resolutiom : " & InfoHeader.biWidth & " by " & InfoHeader.biHeight & " pixels"
    MsgBox "FileSize : " & FileHeader.bfSize & " bytes"

End Sub

You can test this e.g.

Public Sub TestIt()
    GetImageInfo "C:\Windows\Rhododendron.bmp"
End Sub

Now if you want filetype, FileHeader has a filetype value, but I dont know how to represent them to a bmp, jpg etc
I guess u can pass in one of each type then display the value and find out that way

TechGuiseAuthor Commented:
I have great hope for this code, but unfortunately am just getting my feet wet on VB.   Where in the code do I refer to the OLE control at?

The Control that holds the OLE is called "PIC",
TechGuiseAuthor Commented:
I put your code on a COMMAND BUTTON, to test it.
I was thinking that at the point of you importing the file (does it actually ever exist as a file?), you get the info there and then and save to the DB

This code assumes that the file exists

remember access has a 2 gig limit in size, after that you wont be able to save any records.

May I suggest you change your backend DB to something scalable or store links. The latter is what I would do. Something jimpen has also suggested.
TechGuiseAuthor Commented:
I guess the file never really "exists" anywhere.   Currently we're taking it directly from the Logitech camera into the OLE field in ACCESS.   If everything was working right the 2 gig limit shouldn't be a problem.  At most we would have only 500 or 600 pictures, and if they save it bring it in correctly each BMP would only be around 60-80k.  That's why I was wanting the sizes, so I could police it.

But it sounds like the getting the size for them is not possible.  I probably will just need to have the users save the picture to disk, then LINK the OLD to the new BMP file, as suggested.  

Do pictures linked as OLE objects in an ACCESS table effect file size?  

Thank all of you guys very very much for your attemps.   I really appreciate it.    Is a point split between the three of you fair?
The OLE object needs additional space as there's a kind of "wrapper" around the image to open it.
Best to check the BLOB samples I gave you the link of.

urm,. I wonder if u can use GetChunk

u have to specify a size, you will have to keep looping until you dont get any more data,
then do your sums

sounds a nasty method to me though
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.