Link to home
Start Free TrialLog in
Avatar of David Smithstein
David SmithsteinFlag for United States of America

asked on

Embedding large Word documents in MS Access as OLE objects

I can embed a 18 MB Word document in an Access 2002 OLE field without error, but a 35MB document returns the following error when I try to open it. "There isn't enough memory to complete the automation object operation on the OLE Object."  I can actually get the file in the database table, and open it once if I don't leave the field.  If I leave the field and go back and try to open it I get the error message.  Microsoft indicates the field size limitation of an OLE field should be 1GB.

I'm doing this directly in the table to eliminate the user interface as the problem.  I can also reproduce the problem in a new database with only one table with an OLE field.  I can also reproduce the problem with my computer in safe mode.  The problem was brought to my attention by a customer, so it's happening on their system using the 2002 versions of MS Access runtime as well.

I've increased my page file size, and I'm on a new Dell XPS 1730 with massive amounts of resources in terms of hard drive, memory, and processor speed, so it doesn't make sense that it is an issue with the computer.

Anyone have any ideas where to look?
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

OLE Objects should be avoided with Access databases, for several reasons. The only positive reason for using an embedded OLE object is to allow "portability" of the database. Otherwise, you'd be far better off using an unbound OLE control, and pointing it to the document location. This way, your document can be stored outside the database, avoiding the bloat and trouble that comes with OLE objects
Avatar of David Smithstein

ASKER

Thanks, but embedding offers complete control over the file, which in this case is a controlled document in a secure quality management database.  I'm using a replicated database topoloy so that I only have one user linked to a given database, and my largest installation is about 5 years old now and working fine on a 1.6GB database with hundreds of embedded documents.  Portablility is also one of the selling points of the system as users, or consultants, can work on the system when disconnected from the newtwork and synchronize with the hub when they return.  It also offers redundency for when Access misbehaves and makes data loss extremely rare as every user is a back up.

Besides, over the years end user computers have become more and more capable of handling a little bloat, and the usual paradigms about MS Access limitations don't carry as much weight as a result.

Any other ideas?
I second that. OLE was never meant for databases in the first place, and the problems they create are too numerous to list here (your experience is only one of them). If you really must store large text files within the database, which is a bad idea in its own right, at least store them as binary objects, not OLE. It's a bit more work, but you retain full control over the content. But the best solution is to store any external documents, well, externally.

This being said, I'm afraid I don't have the answer to your actual question: how to allow 35 Mb Word documents in an OLE field. I tried it (I had to create a document with 2000 pages) and received the same error. I'm assuming that this document contains in turn other OLE objects (typically entire workbooks for the sake of a single chart) to make it so large, but that is another problem (or not if we are ranting about OLE).

(°v°)
And I think you mean linking the documnet as apposed to embedding it.  Using an "unbound" control means it isn't connected to a data field in a table and it becomes pretty useless as it won't even store the path to the linked document.  
I didn't see your reply before I submitted mine. If you really think you need to keep this architecture, then store your files as BLOBS, or include code to prevent documents over a certain size (a couple of megabytes makes sense).

> embedding offers complete control over the file

Quite the opposite, in fact. OLE objects offers absolutely no control. The very simple task to save a copy requires you to open the file using the server application (and no other) in order to save it. A user cannot use Open Office to edit a simple RTF document if that document was initially saved using Word, for example. This is entirely silly, of course.

(°v°)
Thanks Harfang for trying to replicate the issue, and confirming the limitation.  The document is only 12 pages but has several images that are causing the large file size.   I have been using OLE fields in this way for many years, as have all my users.  Granted Access and the OLE server application will occasionally trip over each other when editing the embedded document, but other than that it has been working just fine.  

Could this be something in the registry related to Word or Access?
Say more about saving the file as a BLOB
I'm using "control" in the context of the ISO requirements of document control and the ability for users to access and change a linked document at it's location on the network.  It requires the company to establish a whole seperate procedure, conventions, and controls of the actual file locations to establish they have control over "bach door" access to the documents.
> Say more about saving the file as a BLOB

A BLOB is a "binary large object", which can be stored in an OLE field, but more often in a plain binary field, which you can create like this:

ALTER TABLE Table1 ADD COLUMN Blob BINARY;

The data type BINARY is not made available in the table design tool, but is recognized correctly once created, and entirely supported by Jet and Access. It's identical to an OLE field, without the interface connected with it.

To use a document stored like this, you save it to disk, edit it, and reimport it. See for example:

Read and Write Blobs
http://www.ammara.com/access_image_faq/read_write_blob.html

OLEtoDisk
http://www.lebans.com/oletodisk.htm

Leban's contains many more examples, and is worth knowing anyway. The ability to extract a file from the field could be a workaround to handle the error on very large documents.

(°v°)
Actually the best solution is to double click one of the images in the document, and use the image compression feature in Word.  My 35MB document goes down to 7MB and works fine in an embedded OLE field.

The BLOB option would require the user to go through extra steps of saving to disk, editing, reinserting which introduces both extra steps and the possibility of inserting the wrong document if they aren't very organized with respect to where they locate their drafts.
Besides, over the years end user computers have become more and more capable of handling a little bloat, and the usual paradigms about MS Access limitations don't carry as much weight as a result.

Bloat has absolutely nothing to do with disc space, or the power of an enduser machine. While it does take up additional space (which isn't a big issue, as you mention) it also tends to corrupt the database container and cause other maladies. Access limitations are just that - limitations. They have no bearing on the machine on which Access is installed.

I don't necessarily agree with the delete, as harfang provided you with several correct solutions but I'll leave it to you to hash out.
ASKER CERTIFIED SOLUTION
Avatar of harfang
harfang
Flag of Switzerland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you and success with your project! -- (°v°)