Attachment field question and db size question

[Icebox]

I've got an access 2007 database of about 4500 photos. In the past the db
has just served to record data about each image, but now I'm adding in scans
of each photo via an attachment field. My first question about the
attachment field is whether the attachments are stored in the accdb file or
externally. It appears to me that it is in the db file.

Assuming that I'm right about that, then my main concern is the size the db
will become once the photos are added. Right now there's only about 10MB of
data and code in the db, but each set of attachments will run about 500 to
700kb, which means that when the attachments are complete the db will grow to
over 2GB in size. What kind of problems will a db that size cause? Will the
attachments cause db operations to slow to a crawl? If so, is there a
different way to achieve the same purpose, which is to display relevant
images of each photograph when the image is looked up?

Thanks!
Mark

 

[Jeff Boyce]

Mark

As I recall, Access has a 2 Gb limit, so you will NOT be storing all those
photo/images in your accdb file!

Options include using a back-end that has greater capacity (SQL-Server is
one example), or storing a pointer (filename/path) to where the images are
actually stored (not in Access).

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Icebox]

Sorry for the double posting; Microsoft's web interface gave me an error
after the first post, claiming that the post was not received. So I tried
again, got the same error, and then noticed the first article indeed was
received. And this one was too. C'est la vie.

Mark

 

[Dirk Goldgar]

I've got an access 2007 database of about 4500 photos. In the past the db
has just served to record data about each image, but now I'm adding in
scans
of each photo via an attachment field. My first question about the
attachment field is whether the attachments are stored in the accdb file
or
externally. It appears to me that it is in the db file.

Assuming that I'm right about that, then my main concern is the size the
db
will become once the photos are added. Right now there's only about 10MB
of
data and code in the db, but each set of attachments will run about 500 to
700kb, which means that when the attachments are complete the db will grow
to
over 2GB in size. What kind of problems will a db that size cause? Will
the
attachments cause db operations to slow to a crawl? If so, is there a
different way to achieve the same purpose, which is to display relevant
images of each photograph when the image is looked up?

If your estimated final DB size is on the order of 2GB, you must find
another approach. 2GB is the maximum size of a Jet (Access) database, and
you start to run into problems as you get near that size.

Although Access 2007 is much better about storing images than previous
versions, and doesn't bloat the way it used to, if you have that many images
(of that size) to store, you'd do better to store the images in a folder at
a specific location, and then just store the name of the image file itself
in the database. The image folder could be either at a fixed location, or
it could be defined relative to the database itself; either way, your code
would know where to look for them.

On the form for viewing the photos, you'd have an image control, and in the
form's Current event you'd calculate the full path to the image file and
assign it to the image control's Picture property.

Note: I haven't worked with A2007 as extensively as I have with earlier
versions, so it's just possible that there's mechanism for doing this
automatically. However, I've done it many times in earlier versions using
simple code along the lines I've described.