Attachment Field in Access 2007

Hi! I'm fairly new to Access (I'm on my seventh database) so forgive a
fairly basic design question.

I'm designing a database that will be responsible for tracking about 200
documents submitted from teachers around the world. Most of them are one or
two page word documents. Some are small excel or publisher files. We have
the occasional .pdf or website. The documents will have a lot of "stuff"
that needs to be tracked related to each one:
-copyright checks
-initial review (resulting in a new word doc ),
-some will need reviews from a group of internal or external experts (they
will have "review forms" (new doc)- each review form having feedback about
the document and revisions to the document. we'll get about 4 filled out
review forms for each document)
-revised document (that will eventually go live on a resources webpage)
-some versions will need to be converted from their original formats-
resulting in .pdf docs that should stay with the original
-some will need instruction sheets

Needless to say, this is a helluva lot of documents to keep track of. What
do I do with them all? I've been eyeing the "Attachments" field longingly but
fear to begin using it due to database bloat. I've seen postings that say
don't use it for photos, but no comments about how much space the word
documents would use. Can anyone tell me if i can get away with it? How many
Word docs before I fill up my 2 GB?

Are there any other useful ways of tracking lots of documents without
requiring the user to update the location of the document each time they move
it?

THANK YOU!!
~Becca

Hello Becca,

I noticed that nobody answered. There are actually several quesitons and
items in your post, here are thought / notes on just a few of them.

As a sidebar note, of the 3 or 4 reasons for not OLE'ing images in tables,
Access 2007 solved one of them - exponential bloat = bloat far larger than
the image size.

I think that you may have gone off on a tangent when you started thinking
about "attaching" documents, and a particular (unique-to-Access 2007) way to
"attach" documents.

There is also an apparent cloflict that need clarification - that of
documents being "inside" the DB (and thus not independently movable by other
people) vs. documents being independently movable by other people
and thus not "inside" the DB.

I would start by thinking about the nature, structur, organizaiton of and
relationships between the entities are that you want to database. A few more
questions to clarify for yourself if not for us:

It sounds like the initial "document" is the core item that everything else
is linked to? Or is everything linked to the latest version of that
document? And, as I understand it, some of these initial documents aren't
documents or even files at all - some are entire web sites, oor references to
such?

- - - -

We do a lot of work here that involves interleaving databased (tabular
type) information as well as entire electronic and paper documents. In our
case, we just create nique document numbers for the documents, and just
track, organization and manage them (and relationships between them) in our
databases.

Each situation is different.










"AngelShadow" wrote:

Hi! I'm fairly new to Access (I'm on my seventh database) so forgive a
fairly basic design question.

I'm designing a database that will be responsible for tracking about 200
documents submitted from teachers around the world. Most of them are one or
two page word documents. Some are small excel or publisher files. We have
the occasional .pdf or website. The documents will have a lot of "stuff"
that needs to be tracked related to each one:
-copyright checks
-initial review (resulting in a new word doc ),
-some will need reviews from a group of internal or external experts (they
will have "review forms" (new doc)- each review form having feedback about
the document and revisions to the document. we'll get about 4 filled out
review forms for each document)
-revised document (that will eventually go live on a resources webpage)
-some versions will need to be converted from their original formats-
resulting in .pdf docs that should stay with the original
-some will need instruction sheets

Needless to say, this is a helluva lot of documents to keep track of. What
do I do with them all? I've been eyeing the "Attachments" field longingly but
fear to begin using it due to database bloat. I've seen postings that say
don't use it for photos, but no comments about how much space the word
documents would use. Can anyone tell me if i can get away with it? How many
Word docs before I fill up my 2 GB?

Are there any other useful ways of tracking lots of documents without
requiring the user to update the location of the document each time they move
it?

THANK YOU!!
~Becca

Hi, Fred!

Thank you so much for answering! And for your questions in return!

Everything is tied to a document ID- That is created when we receive the
initial document, but the ID actually refers to everything necessary for that
document- it's original version, it's descriptors in the database, it's
revisions, etc. Every document we create or revise all use that ID number.
However, they are currently stored in many folders that are outside of the
database. My initial plan was to track where they are in the database but
they are moving around too fast and people aren't updating where they are
moving the files to. I want to bring the files into the database so they
will not be independently movable. However, I'm not sure the database can
handle so many files! I'm sure with the OLE attachment type it would be
overwhelmed. But what about the Attachment type?

Thanks again!
~Becca

Fred wrote:
Hello Becca,

I noticed that nobody answered. There are actually several quesitons and
items in your post, here are thought / notes on just a few of them.

As a sidebar note, of the 3 or 4 reasons for not OLE'ing images in tables,
Access 2007 solved one of them - exponential bloat = bloat far larger than
the image size.

I think that you may have gone off on a tangent when you started thinking
about "attaching" documents, and a particular (unique-to-Access 2007) way to
"attach" documents.

There is also an apparent cloflict that need clarification - that of
documents being "inside" the DB (and thus not independently movable by other
people) vs. documents being independently movable by other people
and thus not "inside" the DB.

I would start by thinking about the nature, structur, organizaiton of and
relationships between the entities are that you want to database. A few more
questions to clarify for yourself if not for us:

It sounds like the initial "document" is the core item that everything else
is linked to? Or is everything linked to the latest version of that
document? And, as I understand it, some of these initial documents aren't
documents or even files at all - some are entire web sites, oor references to
such?

- - - -

We do a lot of work here that involves interleaving databased (tabular
type) information as well as entire electronic and paper documents. In our
case, we just create nique document numbers for the documents, and just
track, organization and manage them (and relationships between them) in our
databases.

Each situation is different.



Hi! I'm fairly new to Access (I'm on my seventh database) so forgive a
fairly basic design question.
[quoted text clipped - 28 lines]
THANK YOU!!
~Becca

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...esign/200909/1

Hi Becca,

I would have a few more things to say or ask, but on your main question
(feasibility of using Access 2007 attachments fields to store lots [sounds
like 1,000 or so for 200 "documents") of files/documents inside the DB, I
don't know the answer.

One note, so far it looks like the main reason you want to put them INSIDE
the DB is because that's the only way you can keep people from messing with
them? Of all of the zillion ways I've heard of to keep people from messing
up files, that's the first time I've heard this one.

Sincerely,

Fred

In message 9c099a94a5be5@uwe, AngelShadow
writes

Hi! I'm fairly new to Access (I'm on my seventh database) so forgive a
fairly basic design question.

I'm designing a database that will be responsible for tracking about 200
documents submitted from teachers around the world. Most of them are one or
two page word documents. Some are small excel or publisher files. We have
the occasional .pdf or website. The documents will have a lot of "stuff"
that needs to be tracked related to each one:
-copyright checks
-initial review (resulting in a new word doc ),
-some will need reviews from a group of internal or external experts (they
will have "review forms" (new doc)- each review form having feedback about
the document and revisions to the document. we'll get about 4 filled out
review forms for each document)
-revised document (that will eventually go live on a resources webpage)
-some versions will need to be converted from their original formats-
resulting in .pdf docs that should stay with the original
-some will need instruction sheets

What you are describing is a document management system. It may be
simpler to implement a real DMS rather than try to build one in Access.
If you have Windows server systems available you might like to take a
look at what SharePoint can do.


Needless to say, this is a helluva lot of documents to keep track of. What
do I do with them all? I've been eyeing the "Attachments" field longingly but
fear to begin using it due to database bloat. I've seen postings that say
don't use it for photos, but no comments about how much space the word
documents would use. Can anyone tell me if i can get away with it? How many
Word docs before I fill up my 2 GB?

Are there any other useful ways of tracking lots of documents without
requiring the user to update the location of the document each time they move
it?

You can use a version control system like subversion to control who has
access to documents. The VCS would hold master copies of each document
and could control who has the authority to make changes to them.



--
Bernard Peek