Fnding object linked to in OLE field (XP/sp2)

[Pat97orbust]

My Access table tbl_Pics has a field (OLE datatype) that stores either (1) jpg object or (2) link to jpg file (varies across records). Some of the original jpg picture files are missing, perhaps inadvertently moved, and I want to find them or re-produce them. Is there a way to (a) identify the path used (in the case of linked objects) so that I can find the actual jpg file and/or ([img]/forums/images/smilies/cool.gif[/img] re-create the jpg file itself?

[WendellB]

In general, storing graphics files in an Access database, or linking to them using an OLE field turns out to be a bad idea. The usual solution is to store a path to the file and then use an image control to display the graphic. The problem, in addition to the situation you have encountered, is that the database tends to bloat by about 1MByte, sometimes even more for every object stored, even if they are small files.

Unfortunately, at this point the only recourse I know of is to try to open each record, note the file name displayed in the error message, and then do a search for that file - slow and time-consuming.

[MarkD]

As Wendell noted, saving OLE objects in an Access db file is NOT good idea. In response to item (a), how to identify linked object path, this can be accomplished by converting the binary OLE object to an Unicode string, then searching string for linked object's path. As noted in MSKB 311147:

ACC2000: How to Programmatically Change the Paths to Linked OLE Objects

"Paths to linked OLE objects are not exposed in any properties in Microsoft Access. To programmatically change a linked OLE object path, you must remove the object and then insert a new object through a form." This is why you have to do things the hard way. The attached text file is an exported code module with sample code that shows how to loop thru records in table with an OLE object field to determine the path for those objects (like pictures) that are linked. The example uses the Northwind Employees table, which has an OLE field (Photo) for employee's photograph. For test, modified table to replace embedded bitmap images with linked .JPG files for some records. See ListOLELinkedPaths sub. Sub opens ADO recordset and for each record calls GetOLELinkedPath function to get path (returns Null string if not found) (this function adapted from MSKB article cited above). Test results:

1 C:MYDOCU~1MYPICT~1SamplesSample01.jpg
2 C:MYDOCU~1MYPICT~1SamplesSample02.jpg
3 C:MYDOCU~1MYPICT~1SamplesSample03.jpg
4 C:MYDOCU~1MYPICT~1SamplesSample04.jpg
5 C:MYDOCU~1MYPICT~1SamplesSample05.jpg
6 C:MYDOCU~1MYPICT~1SamplesSample06.jpg
7 C:MYDOCU~1MYPICT~1SamplesSample07.jpg
8 C:MYDOCU~1MYPICT~1SamplesSample08.jpg
9 Linked path not found.

The procedure returned correct results for the 9 Employee records. Note that function returned path in short file-name format - apparently that's how path is stored in OLE objects. Also note that converting a large binary OLE object to an Unicode string is a resource-intensive operation; the VBE looked a little stressed out while running test, but did not crash or anything. Keep this in mind if utilizing this code. Once path is retrieved, you can perform a file search; this can be done any number of ways, including intrinsic VBA methods, Windows API's, FileSystemObject methods, Office FileSearch, etc. For more info see MSKB 185476:

HOWTO: Search Directories to Find or List Files

As for item ([img]/forums/images/smilies/cool.gif[/img], how to recreate .JPG file, no idea.

HTH

[Pat97orbust]

Someone offered this solution: Open form that includes this OLE object field. Double-click on picture. (jpg file opens in Microsoft Photo.) Click on File > > Save As. It works!