I am in the very beginning stages of developing a database for someone
in sort of an HR function in our organization. She needs to be able to
keep track of various employment qualifications - certification dates,
drivers license status, etc. The tricky request she has given me is
that she wants to click on a button/record/somesuch for an employee,
and have it bring up the appropriate scanned image, such as a scan of
the drivers' license, and then print it. I was thinking of using the
attachments feature in Access 2007 (which i will be developing in),
but saw that the attachment total for the database would be limited to
2GB. As she is dealing with around 500 employees, I think we'll use up
that allotment rather quickly.

Did I misunderstand the limitation? Or does anyone have a better idea
on how I might deal with this? I have *not* started any table design
at this point - I want to figure out how I might do this part first,
and get going on the design from there.

Thanks!
Amy

You've understood correctly. Although written in an earlier version, the
sample database below can be imported in whole or part into an Access 2007
database. It will do exactly what you have described with external objects.

http://www.datastrat.com/Download/Picture2K.zip
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"LadyIlsebet" <(E-Mail Removed)> wrote in message
news:a7070f83-f21c-4867-ba64-(E-Mail Removed)...
>I am in the very beginning stages of developing a database for someone
> in sort of an HR function in our organization. She needs to be able to
> keep track of various employment qualifications - certification dates,
> drivers license status, etc. The tricky request she has given me is
> that she wants to click on a button/record/somesuch for an employee,
> and have it bring up the appropriate scanned image, such as a scan of
> the drivers' license, and then print it. I was thinking of using the
> attachments feature in Access 2007 (which i will be developing in),
> but saw that the attachment total for the database would be limited to
> 2GB. As she is dealing with around 500 employees, I think we'll use up
> that allotment rather quickly.
>
> Did I misunderstand the limitation? Or does anyone have a better idea
> on how I might deal with this? I have *not* started any table design
> at this point - I want to figure out how I might do this part first,
> and get going on the design from there.
>
> Thanks!
> Amy

I don't use Access 2007 but I'd have to believe that the attachment
limit of 2G refers to a single attachment instance.

HTH
--
-Larry-
--

"LadyIlsebet" <(E-Mail Removed)> wrote in message
news:a7070f83-f21c-4867-ba64-(E-Mail Removed)...
> I am in the very beginning stages of developing a database for
someone
> in sort of an HR function in our organization. She needs to be able
to
> keep track of various employment qualifications - certification
dates,
> drivers license status, etc. The tricky request she has given me is
> that she wants to click on a button/record/somesuch for an employee,
> and have it bring up the appropriate scanned image, such as a scan
of
> the drivers' license, and then print it. I was thinking of using the
> attachments feature in Access 2007 (which i will be developing in),
> but saw that the attachment total for the database would be limited
to
> 2GB. As she is dealing with around 500 employees, I think we'll use
up
> that allotment rather quickly.
>
> Did I misunderstand the limitation? Or does anyone have a better
idea
> on how I might deal with this? I have *not* started any table design
> at this point - I want to figure out how I might do this part first,
> and get going on the design from there.
>
> Thanks!
> Amy

Amy:

Some years ago I produced a demo file for this sort of image handling. I
think the original version is still available online in the libraries of the
forum for which it was produced, but its been changed quite a lot since then.
It works by storing the paths to the image files and loading them into
lightweight image controls at runtime. The demo takes the form of a simple
contacts database, and multiple images can be attached to each contact and
are listed in a list box along with the contact data on a form. Reports of
selected multiple images per contact can be printed, or individual images can
be printed.

If you'd like a copy of the file mail me at:

kenwsheridan<at>yahoo<dot>co<dot>uk

The file is in Access 2002 format, but should be convertible to later
versions.

Ken Sheridan
Stafford, England

"LadyIlsebet" wrote:

> I am in the very beginning stages of developing a database for someone
> in sort of an HR function in our organization. She needs to be able to
> keep track of various employment qualifications - certification dates,
> drivers license status, etc. The tricky request she has given me is
> that she wants to click on a button/record/somesuch for an employee,
> and have it bring up the appropriate scanned image, such as a scan of
> the drivers' license, and then print it. I was thinking of using the
> attachments feature in Access 2007 (which i will be developing in),
> but saw that the attachment total for the database would be limited to
> 2GB. As she is dealing with around 500 employees, I think we'll use up
> that allotment rather quickly.
>
> Did I misunderstand the limitation? Or does anyone have a better idea
> on how I might deal with this? I have *not* started any table design
> at this point - I want to figure out how I might do this part first,
> and get going on the design from there.
>
> Thanks!
> Amy
>

Hi Amy,

I'm sure you will several fine examples - I saw that Ken Sheridan you offered a sample database - take advantage of that offer; he's an excellent programmer.

WIth massive data, I would store the path and use a naming convention for images and assorted documents that can be programmatically generated, instead of attaching or embedding large objects in quantities.

This way you can update a given control with code to point to a single instance of a 500k or 2 meg file at runtime rather than attaching to 500 of them.

The click event attaches the item to a control or opens up an external handler (Acrobat Reader for instance) as appropriate for a given document type.

I've seen a couple rather complex databases that contains an enormous amount of references to scanned project documents constructed this way, one had around 35 gigs of scanned pages last December and grows at the rate of 10-25 megs a day - not sure what the effective limits are but the hard drive system (raid storage) wll fill up before Access tops out.

Each document type is placed in a known folder for the general category of document, given a name that concatenates the information required to uniquely identify the project, the date, the job phase, the generic document type (inspection requested, RFP, certificates of inspection, additional work, whatever - any specific information required to uniquely identify the document in a manner that can be programmatically reproduced. Once you work out the naming convention, it's fairly easy to build the name and snag the document on demand without having to be concerned about Access's internal storage.

Access's two gig limit is a hard-coded addressing limitation, a maximum - not a recommended operating condition. Access gets a bit squirrelly well short of that limit, depending on how you are using the database.

As a side note: hard drives are cheaper than managing the data efficiently, for the items you discussed . At times, it's cheaper to store the same <static content> document in two or three folders than it is to 'break' the folder logic with exceptions, when a static document legitimately fits in multiple categories, put it in all of them and keep on trucking. What matters is the user finding the doc when they go looking for it.

Hope this helps,
Gordon










"LadyIlsebet" <(E-Mail Removed)> wrote in message news:a7070f83-f21c-4867-ba64-(E-Mail Removed)...
>I am in the very beginning stages of developing a database for someone
> in sort of an HR function in our organization. She needs to be able to
> keep track of various employment qualifications - certification dates,
> drivers license status, etc. The tricky request she has given me is
> that she wants to click on a button/record/somesuch for an employee,
> and have it bring up the appropriate scanned image, such as a scan of
> the drivers' license, and then print it. I was thinking of using the
> attachments feature in Access 2007 (which i will be developing in),
> but saw that the attachment total for the database would be limited to
> 2GB. As she is dealing with around 500 employees, I think we'll use up
> that allotment rather quickly.
>
> Did I misunderstand the limitation? Or does anyone have a better idea
> on how I might deal with this? I have *not* started any table design
> at this point - I want to figure out how I might do this part first,
> and get going on the design from there.
>
> Thanks!
> Amy

That is true, but since the entire database has the same limit, Amy's
concern is very real. The biggest concern should be however is that the
attachment is not an internal control to display anything, and requires a
program association to open the file. The very same thing can be realized by
simply providing a path to any file that has a registered file type.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"Larry Daugherty" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
>I don't use Access 2007 but I'd have to believe that the attachment
> limit of 2G refers to a single attachment instance.
>
> HTH
> --
> -Larry-
> --
>
> "LadyIlsebet" <amy.s.cooper@.com> wrote in message
> news:a7070f83-f21c-4867-ba64-(E-Mail Removed)...
>> I am in the very beginning stages of developing a database for
> someone
>> in sort of an HR function in our organization. She needs to be able
> to
>> keep track of various employment qualifications - certification
> dates,
>> drivers license status, etc. The tricky request she has given me is
>> that she wants to click on a button/record/somesuch for an employee,
>> and have it bring up the appropriate scanned image, such as a scan
> of
>> the drivers' license, and then print it. I was thinking of using the
>> attachments feature in Access 2007 (which i will be developing in),
>> but saw that the attachment total for the database would be limited
> to
>> 2GB. As she is dealing with around 500 employees, I think we'll use
> up
>> that allotment rather quickly.
>>
>> Did I misunderstand the limitation? Or does anyone have a better
> idea
>> on how I might deal with this? I have *not* started any table design
>> at this point - I want to figure out how I might do this part first,
>> and get going on the design from there.
>>
>> Thanks!
>> Amy
>
>

On Feb 26, 8:25 am, "Arvin Meyer [MVP]" <a...@m.com> wrote:
<snip>The very same thing can be realized by
> simply providing a path to any file that has a registered file type.</snip>

Thanks, folks I think this is exactly what I'm going to do - use
the hyperlink field type to store the location of the image. They are
going to get scanned in anyways, and what I can do I think is use the
default value for the field to point to the directory where the file
will be, so that the end user can just append the file name. She will
probably be changing the file names to something that makes sense for
her, but using the hyperlink will make it easy for her to click and
view/print the file.

All the help is MUCH appreciated!

Amy

You will save overhead by using the text datatype instead of a hyperlink.
Using the ShellExecute API, you can do everything in a hyperlink (and more):

http://www.mvps.org/access/api/api0018.htm

But, by using the Picture property, you can display the image directly in a
form or report. The sample app, I mentioned earlier:

http://www.datastrat.com/Download/Picture2K.zip

demos exactly how to do this.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

"LadyIlsebet" <(E-Mail Removed)> wrote in message
news:90931dec-c0ac-4291-9e53-(E-Mail Removed)...
> On Feb 26, 8:25 am, "Arvin Meyer [MVP]" <a...@m.com> wrote:
> <snip>The very same thing can be realized by
>> simply providing a path to any file that has a registered file
>> type.</snip>
>
> Thanks, folks I think this is exactly what I'm going to do - use
> the hyperlink field type to store the location of the image. They are
> going to get scanned in anyways, and what I can do I think is use the
> default value for the field to point to the directory where the file
> will be, so that the end user can just append the file name. She will
> probably be changing the file names to something that makes sense for
> her, but using the hyperlink will make it easy for her to click and
> view/print the file.
>
> All the help is MUCH appreciated!
>
> Amy