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

 

[CrazyAccessProgrammer]

I asked a similar question a few days ago here is my question and the
response I received:

QUESTION:

One of the reasons I never used Embedded OLE field types in versions of
access prior to Access 2007 was that the embedded objects would quickly
grow
the size of your access database... sometimes to the point where you can't
enter any more data.

Does the attachment field type of Access 2007 have this same problem? From
what I understand so far, Attachment field types embed a copy of the
object
into your database (no option to link to an object from what I can see so
far).

I have a database application I am building that will include multiple
hi-res photographs with each record of the primary table. Normally I'd
make a
custom form that would link these photographs to the record. But I'm
curious
about using the new Access 2007 attachment feature as long as it doesnt
grow
my database by leaps and bounds.

ANSWER
When you embed a file in your database, Access 2007 uses the space taken by
the JPG, whereas previous versions used the uncompressed space (the amount
that would have been taken if the file had been a BMP rather than a JPG. So
the file space to store one photo in A2007 is multiple times less than the
space taken in previous versions.

This assumes you configured A2007 to do so under:
Office Button | Access Options | Current Database | Picture Property
Naturally, previous versions of Access can't display in the database the
info if you do this.

But your question relates to storing hi-res photos in an Access database. My
advice would be, Don't do that. A typical hi-res RAW photo will be 4-8MB
today, and over 20MB tomorrow (e.g. using a Canon 50D.) My wife is a graphic
designer, so we shot 7000 frames on our last trip. 7000 @ 20MB each would be
140GB of storage -- for one trip! An Access database has a 2GB limit. This
is not going to work in any version.

So, my suggestion would be to use a Text field to store the file name rather
than embed the data in an Attachment field.

Another unemphasised feature of A2007 is that the Image control has a
Control Source for the first time. This makes it really easy to display
photos: you just set the Control Source of an Image control to the name of
the Text field than contains the path, and Access shows the image. This even
works in Continuous Forms, so it's even better than using an Attachment
field. And as a bonus, this has none of the problems/limitations associated
using multi-valued fields like the Attachment.

 

[Dirk Goldgar]

[Allen Browne wrote:]
Another unemphasised feature of A2007 is that the Image control has a
Control Source for the first time. This makes it really easy to display
photos: you just set the Control Source of an Image control to the name of
the Text field than contains the path, and Access shows the image. This
even
works in Continuous Forms, so it's even better than using an Attachment
field. And as a bonus, this has none of the problems/limitations
associated
using multi-valued fields like the Attachment.

I didn't know that, though I wondered (cf. my own reply). What a welcome
addition!

 

[Icebox]

I didn't see that post or discussion, but thanks for pointing it to me. The
image control source property will make this very easy to do. Thanks!

Mark

 

[cathyclc]

Thanks for your insight about storing pictures in an ACCESS 2007 database
through attachments.

I am running into an issue that more has to do with the database performance
when attachments are being added or viewed. We have a HR database and we
store Employee Apps, Discipline Notices, etc. in an attachment field. These
are stored as PDFs, Word Docs, etc. As soon as one of the users (1-5 people
are currently using the database) adds an attachment or views an already
existing attachment everyone currently using the database gets an hourglass,
and the systems crawls until either the "offending" user closes the
attachment or exits the database all together.

If we can't find a solution soon, we were thinking of just adding a
hyperlink field to each employee's record which identifies a folder with all
of that employee's pertinent scanned files stored.

Any other suggestions or am I missing something? Thanks!

 

[John W. Vinson]

If we can't find a solution soon, we were thinking of just adding a
hyperlink field to each employee's record which identifies a folder with all
of that employee's pertinent scanned files stored.

Even with 2007, I'd still prefer this alternative to storing the objects in
the database. After all, even in 2007 you're still limited to 2GByte overall -
and it wouldn't take too many .jpg, .pdf or .doc files to eat up that much
space!