relationships

[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?

 

[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.

 

[John W. Vinson]

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.