2GB limit - Will it be true for a db with large number of images?

  • Thread starter Thread starter RRKEM via AccessMonster.com
  • Start date Start date
R

RRKEM via AccessMonster.com

Just a thought. If 2GB is the "upper limit " for Access db, it would appear
that this is beacuse at this size , there would be millions of records that
may overwhelm the Jet database engine. On the other hand, when images are
embedded in the record ( we will not dicuss the pros and cons of this), the
actual number of records may just be 10,000 - 20,000 by the time the 2GB
limit is reached. Will Acess still fail in the same way as while handling
millions of records at 2 GB? Food for thought or...?
 
Yes, it is completely impractical to store many images in an OLE Object
field in an Access database.

In Access 2007 this issue will be significantly improved, i.e. the database
will store the compressed image instead of converting it into an
uncompressed bitmap. The issue will still be there, but not as quickly as
with current versions.
 
Using the current OLE Embedding approach with it’s stupendous overhead it often only takes a few hundred
images to hit the 2GB limit, but as you point out, it’s usually possible to achieve 10,000-20,000 records
with compressed images at screen resolution, provided they are stored correctly as blobs (raw/long binary).
It’s always wise not to get too close to the limit though, otherwise Access can display a range of problems,
whatever you’ve filled the database with.
 
On the other hand, when images are
embedded in the record ( we will not dicuss the pros and cons of
this), the actual number of records may just be 10,000 - 20,000 by the
time the 2GB limit is reached.
Click to expand...

Rather fewer than that, with OLE objects.

Just as an aside, does anyone have actual experience of using GetChunk etc
to store the real binary data in a OLE (aka BLOB) field? Should be possible
and more efficient, but I've never tried it for real.

All the best


Tim F
 
does anyone have actual experience of using GetChunk etc
to store the real binary data in a OLE (aka BLOB) field?
Click to expand...

Yes, lots. It generally works very well. Unlike OLE Embedding/Linking there’s essentially no overhead
(well, a few bytes due to allocation granularity, just like the file-system). If images are stored at
screen resolution (and not, for example, at full digital camera resolution) then the capacity is sufficient
for many applications - as the OP said, typically 10,000 to 20,000 images (e.g. digital photo downsampled
to 1024 x 768 at 80 JPEG Quality).

Performance can be a bit of an issue if you use the built-in Access Image Control – loading and decoding
JPEG synchronously results in a delay when scrolling through records that can range from ‘annoying’ to
‘unusable’, and this is compounded by having triple the disk I/O (read from database file, write to temporary
image file, then read into image control), but the bulk of the work is usually in the JPEG decoding process,
so this is not that much worse than using the Access Image Control with regular external image files (and
should be mitigated somewhat by disk caching). Larry Linson’s Imaging Examples include a demo of this
approach (see link below). I believe that there may also be commercial solutions available.

Larry Linson’s Imaging Sample:
http://members.tripod.com/accdevel/imaging.htm


--
_______________________________________________________
DBPix 2.0: Add Images to Access, Easily & Efficiently
http://www.ammara.com/dbpix/access.html
 
Just as an aside, does anyone have actual experience of using GetChunk etc
to store the real binary data in a OLE (aka BLOB) field? Should be possible
and more efficient, but I've never tried it for real.
Click to expand...

It's worked when I've played with it, but I haven't yet needed to use it
in an application.
 
See:
http://www.lebans.com/loadsavejpeg.htm
A97LoadSaveJpeg.zip contains a database demonstrating how to use the Intel
Jpeg library with Access. Jpeg files are stored in their original compressed
state within a Binary OLE avoiding the Ole object "bloat" issue. Here is the
A2K version: A2KLoadSaveJpeg.zip

A standard Image control is used to display the contents of the field
containing the Jpeg file. The Intel DLL is used to load the contents of the
field directly into memory bypassing the need to create a temporary disk
file.

The contents of the Image control, containing any supported source Image
format, can be saved to disk as a Jpeg file.

Very large Jpeg files that could not be loaded into the standard Access
Image control directly can now be loaded through the Intel Jpeg library.
--

HTH
Stephen Lebans
http://www.lebans.com
Access Code, Tips and Tricks
Please respond only to the newsgroups so everyone can benefit.
 
Back
Top