I know I’ve made posts in the past regarding not using attachments

but in case it wasn’t clear in that post let me extend the advice to include ANY embedding of files within a database by ANY means.  Thus, please, please, please, NEVER use the OLE Object data type to save files within a database!

It always seems like a brilliant plan at the time, but I PROMISE you that it will nip you in the ass down the road!

Data Extraction

Case and point, I had someone approach me with 2 databases nearing the 2GB limit each which employed OLE Objects to store files.  In this instance Corel files.  The solution to the problem is to extract all the files from the database and store them on a server as normal files, then store the path/filename as plain text in the db.  The issue now however is that there is no automated way to extract thousands and thousands of files!  Yep, to make this happen, someone will have to go record by record, activate the OLE Object and manually perform a Save As.   NUTS!

Oh, I hear some of you screaming into your monitors at me saying that’s not true.  You can save the OLE Object to a Stream to file, or you can perform OLEObject.SaveAs, …. or use Stephen Lebans OleToDisk.  Tried all of those and many more, none worked!

For simple images fine, PDFs as well, but for Corel files (and others) it just doesn’t work.

I even went as far as contacting the Dev Team (that’s how desperate I was/am) and was basically told the manual approach was the way to go in this instance.

OLE Server Isn’t Registered

Moreover, I’ll also add that by using OLE Objects, you gets into serious issues with requiring the original software that embedded the file in the first place.  Case and Point, in this instance the files were embedded as Corel 18-19-20 graphics.  I installed Corel 2023 and cannot open them via Access, yet 2023 is fully compatible with those version, but because it wasn’t the version used to embed the file originally, it doesn’t work and you get the above error.

However, if I embed a 2023 file, then it does work.

I also tried to use the Convert option, but that led nowhere fast as it displayed the files as ‘Unknown Type’ and thus failed to complete the conversion operation.

So OLE Objects seems to suffer from some serious versioning problems.  Just upgrading your application version (for the files you are embedding, not Access) and suddenly you can find yourself with OLE Object files that you can no longer access!

Loss of File Meta Data

Another serious PITA with using OLE Objects is the fact that once the file is embedded we loose the file meta data/attributes: filename, original path, …  making it even more difficult to extract and possibly try and compare with existing files.

Overall Process Stability

I’ll also state that while trying to work with 2023 files, to perform some tests and try and figure this all out, Access became unresponsive multiple times to the point that I had to manually kill the process on a number of occasions. Also, in several instances, when I was done working with OLE, Access left hidden instances of Corel running in the background.  So, all of that to say, OLE Objects seem VERY temperamental.

Some Final Thoughts

How is it we have no means to extract OLE Objects just blows my mind!  Seems to me if you build a process to allow embedding files, that same process should natively have the means to extract them as well.

Finally, as I personally find it ridiculous that we have no means to simply save an embedded file back to disk, I have created a Feedback Portal suggestion and would ask you help by up voting it. It’s the only way such things will ever get addressed.