relationships

6 Jul 2009 12:31 PM

Striker

I've been asked to normalize a database created in Access 2007. Originally
created for personal use, and make it usable for many. One of the main
tables has a PK field that is text 255 in length. Looks like he just
accepted the default value here as no record is longer than 4 letters. I
would like to shrink this from 255 to 6 letters in length to allow for
growth.

Problem is I have to temporarily undo the relationships to do this. So I'm
wondering if there are any unwanted affects to doing this. Will removing a
relationship cause any errors on forms reports? Are there any gotcha's to
look out for?

Of course I will back up and save the DB first. I'm just wondering if I am
right and no PK data is larger than 4 letters, can I remove relationships,
shrink the field size, and replace relationships without any ill effects?

6 Jul 2009 1:16 PM

Jerry Whittle

Don't bother. Access doesn't waste space if the field size is larger than
need be. If only 4 characters are put into the field, only 4 are used and not
the 255.

About the only down side is that forms and reports created by a wizard look
at the field size and might make the text box for it much larger than needed.

Another down side is that someone could type in too many characters. If the
PK is only 6 characters max, then setting the field size to 6 would prevent
such a gross error.

--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Show quoteHide quote

"Striker" wrote:

> I've been asked to normalize a database created in Access 2007. Originally
> created for personal use, and make it usable for many. One of the main
> tables has a PK field that is text 255 in length. Looks like he just
> accepted the default value here as no record is longer than 4 letters. I
> would like to shrink this from 255 to 6 letters in length to allow for
> growth.
>
> Problem is I have to temporarily undo the relationships to do this. So I'm
> wondering if there are any unwanted affects to doing this. Will removing a
> relationship cause any errors on forms reports? Are there any gotcha's to
> look out for?
>
> Of course I will back up and save the DB first. I'm just wondering if I am
> right and no PK data is larger than 4 letters, can I remove relationships,
> shrink the field size, and replace relationships without any ill effects?
>
>

6 Jul 2009 4:04 PM

John W. Vinson

Show quote Hide quote

On Mon, 6 Jul 2009 05:31:48 -0700, "Striker" <striker3***@qwest.net> wrote:

>I've been asked to normalize a database created in Access 2007. Originally
>created for personal use, and make it usable for many. One of the main
>tables has a PK field that is text 255 in length. Looks like he just
>accepted the default value here as no record is longer than 4 letters. I
>would like to shrink this from 255 to 6 letters in length to allow for
>growth.
>
>Problem is I have to temporarily undo the relationships to do this. So I'm
>wondering if there are any unwanted affects to doing this. Will removing a
>relationship cause any errors on forms reports? Are there any gotcha's to
>look out for?
>
>Of course I will back up and save the DB first. I'm just wondering if I am
>right and no PK data is larger than 4 letters, can I remove relationships,
>shrink the field size, and replace relationships without any ill effects?

You can; if it's a shared database just be sure you're the only one who has it
open at the time.

I agree with Jerry that this change won't make any substantial difference (it
won't affect the size of your database one iota, for instance), but just to
have things clean I'd do it anyway. Your instinct on backups is, of course,
right on the money.

--

John W. Vinson [MVP]