Linked OLE Objects bloating size of database

[Nosaj08]

Hello everyone,
I am running into a slight problem with my database design. Currently, I am constructing a database that acts as an inventory system. For the inventory table, I have one column with its data type set as an OLE Object for the purpose of adding an image of each product into the table. To insert the image, I have a form linking to the table with an OLE Object box and its property is set to only allow linked images. Now my question is, why would that column be adding 230 mb to my database size if all of the images are linked and not embedded? There are only about 85 entries into the table. Also, I have tried compacting and repairing and that has not been able to fix the problem. Any ideas? Thanks in advance!

Jason

[marianne]

well. often it is recommended not to any images on the database itself BUT save ONLY the path of the images in the database and allow the code to get the path and show it in a form or report.

[RuralGuy]

Marianne is absolutely correct. Access is terribly inefficient at storing pictures. There is improvement in ac2007 with the attachment field but then you are limited to ac2007. Just store the path to the picture and display them as needed. Here's a link to three ways to do that.

[Nosaj08]

Ok, sounds good. Ill browse through the example to see what I can pull out of it. Thanks for the help!

Jason

[Nosaj08]

Hey RuralGuy,
Thanks for the link! Linking the image as an external file worked perfectly for what I needed to do. I ended up modifying the code slightly to allow me to add the image directly into my form, without having to double click on the value and open a second preview window. However, one problem that I am having with it is when there is no image linked to a particular inventory item and there is no value in my "Display Image Link" field, I receive an error that says "Type Mismatch". Is there an If-Then statement that will allow me to tell the image box that if there is no data in the image link field to stay blank or become disabled? Right now my image box refers to the image link text box using the following VB code:

Me![imgTheImage].Picture = [Display Image Link]

Thanks for all the help!
Jason

[RuralGuy]

How about creating an image for "No Image Available"? You of course could stop the load if you want with VBA. Are you loading the picture in the Current event of the form?

[Nosaj08]

I am loading the picture in the On Open event and also on each of the navigation buttons on the form so it would update the image when I changed to another inventory item. I think I would rather stop the load through VBA just in case someone forgets to attach an image to the inventory item. I also have inventory reports that I use this same method on and if one item does not have and image attached to it, I'll receive the "Type Mismatch" error upon opening the report followed by access closing the report. Should I change over to On Current instead?

[RuralGuy]

Use the Current event of the form. That event will fire every time you land on a new record, including the first time after the form is opened. Then change the code to:

If Len([Display Image Link] & "") > 0 Then
   Me![imgTheImage].Picture = [Display Image Link]
Else
   Me![imgTheImage].Picture = ""
End If

[Nosaj08]

Ok, I made the change and it worked perfectly on the forms, Thanks! Any idea why the Else command wouldn't work on a report? I tried using the code in the On Open event for the report and it did not place any of the images correctly. So, I moved the code over to the On Format event under the Detail section and that seemed to allow all of the images to display correctly. However, once it gets to an item with no image, Access uses the image from the previous item instead of making it blank. This problem only occurs in reports.

[RuralGuy]

Try using the DetailPrint event instead of the DetailFormat event. The events in a Report are nothing like the events in a form.

[RuralGuy]

Actually I think you will need to play with the Visible property of the control displaying the picture. Something like:

If Len([Display Image Link] & "") > 0 Then
   Me![imgTheImage].Visible = True
   Me![imgTheImage].Picture = [Display Image Link]
Else
   Me![imgTheImage].Visible = False
End If

...using your control names of course.

[Nosaj08]

Yup, that seemed to do the trick. Its strange that the code needs to be written differently in reports than in forms in order to perform the same function. I guess I need to learn more about the structure of the VB language to understand it. Thanks for all the help!

Jason

[RuralGuy]

Yup, that seemed to do the trick. Its strange that the code needs to be written differently in reports than in forms in order to perform the same function. I guess I need to learn more about the structure of the VB language to understand it. Thanks for all the help!

Jason

Hi Jason,
Actually the two are very different and serve completely different functions. Reports are a one way piece of paper headed for a printer and Forms are objects for a screen where the user interacts with the object. Really very different. There are also things you can do with a report that are a real bear to do in a form such as running totals. Different tools for different jobs.

[Max D]

Its hard to write external image storage usually. You'll find some problems again and again.

Beside that, its usually not easy for users to manipulate images in such a database. There is a ActiveX control called AccessImagine, which handles external image storage automatically and makes adding images to database convenient - you can load from file, scan, paste from buffer or drag-n-drop. You can crop image right inside the database and do much hard-for-Access tasks with it.

[RuralGuy]

Max,
That really looks like a good image tool and only $28. Thanks for the link.