Converting MS Access "OLE Objects" back to plain JPEGs - best way?

Question

Background: We have an old (but business-critical) SQL Server database with an MS Access ADP front-end; this was originally upsized to SQL Server from a series of Access databases.

This database tracks hazardous materials for our customers, and stores a large number of images. These images are inserted from MS Access, and get put into the database as OLE Objects.

The problems are:

• These are a pain to read out in anything except Access/Office
• There's a MASSIVE storage overhead - ~10GB of images takes up 600+ GB of storage space(!)

My question is this: what way would you recommend to convert these bloated objects back into simple JPEGs? Once we do this we can finally migrate our front-end from Access and onto a simple web-based system, and our backup times will become manageable again!

Answer 1

Use Access MVP Stephen Lebans ExtractInventoryOLE tool to extract the OLE objects from a table to separate files.

http://www.lebans.com/oletodisk.htm

According to Lebans: "Does NOT require the original application that served as the OLE server to insert the object. Supports all MS Office documents, PDF, All images inserted by MS Photo Editor, MS Paint, and Paint Shop Pro. Also supports extraction of PACKAGE class including original Filename."

Also, Access 2007 stores OLE objects much more efficiently than the historical BMP formats of previous versions, so you would have a smaller storage space and be able to keep your Access app if you converted it from the 600+GB storage of SQL Server to Access 2007 accdb format. Your backup times would be manageable and you wouldn't need to spend time converting an Access front end to a web front end.

Answer 2

Take the *.bas file from here http:http://stackoverflow.com/Content/img/wmd/ul.png//www.access-im-unternehmen.de/index1.php?BeitragID=337&id=300 (unfortunately it is German).

It uses the GDI+ lib from MS (included in Win standard installation) to import/export pics to/from Access OLE.

Rough translation of interface:

• IsGDIPInstalled: Checks for installation of GDI+
• InitGDIP: Init of GDI+.
• ShutDownGDIP: Deinit of GDI+ (importand to be used!)
• LoadPictureGDIP: Loads pic in StdPicture object (bmp, gif, jp(e)g, tif, png, wmf, emf and ico).
• ResampleGDIP: Scales pic to new dimensions and sharpens if needed.
• MakeThumbGDIP: Makes thumbnail and fills border with color.
• GetDimensionsGDIP: Get dimensions in TSize-Struktur in pixel.
• SavePicGDIPlus: Saves Picture objekt in file as BMP, GIF, PNG or JPG (jpg with given quality)
• ArrayFromPicture: Returns a byte array of picutre to put pic into OLE field of table
• ArrayToPicture: Creates byte array of OLE field of table containing a picture

Answer 3

I think the reason your database becomes so bloated, is that the JPGs are also stored as bitmaps inside the "OLE object" structure, or so I've seen, depending on the method the JPEG was inserted.

This is not optimal, but: for every image in the database, I would programmatically create a dummy .doc containing just the image, then pass it through OpenOffice conversion, and extract the JPEG from the images subfolder of the produced OpenOffice document (which is a ZIP file).

I would then replace the OLE documents in the database with the raw JPEG data, but then I have no way for you to plainly display them in a custom application (unless it's a web app).