OLE or not to OLE

[Guest]

Have had a request from one of my clients to add OLE support to one of my
access databases. Basically, they want to be able to relate a jpg, bmp, or
ppt file to a particular record in the database. I've heard that OLE objects
tend to seriously bloat an MDB, which causes them to rapidly reach their 2Gb
size constraint.

Am I better off implementing OLE, where I actually store the object in my
db, or just storing the reference to the object, and filing it away where
noone can see it?

In the past, I've always done the latter, and created a folder on the server
where I post a copy of the file, and continue to reference that file whenever
the appropriate record is selected.

If using OLE is the way to go, anybody have any references for how to write
the object to the db and to access it later?

 

[Guest]

Dale:
I had this same request from an engineering customer who wanted to link
their CAD drawings to the records of their jobs in the database. We
organized the CAD drawings so that - the folder structure where the files
were stored - matched data elements in the job file in the database. For
example, a job was located in the FOO wire center and the job number was
A100BB06. The code to construct the path looks like this:
myPath = "\\servername\engineeringCAD\" & [WireCenter] & "\" & [EngJobNum] &
"\"
The path constructed would look like this:
\\servername\engineeringCAD\FOO\A100BB06.
After testing the path to see that it really did exist, this code did the
trick:
stAppName = "explorer.exe " & myPath
Call Shell(stAppName, 1)
This would open the folder where the drawings for that particular job were
stored. This is one way to do it, there are undoubtedly other ways, most
likely other better ways to do it. I look forward to hearing how others have
solved this.

The engineers had to be trained to save their drawings in the appropriate
path, creating the destination folder if it did not already exist.

 

[Tim Ferguson]

If using OLE is the way to go, anybody have any references for how to
write the object to the db and to access it later?

OLE is definitely not the way to go: it creates a _huge_ overhead and
massive databases with few records.

One of these might help:

http://support.microsoft.com/default.aspx?scid=kb;en-us;103257

http://support.microsoft.com/default.aspx?scid=kb;en-us;210486

Hope it helps


Tim F