We were having this discussion in my Microsoft Access Forum, but I know a lot of you who are interested in Access don't follow the forums, so I figured I'd post it here. Hint: you should subscribe to the forum. :)
I just did some more research on Access and OLE (Object Linking & Embedding) with large files.
One of my good clients, Dave, said he's trying to link PDF files into his Access tables, and they're still blowing his tables up in size. So I did some investigating.
I did this with both Access 2003 and 2007, and the file sizes are a little different, but both results came out about the same. This time instead of small files, I used a couple of larger ones.
First, I started with a blank database that was 304k. I took a PDF file that was 6133k (about 6 MB) and embedded it into the table. The database blew up to 12364k, and only compacted down to 12340k. Wow!
Then I tried LINKING the same PDF file. The database grew to 6148k and only compacted down to 6124k. So basically, we didn't save much space at all.
It gets even stranger with a BMP (Bitmap) file. I embedded a 6751k BMP file and the database expanded to 7120k. It compacted down to 7120k.
Now here's the REALLY wierd part: when I LINKED the BMP file into the Access 2007, the database shot way up to 13968k and compacted at 13994k. That means it's actually TWICE as large just LINKING to the picture as it was embedding it!
With the Access 2003 database, the linked BMP compacted way down to 116k. So obviously Access 2003 and 2007 databases are handling the BMP files differently since one blew up gigantically, and the older database handled it just fine. Odd.
I tried the same thing with a 752k JPG file. This time BOTH databases compacted just fine, down to 332k. So apparently they were able handle the JPG images just fine.
What's going on here? Well, the research I've done on Microsoft's (and other) web sites has indicated that Access's OLE isn't very efficient at storing large amounts of binary data. Some objects store just fine. Others create incredible "database bloat" due to overhead associated with that file type.
In a nutshell, not only is Access storing binary data for that object but also information on how to work with that data. This can sometimes cause the database to skyrocket in size. And, it seems that the more compressed some objects are (like ZIP files) the larger the overhead that's required to store them in the database. One web site reports that one ZIP file blew up 10,000 TIMES its original size when dropped into an Access table. I haven't tested this myself, but my testing with BMPs and PDFs makes it plausible.
So LINKING to objects in your Microsoft Access tables might not always be the best solution for you. If you have hundreds or thousands of pictures, PDFs, videos, or other objects you need to store, you might be better off saving those files on your PC in a folder somewhere, and then using a TEXT FIELD to store the full pathname to that file in the database. Like:
C:\Documents\MyBigPicture.JPG
You can then use an unbound image control or picture control and display that picture using some VBA code. The files need to be where everyone who is using the database can get to them. So if you are on a network, you will need to store them in a shared network folder:
\\SERVER\Shared\MyBigPicture.JPG
As far as PDF files are concerned, if you just need to store them so someone can double-click on them to open them up and read them, you can also use this technique, and just launch Adobe Acrobat with the VBA Shell commands to view the file.
Storing the data in your database might not be the BEST solution for your needs if you have a lot of data.
Learn more about working with pictures in your Access databases in my IMAGING SEMINAR.
17 years ago
