moosach
asked on
Insert OLE Object in Table with VBA => Long Binary Data
Hi everybody,
that might be a newbie question but I am simply not able to
find the solution ...
What I want to do is to create a piece of VBA code which
uses GetObject to start and instanciate an OLE Object. The
Object pointer I want then to store in a table field (Ole Object). Afterwards it should be possible to double click
on such a field and the appropriate application comes up with the matching document, spreadsheet or whatever.
Much the same as I can do from Table View with menus (insert object).
So in principle I did that :
Sub Button_Click ()
Dim db As Database, rs As Recordset, obj As Object
Set db = CurrentDB()
Set rs = db.OpenRecordset("testtabl
Set obj = GetObject("c:\temp\x.doc")
rs.AddNew
rs!objekt = obj
rs.Update
Exit Sub
End Sub
Code is running, but when I have a look at the table, I only find "Long Binary Data" as content type.
When I insert an object via the menus (Insert Object ...) I find either 'Word Document' or 'Package' and so on, depending what I have inserted.
I am really stuck now and feeling rather dumb ...
Any help is appreciated !!
that might be a newbie question but I am simply not able to
find the solution ...
What I want to do is to create a piece of VBA code which
uses GetObject to start and instanciate an OLE Object. The
Object pointer I want then to store in a table field (Ole Object). Afterwards it should be possible to double click
on such a field and the appropriate application comes up with the matching document, spreadsheet or whatever.
Much the same as I can do from Table View with menus (insert object).
So in principle I did that :
Sub Button_Click ()
Dim db As Database, rs As Recordset, obj As Object
Set db = CurrentDB()
Set rs = db.OpenRecordset("testtabl
Set obj = GetObject("c:\temp\x.doc")
rs.AddNew
rs!objekt = obj
rs.Update
Exit Sub
End Sub
Code is running, but when I have a look at the table, I only find "Long Binary Data" as content type.
When I insert an object via the menus (Insert Object ...) I find either 'Word Document' or 'Package' and so on, depending what I have inserted.
I am really stuck now and feeling rather dumb ...
Any help is appreciated !!
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I can't really tell what type of object your trying to insert into the OLE field, but from what I can tell, you need to use the acOLECreateEmbed action.
The information in this article applies to:
- Microsoft Access versions 7.0, 97
--------------------------
SUMMARY
=======
Moderate: Requires basic macro, coding, and interoperability skills.
This article describes how to programmatically link or embed an object in
an unbound object frame on a form using the object frame properties in
Microsoft Access 7.0 and 97.
MORE INFORMATION
================
You can set the object frame Action property at run time to perform a number of operations on an object frame. These operations include the ability to link and embed objects in an object frame, as well as other operations for programmatic access to OLE functionality.
There are other object frame properties that you must set before you set the Action property. Those other properties depend on the type of OLE object you are working with, and the type of action you want to perform using the Action property.
Linking or Embedding an OLE Object
--------------------------
To link or embed an OLE object in an unbound object frame on a form, first set the following properties of the frame:
- OLETypeAllowed: Set to acOLELinked to indicate the OLE field will contain a linked object, acOLEEmbedded for an embedded object, or acOLEEither for either linked or embedded.
- SourceDoc: Set to the path and file name of the file to be linked or embedded. Do not set this property if you are creating an empty OLE object.
- Class: Set to the class name of the file extension. You can get the class from the Windows registry file in HKEY_CLASSES_ROOT listed under the file extension. You can also find the information in the documentation for the application supplying the object. This property may not be required, depending upon the OLE server and object being used.
- SourceItem: Indicates the portion of data in the source document to link to. For example, it can be a cell or cell range in a Microsoft Excel spreadsheet or a bookmark in a Microsoft Word for Windows document. This property setting is optional.
Once you set these properties, you can set the Action property to
acOLECreateLink to link an object in the object frame, or acOLECreatEmbed to embed an object.
The following methods show examples of embedding an OLE object in an unbound object frame.
Method to Embed an OLE Object
--------------------------
1. Start Microsoft Word.
2. Create a new document and type some text into it.
3. Save the document as TestOLEAuto.doc, and then quit Microsoft Word.
4. Start Microsoft Access.
5. Create a new form not based on any table or query in Design view.
6. Add an unbound object frame control to the detail section of the form.
7. In the Insert Object dialog box, click Create New, and then select Microsoft Word Document in the Object Type box. Click OK, and then quit Microsoft Word when it starts.
8. Set the following properties for the unbound object frame:
Unbound Object Frame:
Name: OLEWordDoc
Height: 11"
9. Add a command button to the form, and set its Name property to cmdOLEAuto.
10. Set the OnClick property of the cmdOLEAuto command button to the following event procedure:
Private Sub cmdOLEAuto_Click()
On Error GoTo Quit
Me![oleWordDoc].Enabled = True
Me![oleWordDoc].Locked = False
' Specify what kind of object can appear in the field.
Me![oleWordDoc].OLETypeAll
' Class statement for Word document.
Me![oleWordDoc].Class = "Word.Document"
' Specify the file to be embedded. Type the correct path name.
Me![oleWordDoc].SourceDoc = "c:\<pathname>\TestOLEAuto
' Create the embedded object.
Me![oleWordDoc].Action = acOLECreateEmbed
' Optional size adjustment.
Me![oleWordDoc].SizeMode = acOLESizeZoom
Quit:
End Sub
11. Save the form as frmOLEAutoEmbed.
12. Open the form in Form view and click the cmdOLEAuto command button.
Note that the TestOLEAuto.doc document appears in the form.
REFERENCES
==========
For more information about the Action property, search the Help Index for
"Action property," or ask the Microsoft Access 97 Office Assistant.
For more information about OLE linking and embedding, please see the
following articles in the Microsoft Knowledge Base:
ARTICLE-ID: Q123859
TITLE : INF: Sample OLE Automation for MS Word and MS Excel
ARTICLE-ID: Q132003
TITLE : INF: How to Save a Copy of an Embedded MS Word Document
ARTICLE-ID: Q114214
TITLE : INF: How to Programmatically Embed or Link an Object in a
Form
KBCategory: kbprg kbhowto
KBSubcategory: FmsHowto
Additional reference words: 7.00 97 8.00
Copyright (c) Microsoft Corporation. All rights reserved.
The information in this article applies to:
- Microsoft Access versions 7.0, 97
--------------------------
SUMMARY
=======
Moderate: Requires basic macro, coding, and interoperability skills.
This article describes how to programmatically link or embed an object in
an unbound object frame on a form using the object frame properties in
Microsoft Access 7.0 and 97.
MORE INFORMATION
================
You can set the object frame Action property at run time to perform a number of operations on an object frame. These operations include the ability to link and embed objects in an object frame, as well as other operations for programmatic access to OLE functionality.
There are other object frame properties that you must set before you set the Action property. Those other properties depend on the type of OLE object you are working with, and the type of action you want to perform using the Action property.
Linking or Embedding an OLE Object
--------------------------
To link or embed an OLE object in an unbound object frame on a form, first set the following properties of the frame:
- OLETypeAllowed: Set to acOLELinked to indicate the OLE field will contain a linked object, acOLEEmbedded for an embedded object, or acOLEEither for either linked or embedded.
- SourceDoc: Set to the path and file name of the file to be linked or embedded. Do not set this property if you are creating an empty OLE object.
- Class: Set to the class name of the file extension. You can get the class from the Windows registry file in HKEY_CLASSES_ROOT listed under the file extension. You can also find the information in the documentation for the application supplying the object. This property may not be required, depending upon the OLE server and object being used.
- SourceItem: Indicates the portion of data in the source document to link to. For example, it can be a cell or cell range in a Microsoft Excel spreadsheet or a bookmark in a Microsoft Word for Windows document. This property setting is optional.
Once you set these properties, you can set the Action property to
acOLECreateLink to link an object in the object frame, or acOLECreatEmbed to embed an object.
The following methods show examples of embedding an OLE object in an unbound object frame.
Method to Embed an OLE Object
--------------------------
1. Start Microsoft Word.
2. Create a new document and type some text into it.
3. Save the document as TestOLEAuto.doc, and then quit Microsoft Word.
4. Start Microsoft Access.
5. Create a new form not based on any table or query in Design view.
6. Add an unbound object frame control to the detail section of the form.
7. In the Insert Object dialog box, click Create New, and then select Microsoft Word Document in the Object Type box. Click OK, and then quit Microsoft Word when it starts.
8. Set the following properties for the unbound object frame:
Unbound Object Frame:
Name: OLEWordDoc
Height: 11"
9. Add a command button to the form, and set its Name property to cmdOLEAuto.
10. Set the OnClick property of the cmdOLEAuto command button to the following event procedure:
Private Sub cmdOLEAuto_Click()
On Error GoTo Quit
Me![oleWordDoc].Enabled = True
Me![oleWordDoc].Locked = False
' Specify what kind of object can appear in the field.
Me![oleWordDoc].OLETypeAll
' Class statement for Word document.
Me![oleWordDoc].Class = "Word.Document"
' Specify the file to be embedded. Type the correct path name.
Me![oleWordDoc].SourceDoc = "c:\<pathname>\TestOLEAuto
' Create the embedded object.
Me![oleWordDoc].Action = acOLECreateEmbed
' Optional size adjustment.
Me![oleWordDoc].SizeMode = acOLESizeZoom
Quit:
End Sub
11. Save the form as frmOLEAutoEmbed.
12. Open the form in Form view and click the cmdOLEAuto command button.
Note that the TestOLEAuto.doc document appears in the form.
REFERENCES
==========
For more information about the Action property, search the Help Index for
"Action property," or ask the Microsoft Access 97 Office Assistant.
For more information about OLE linking and embedding, please see the
following articles in the Microsoft Knowledge Base:
ARTICLE-ID: Q123859
TITLE : INF: Sample OLE Automation for MS Word and MS Excel
ARTICLE-ID: Q132003
TITLE : INF: How to Save a Copy of an Embedded MS Word Document
ARTICLE-ID: Q114214
TITLE : INF: How to Programmatically Embed or Link an Object in a
Form
KBCategory: kbprg kbhowto
KBSubcategory: FmsHowto
Additional reference words: 7.00 97 8.00
Copyright (c) Microsoft Corporation. All rights reserved.
ASKER
Hi,
thanks for this KB article, but I already knew him. If you read carefully (...embed an object in an unbound object frame on a form ...) you'll see that this describes how to embed an Ole object in a frame on a form (!!!). This doesn't describe how this could be done in a table field. And that is exactly my problem.
Seems I am stuck with AppendChunk and GetChunk.
If you don't see another method I'll rate your answer. Please add a comment.
Regards
Toni
thanks for this KB article, but I already knew him. If you read carefully (...embed an object in an unbound object frame on a form ...) you'll see that this describes how to embed an Ole object in a frame on a form (!!!). This doesn't describe how this could be done in a table field. And that is exactly my problem.
Seems I am stuck with AppendChunk and GetChunk.
If you don't see another method I'll rate your answer. Please add a comment.
Regards
Toni
Sorry, thought you were trying to do it in a form.
You might play around with the acOLECreate... objects and see if they MIGHT work in a table.
You might play around with the acOLECreate... objects and see if they MIGHT work in a table.
ASKER
I'll try that. But for the next 2 days I'm on the PDC here in Franfurt ...
ASKER
This AppendChunk etc. Thing seems to be the only way ...
Thanks
Toni
Thanks
Toni
ASKER
BTW, if your solution is the only way to enter an OLE Object in a field by VBA, it is rather funny. MS invented OLE Objects and created Access/VBA and didn't implement an easy way to insert OLE Objects on this level ... Just wondering ...