Access 2007-2019
Access 2000-2003
Access Seminars
TechHelp Support
Tips & Tricks
Access Forum
Course Index
Topic Glossary
Insider Circle
 
Home   Courses   Seminars   Templates   Help   TechHelp   Forums   Contact   Join   Order   Logon  
 
 
Access Object Linking (OLE) File Sizes
By Richard Rost   Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   12 years ago

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.

Picture links in Access using text fields Upload Images   Link  
Wayne Duff-Riddell 
11 years ago
I have used the text field approach to linking jpgs in Access 2003 and am still suffering from serious bloat.  Any ideas?


Reply from Richard Rost:

You're storing the filenames in a TEXT field and displaying them using the Image.Picture method, like I describe in my Imaging Seminar, and your database is still bloated? Have you tried compacting? Unless you're using an OLE field, you shouldn't have that much bloat. I'd have to see your database to tell you for sure what the problem is. Read More...
Add a Reply
I like what you do Upload Images   Link  
Naa Albert 
12 years ago
hey rost! its been great using your free tutorials and I give you my personal two thumps up. I am a boy of 22 i live in Ghana and i dont have money to buy any of your complete tutorials but atleat am happy with what the free tutorials has given me. Thank you
Add a Reply
printing the pdf Upload Images   Link  
Dave Sterner 
12 years ago
They come from a variety of sources: ArcGIS, Google maps, but most are hand drawn on pre-existing watershed maps and then scanned.  Ultimately I hope they go to one source, like ArcGIS (which right now get printed as pdf's). I was trying not to rock the boat too much with their previous methodology(non-Access - hand written data forms and hand drawn maps - both of which they were scanning)and was excited, then disappointed when I showed them the linked OLE object function.   Read More...
Add a Reply

Show Older Comments...
View in Table Format

Start a NEW Conversation
 
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.
 
Subscribe
 

You may want to read these articles from the 599CD News:

12/4/2021Hangman!
12/3/2021Access Beginner 2
12/3/2021Access Beginner 2 Lessons
12/3/2021Access Beginner 2
11/30/2021Import Multiline Cells
11/30/2021Adam's Access Games
11/29/2021Prevent Close
11/28/2021Random
11/22/2021Currency Symbols
11/13/2021Access Developer 36
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Account Login
Online Theater
Downloads
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Blog RSS Feed    Twitter

YouTube Channel    LinkedIn
Keywords: access ole linking embedding  PermaLink