Bloat with Linked OLE/Best Practice for OLE Links

[jimo]

I am having a problem with database bloat. I have confirmed that the bloat
is caused by a table of OLE images that are used to store links to documents
stored on a fileshare. The database is not storing the full OLE files, just
links to them.

These OLE images (typically Visio diagrams, Word Docs, or Excel
Spreadsheets) are then printed in a report. Converting the images to jpegs
does not appear to be a good solution for us, as many of the linked OLE
documents continue to evolve and we don't want to have to keep saving
separate copies of the documents/images.

Approximately 150 linked OLE images are consuming almost 900 MB of disk
space. What can I do to eliminate this bloat? Are there better ways for
storing references to Microsoft Office documents and printing them in reports?

 

[vbasean]

are you using a hyperlink field to store the link?
is the directory to all these files the same or close?
why not create a default directory that reaches the root of where all these
files are and then have your field only store the sub directory and then link
them together in a function?
You could have a text field (as long as the sub directory never exceeds 255
characters) and that would take up a whole lot less space.
where to store teh default directory. A table? I've created tables in my
db (or a single table) dedicated to storing the databases default values.
Add a memo field to the table to store your default directory for this
certain application.

 

[jimo]

I'm using an OLE field to store the link, as we need to be able to include
the image in reports. Storing the files in a directory isn't the problem -
the issue is that the database file is growing like crazy even though we are
only storing the OLE as a link...

 

[Fred]

I know OLE only as a generality, I'm not enough jock to know the fine points
of it's architecture or terminology. But it's well established that when one
uses an OLE field type to put images into an Access DB (at least through
2003, 2007 is rumored to be better) the DB undergoes exponential bloat.
Despite the "L" in the field type name, the experts (which I am not) refer
to this as being stored "in" the table. I learned from a zillion experts to
store them separately (simply files in a seperate folder), store their path /
file name in a table (typically a text field or 2) and then use code to make
them display in forms and reports.

Not sure if this helps a little.

 

[jimo]

Thanks Fred.

What you describe does make sense and would certainly be a preferable
solution, but my understanding is that there is not a reliable way to display
an OLE image/file in a report via code referencing the path of the file. I
understand that this is reasonably straightforward for jpegs, but that it
doesn't work for OLE files (Word, Visio, etc). Any pointers to the contrary
would be welcome!