How to Change SourceDoc for Unbound Object Control in MS Access 2003 Report
I am developing an application in Access 2003. It has a table that lists various information about clients. One of the fields is a text field that has the path to an attachment for that client. It could be a Word document, a Visio drawing, Adobe document, jpg file, etc. None of these attachements are that big and there is only one per client. I want to print the attachment in a report so I have inserted an unbound object frame control in the report. I have tried to change the SourceDoc property of this control with no success. I have read several Experts Exchange question/answers and Googled this but have not been able to change it. When I create the control I need to specify a document and it seems I can't get rid of the original document no matter what I do.
Thank you in advance for your help.
Thank you in advance for your help.
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim filePath as string
Dim drawingCtl As ObjectFrame
Set drawingCtl = Me!OLEUnbound29
' I will reference the field contents to get the filePath but for
' now I am just hard coding it in to try to get it to work.
filePath = "C:\NetworkDiagrams\Diagram08.vsd"
drawingCtl.OLETypeAllowed = acOLELinked
drawingCtl.Class = "Visio.Drawing.11"
drawingCtl.SourceDoc = filePath
' This next statement produces an error
' Without this statement nothing happens at all
drawingCtl.Action = acOLECreateLink
I don't think you can do that.
Loading an object on a form into a bound object frame is as simple as:
With Me.OLEBound0
.SourceDoc = "C:\NetworkDiagrams\Diagra
.Action = acOLECreateLink
End With
However, this doesn't work on reports. I guess you might have to use a form to load your OLE links into a table (in an OLE field), and then use that table to display the object on a report. Bound object frames displaying an OLE field work fine; but OLE objects themselves are a pain, so it's best to keep your structure with the path in a memo field.
Perhaps another expert has a better idea, but that is all I could come up with.
(°v°)
Loading an object on a form into a bound object frame is as simple as:
With Me.OLEBound0
.SourceDoc = "C:\NetworkDiagrams\Diagra
.Action = acOLECreateLink
End With
However, this doesn't work on reports. I guess you might have to use a form to load your OLE links into a table (in an OLE field), and then use that table to display the object on a report. Bound object frames displaying an OLE field work fine; but OLE objects themselves are a pain, so it's best to keep your structure with the path in a memo field.
Perhaps another expert has a better idea, but that is all I could come up with.
(°v°)
ASKER
Thank you - I will wait to see if there is another solution. I have always understood that it is best to try to keep objects out of the database when possible.
Of course, yes, and I hope there is. If not and in case you need help to create a temp table with OLE links, ask here; I'll get the notification.
(°v°)
(°v°)
LynnBertrand,
Try this (if you have not already.)
Make an "OLE Object" Field in your table, to link to the attachment.
Right click each attachment field in each record and "Insert" each attachment into each attachment field as a "Link".
Create your report from this table.
Open the report in design view and set the "Size Mode" property of the attachment field to: "Zoom".
Preview the report.
JeffCoachman
Try this (if you have not already.)
Make an "OLE Object" Field in your table, to link to the attachment.
Right click each attachment field in each record and "Insert" each attachment into each attachment field as a "Link".
Create your report from this table.
Open the report in design view and set the "Size Mode" property of the attachment field to: "Zoom".
Preview the report.
JeffCoachman
ASKER
Yes, this is what I ended up doing after the original response. I thought that it was best practice to avoid OLE fields in tables whenever possible so I was treating this as the last resort solution.
> I thought that it was best practice to avoid OLE fields in tables
Absolutely! OLE is a terrible technology when applied to databases. I certainly hope that you maintain your file paths externally, and only create the OLE fields on the fly (in a temporary table?) when needed for reporting.
(°v°)
Absolutely! OLE is a terrible technology when applied to databases. I certainly hope that you maintain your file paths externally, and only create the OLE fields on the fly (in a temporary table?) when needed for reporting.
(°v°)
ASKER
Okay, so I guess if you can give me your thoughts on how to do this, I will close this question and award to you? I'm sorry I haven't posted too many questions in the past so I'm a little new to this part.
Thanks in advance for your help.
Thanks in advance for your help.
<I thought that it was best practice to avoid OLE fields in tables>
It is best to avoid storing Objects in the database because of bloat and the myriad of weird error message you might get it everything is not "just right".
:-O
(OLE Sever Errors, "Packages" inserted instead of the object itself, File association issues)
That's why I said use the "Link" option.
Now believe it or not, you will still get bloat even if the objects are linked.
The only reason I posted was that you might have relatively few records, or you "Had" to get this working, one way or another.
This is the only way I have ever used to print "Objects" in a report.
Be sure to follow whatever advice harfang gives you on the best practices for doing it this way.
;-)
JeffCoachman
It is best to avoid storing Objects in the database because of bloat and the myriad of weird error message you might get it everything is not "just right".
:-O
(OLE Sever Errors, "Packages" inserted instead of the object itself, File association issues)
That's why I said use the "Link" option.
Now believe it or not, you will still get bloat even if the objects are linked.
The only reason I posted was that you might have relatively few records, or you "Had" to get this working, one way or another.
This is the only way I have ever used to print "Objects" in a report.
Be sure to follow whatever advice harfang gives you on the best practices for doing it this way.
;-)
JeffCoachman
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you to you both. I wish Microsoft would let me do what I originally wanted to!
;-)
LynnBertrand ,
One final note:
Access 2007 has a new "attachment" datatype.
My guess is that this new data type is supposed to address some of the short falls of OLE Fields.
I have 2007 at work but have not tried this out yet.
As Allen Browne states (http://allenbrowne.com/Access2007.html#Mixed) using these fields could easily push you to the 2 gb limit fairly quickly.
One other thing that is unclear is whether or not this new datatype has, or will have, a corresponding datatype in SQL server (or MySql)
I'll play around with it next week to see how these attachments:
-Are activated.
-Display on Forms and Reports.
-Affect overall database performance.
JeffCoachman
One final note:
Access 2007 has a new "attachment" datatype.
My guess is that this new data type is supposed to address some of the short falls of OLE Fields.
I have 2007 at work but have not tried this out yet.
As Allen Browne states (http://allenbrowne.com/Access2007.html#Mixed) using these fields could easily push you to the 2 gb limit fairly quickly.
One other thing that is unclear is whether or not this new datatype has, or will have, a corresponding datatype in SQL server (or MySql)
I'll play around with it next week to see how these attachments:
-Are activated.
-Display on Forms and Reports.
-Affect overall database performance.
JeffCoachman
> I wish Microsoft would let me do what I originally wanted to!
Actually, the idea to combine several types of documents in a single report is more Adobe's Acrobat niche, isn't it? But frankly, I can't see a really good solution to what you are trying to do. What if one document needs three pages, and the next is just a logo? Manually combining and laying out the material will always look much better...
Anyway, success with your project!
(°v°)
Actually, the idea to combine several types of documents in a single report is more Adobe's Acrobat niche, isn't it? But frankly, I can't see a really good solution to what you are trying to do. What if one document needs three pages, and the next is just a logo? Manually combining and laying out the material will always look much better...
Anyway, success with your project!
(°v°)
ASKER