I have a table with approximately 700 records (700 Employees).
Each record has a Photo field and right now the photos are embedded.
The size of the entire database now exceeds 2 Gig.

I was going to experiment with linking the photo field instead of embedding but that will be quite an effort to relink all 700 photos.
I hope if I did go that route, that would solve the size issue?

The only way I know to reduce the size of a photo is to send it as an email attachment and change the size to the smallest. Then save the attachment and don't actually send the email.

Surely there must be a better way. Does anyone have any suggestions for me to either reduce the size of each embedded photo in my table or other ideas?

The tables are already in a backend database, but this one table (tblEmployee) is causing the overall db size issue.

I also thought about putting tblEmployee in it's own database and then I would have to have 2 backend databases but that would require lots of rework for all of the various user front end databases.

I would appreciate your thoughts.

Thank you.
Brandi

Hi Brandi,

Assuming you were using an OLE Object field, you might try converting them into Attachment fields. I haven't done anything like that because I don't use either data types but just make sure you try it on a backup copy first.

Also, if you move the employees table to another database, you might be able to link the table in the original BE, and you won't have to change anything in the FE. Again, that's just a thought. I haven't tried that one either.

If all else fails, you could automate the process of moving the image into a file folder and linking it to the appropriate record. That might be a better way to go.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Brandi,

You've got several questions embedded in this post, let's see if I can help to address them...


In any case, it really should not be "quite an effort". You can write some VBA code to go through each record of your table, grab some unique text (say the employee's EmployeeID number, or their full name) to use as the filename, then save the file with that unique text as the filename. Since this will mean a consistent and predictable way of identifying which picture goes with which record, you should be able to calculate the employee's picture filename on demand at any time in the future.


This is probably a safe assumption, especially if the picture files are high enough in resolution. Of course, we know virtually nothing else about your database, so it's hard to tell if there could be any other ways of reducing the file size. For example, do you Compact and Repair the backend?


You can search our Graphics forum for keywords like "resize" to find some suggestions. For example, I found a recommendation for ImageMagick in one thread. It's a command-line tool that can resize images, so with a little work, it could be controlled from Access via the Shell command. There are a couple of suggestions in this thread.

Since the problem seems to be centered around this one table, I would first try to export all of the picture files to a folder, and see how big that folder is when you're done. Then, you can decide whether you want to resize the photos or not, as well as whether you want to re-import them into your database after resizing.

Regarding Photo Size
In the pre-Access 2007 days, JPG files would be stored as BMP format, which made them balloon up in size unexpectedly for many users, which I think was the impetus for recommendations to store photos outside of the database entirely. From what I've read, this JPG-to-BMP issue is no longer a problem as of Access 2007, so I'm guessing you just have some really high-resolution photos, or there's another issue that's affecting your database.

There are two basic uses for an employee photo that I can see: Printing on paper or display on a screen. If the image is meant to be printed (like for an ID badge, you should measure how big the image needs to be, at it's largest. Let's say it needs to be 1.5 inches wide by 2 inches tall. To print a photo at 300dpi at that size, the image won't need to be bigger than 450 x 600 pixels in size. You could halve those dimensions, print at 150dpi, and still have a usable ID photo. On the other hand, if the photo is just needed to display on a form (like a front desk verification system for the security guard), the file can be much smaller. A common monitor resolution is 1024x768. So, if you only need to put up a picture that takes up roughly 1/4 of the screen, it doesn't need to be any bigger than 256x192.

Hope this helps,

Dennis

Thank you for your suggestions. I will try a few things and then probably have some more questions, especially if what I do involves VBA.
I think I will need help learning
1. how to automate taking the embedded photos from tblEmployee and putting them in a folder and 2. automating resizing them in the folder 3. linking them back into tblEmployee

Thank you.
Brandi

This post has been edited by Brandi: Mar 27 2012, 01:49 PM

Hi Brandi,

(IMG:style_emoticons/default/yw.gif)

Dennis and I are happy to help. Let us know if you get stuck. Good luck!

Hi Brandi

PMFJI

Access does not have a facility to export embedded images, but there are various tools which you could try e.g. this one on Stephen Lebans site. Though I can't say how accurate / successful they are.

FWIW; I store all my images in a separate sub folder to the folder that holds the BE data file. I use the windows find file dialog box (API) to locate the image and then copy the file to this folder, renamed to the ID of the record it is associated with. This way I do not have to save any details of the image in the database as the image path and filename is constructed whenever the image has to be retrieved; as a result there is no database bloat however many images are saved in the images sub folder.

hth

I don't usually push other products, but when I do its because I've experience with them and they work. Have a look at DBPix. It's not too expensive and does a great job of working with images.

hope this helps

I am attaching a tiny database sample with one table that has three fields.
tblEmployee where each employee has an embedded photo. Because of the size limitations of my attachment, these are already small photos but I am just looking for the process.

Can anyone tell me how the command would work on this or any other way I can actually make the embedded photos smaller from within the Access database?
I have over 700 real photos to resize and since they aren't linked now, I am hoping to be able to do something to the table itself.
Unfortunately this is something I did not forsee five years ago.

Thank you for looking.
Brandi

Hi Brandi

I downloaded the Stephen Lebans file and opened the form "ExtractInventoryAllOLEobjectsFromTable"
Selectd the mdb, Output folder and 'Naming field' (as EmpID) and Clicked to start the Exporting process.

It exported the 3 'package' files to a jpg file in the selected output folder without any problems.

hth

OK. I'm going to try your method of exporting the pictures but what I am really trying to do is change the size of the embedded picture, otherwise, I will have to delete all the embedded photos and relink them one at a time. Is that correct?

Thank you.
Brandi

Hi Brandi,


Sorry, this is not to answer your question but to just offer an opinion. If you are going to go through the motion of exporting the images to a [network] folder, you might as well just leave them there instead of "re-linking" them as OLE Objects back into your database even if you manage to reduce their sizes.

If you're able to use the routine from Stephen Leban's website, you might be able to add something to the code to store the path of the exported image with the record it came from. Then, you can just delete your OLE Object field and use the path of the image in your forms/reports.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Are you using accDB, and using the new picture format?

File (or pizza button), then options-> current database

Eg this option:

(IMG:https://public.bay.livefilestore.com/y1p8q2BPVU5Oj03W-vbj_s2r6XD0qbVZVexj_hehSmuJsnAkw7u_nlmRfo794lhOnB9dah3SxlG-dQZyOOj8wPjeg/picf.png)

We complained about picture bloat for years, and thus Access finally fixed this issue.

You can now build applications that use pictures, and without the bloat issue (fantastic change in Access by the way).

And 2010 even better since it not only fixes the bloat issue, but we have a new "shared" image option in which you can manage and maintain all the images in your application. This means that not only can you share one image, but changing that image such as a corporate logo will be changed EVERY place in the application since only ONE copy of the picture exists and this again reduces bloat even more. The picture management feature and sharing is only available in a2010, but the new picture format is available in 2007.

So, I would check out the new picture format, and ensure you using an accDB file here.

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com

I can change the database to an accdb format.
If I do that, then is it as simple as changing the data type of my photo field in my table?

I see the option you have included as a print screen. So this would make all picture objects in my entire database smaller? If so, that would be totally awesome!

I have no idea what occurs to an existing system. I would certainly suggest you test anything like this on a copy.

I would perhaps create a new blank database, ensure this setting is correct, and then perhaps import the other table. So just "flipping" the swtich here may not go and change all existing data.

I never tested this option with existing older pictures being converted, so I kind going into a area that I not tested or even know how well this will work for you.

But, yes, this new option does reduce bloat and file growth –the file should only be as large as the actual pictures.

Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
kallal@msn.com

Hi Brandi

Do have a look at the attached. Extract the zipped files to the same folder (ie the 2 mdb files (Test FE.mdb and Test BE.mdb and the 'images' folder, which contains the the three jpg files)

Open the test FE.mdb file and it should automatically reconnect to the BE file in the same directory (when the autoexec file is run) and load 'form1'

The images are contained in a subfolder to the BE data directory and are loaded whenever a new record is selected on form1 (see the 'on current' event.)

Although A2007 and A2010 have the new picture /attachments field and they do not have the same bloating problem as earlier versions of access, I still do not use them. This is because adding attachments to an Access file still causes the data file to enlarge and Access is limited to a 2GB file size, which although it seems large enough can still quickly expand the database to the limits of Access' file size with image attachments .

Storing the images in a folder means that your filesize will never be limited by the images in your database. In the attached implementation the image files are given name of the record ID it is associated with. In this way there is never any impact on the db filesize how ever many images are stored.

hth

Thank you so much for the export and linking information. It works great.
I am going to try and have my user implement this method of storing the pictures in a folder and naming them using the Employee ID.

The system was originally in Access 2003 and when they moved to 2010, they did not really utilize the 2010 format. Many of the photos are very old and when I try to put new photos in the database, they are storing as a Package which my research shows, means that the user is missing some old software to display the photo properly.

Here is what I found. "When a user inserts a file using OLE Embedding (eg a jpeg picture), Access attempts to invoke the application that is registered for that file-type/extension, and have it store the 'Object' using OLE mechanisms. Only a few graphics applications support this functionality, and if the registered application does not, a default 'Package' is created (which cannot be displayed directly on a form/report).
In order to display the picture, Access attempts to invoke the application that was used to initially store the object. If this application is not correctly installed & registered on the user's system, the image will display as an icon"

Another article sounds like they need to reinsatll MS Photo Editor.
"When you use Datasheet view to add GIF, JPEG, and other common types of image files to an OLE Object field in a table, you see the word "Package" instead of the expected "Bitmap Image."
"Package" appears in the field because the OLE Object field only supports Windows Bitmap (.bmp) and Device Independent Bitmap (.dib) files. If you want to store additional types of graphic files in an OLE Object field and have Access render them properly, you must install additional software, and that software must provide the components (called OLE servers) needed to display the additional image types.
Previous versions of Microsoft Office provided a program called Microsoft Photo Editor, which in turn provided the OLE server needed to render GIF and JPEG files. The Office 2003 System replaced Photo Editor with Microsoft Picture Manager, which does not provide the needed OLE server for those types of files.
You can use either of the following techniques to work around this issue:
• Store your images outside of your database and use Visual Basic® for Applications (VBA) code to display them. For more information, see the Microsoft Knowledge Base article How to display images from a folder in a form, a report, or a data access page.
• Convert your GIF or JPEG images to Windows Bitmap (.bmp) files. Although BMP files are usually larger than GIF or JPEG files, they can help reduce the size of your database, because Access doesn't create additional Device Independent Bitmap (.dib) files to support them. For information about why Access creates DIB files when you use GIF or JPEG images, see the Microsoft Knowledge Base article Why OLE Objects Cause Databases to Grow.
• Reinstall Photo Editor using an Office XP or Office 2000 installation CD.
Reinstall Photo Editor from Office XP
"

If you know of a better way to get rid of the package icon so that the photos will display properly, please let me know.
They do use the old databases for history, so I wasn't sure what converting them all to accdb files would do.

Thank you all so much.

Brandi

Hi Brandi,


The old database probably can only display BMP images. If you're using JPGs, then when converting the old database to ACCDB, you will be able to use the Attachment Field which could display JPG files.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

[attachment=52100:GuruReport.doc]I did almost the exact task you described; only with pictures of houses.
I created a folder with all the pictures in it; then created the form with the data that retrieved the picture to go along with it. This keeps the image entirely out of the database. I had to pay this guy from XXXX to spell it out for me, and he did, and it works.
The only pecularity I've observed is that I need to start designing the form exactly as specified; then add all the extra information on the form. The form also does not copy well. These are mysteries I will not investigate.

This post has been edited by TinyGiant2010: Mar 31 2012, 01:59 PM

If you "code guys" want to open those files and post their contents here; by all means do so. They were written for a simpleton (me) and the method does work. I have thousands of pictures that I can just blink by record by record; the load times are, as far as I can tell, instant. I am very pleased to finally be able to contribute something of value to this forum, rather than being just a beneficiary. :-)