Update OLE Object Path

[James Reay]

Does anyone know how to change the path of an OLE Object that is stored within a table? I want to move thousands of linked files to another location, but must update all the references.

 

[sfreeman@co.mer]

An update query will do it.

I assume that you are updating all records in a text field [here called "MyLinkedField"], from a folder like

c:\MyFolder\MyOLEs\

to

Z:\MyNewFolder\

Add a new field to your table [here called tblLink],
call it something like "MyNewLinkedField"

Create a new update query...

Something like:

UPDATE
tblLink

SET
tblLink.MyNewLinkedField = "Z:\MyNewFolder\"
& Right([tblLink]![MyLinkedField],Len([tblLink]![MyLinkedField])-19);

This should put the correct data into the new field. Obviously, you will need to adjust the '-19' for your particular case.

Once you double-check and verify that the new data is properly within the new field, you can delete the old field, and rename the new field with the name of the old field.

If the db isn't too big, I'd test this on a backup copy just to be sure it works as expected.

HTH

 

[James Reay]

Sorry, I should have been clearer - the field is of type OLE Object and I need to update the path of the linked Word doc etc that is stored within it.

 

[James Reay]

Partial Success

For anyone interested, a partial answer to the problem can be found in http://support.microsoft.com/?kbid=311147, but does not work if the linked object embedded in the control is iconified and does not explain how to differentiate between linked/embedded and empty fields within the table. To do this you will need to query the OLEType property of the control e.g.

Forms(FormName As String).Controls(ControlName As String).OLEType

which will return 0/1/3 represented by the following constants:

acOLELinked (0), OLEEmbedded (1), acOLENone (3)

The problem is that if the embedded linked object is iconified, Microsoft's code does not return the path of the link - I need this to be able to automate re-linking of many files with the new path derived from the old/current path via simple string manipulation.

Does anyone know how to pragmatically de-iconify a linked embedded OLE object without using the SendKeys function?

DoCmd.RunCommand acCmdOLEObjectConvert

brings up the correct dialogue box when the target control has focus and

SendKeys "%" & "E" & "O" & "c" & "{TAB}" & "{TAB}" & "{TAB}" & "D" & "{ENTER}", True

performs the whole task, but isn't particularly elegant.

Thanks

James.