Cannot create an OLE Linked field in a recordset using VBA

[rzielins]

My goal is to programmatically set the OLE datatype field in one of my tables to the OLE object selected by a user via the Open File Dialog Box
ie. if the user selects "c:\my documents\Demo.xls" I want to set the 'Multimedia' field in my 'DataSetDetails' table to 'demo.xls'

I have painted a Bound Object Frame onto my form with the intention of setting all the necessary properties to this control and then assigning this control to the OLE field in my table. (hopefully my intentions and methods are clear)

so far my code is:

A With rstDataSetDetails
B .AddNew
C
D !StudyID = cboStudyID
E !DataSetDescription = txtDesc
F !DataSetStatus = txtDataStatus
G !DataType = cboDataType
H
I oleDataSetLink.Class = "Excel.WorkSheet" J ' Set class name.
K oleDataSetLink.OLETypeAllowed =acOLELinked L ' Specify type of object.
M oleDataSetLink.SourceDoc = strFile_Path D N ' the User selected source file c:\my E O documents\demo.xls.
P oleDataSetLink.Action = acOLECreateLink Q
R !Multimedia = oleDataSetLink
S .Update
T .Bookmark = .LastModified
U
V End With

This almost works, I receive no errors on Line R, however it does not successfully link the 'demo.xls' spreadsheet to the DataSetDetails table. Instead of displaying "Microsoft Excel Spreadsheet" in the DataSetDetails table the 'Multimedia' field displays "Long Binary Data" and the field's Value is an awful looking value of:

",









ÿÿÿÿWorksheet
Excel.Sheet.8









Access.OLE2Link









@

ÐÏࡱá















>

þÿ

































þÿÿÿ







ÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿÿ

Any clues to get around this beauty?

 

[ElsVanMiert]

The OLE commands require the existence of a OLE control on a form therefore the code does not work properly in your code (I am surprised that no error message occurs!).

 

[rzielins]

Thanks for your help - you are quite correct. I did paint an OLE object on the form but it had no control source. My theory was to assign every important OLE property to this OLE control and then assign this to the OLE object in my table. It is this last step that doesn't work. Is there a special way to assign OLE object references and values?

 

[ElsVanMiert]

I never encounter any problems with the OLE objects since I use the Graphic Wizard (Powerup at www.unsoftwareag.com).