[AccessD] Why Change Field Size/was Change Field Size

Jürgen Welz jwelz at hotmail.com
Mon Dec 5 14:51:58 CST 2005


Jim:

That sounds awfully like what I remember.  Indexes get their own data pages.

I generally test a table record afer modification by filling all fields to 
capacity and making sure the record saved.  If it didn't, it was a matter of 
running a loop that decreased the data in the text fields a character at a 
time and then a field at a time until the record saved.  Then the validation 
ensured the combined text length didn't exceed the length determined by the 
test.  Rather than limit the field size, the validation routine dumps the 
overflow in a memo field headed by the field name source for each field that 
had to be truncated in order to permit the record save.

I first got into record size determination as a means to providing record 
level locking in Access 97 for a few special tables where it was probable 
that users would run into page lock issues and found the padding calculation 
less prone to grief the padding started a couple of characters high and cut 
it back in a loop until the write succeeded.

In the example, limiting each field to 90 character (you said 'bytes' where 
you meant characters) is of course too arbitrary, but it does serve to 
illustrate the problem.


Ciao
Jürgen Welz
Edmonton, Alberta
jwelz at hotmail.com





>From: "Jim Dettman" <jimdettman at earthlink.net>
>
>Let's see how good my memory is...
>
>   8 - Bytes for record overhead
>  19 - Date fields are really a double (8 bytes each plus 1 byte per field)
>  27 - Longs are 8 bytes plus one overhead
>   2 - Boolean is 1 plus 1 overhead
>  16 - Memo - only a pointer to the start of the chain resides in the 
>"fixed"
>portion of the record, unless the data is less then 65 bytes, at which 
>point
>it's stored in the fixed portion (JET 4.0).  Jet 3.5 was 32, Jet 3.0 and up
>stores a 16 byte pointer, and prior versions it was a 14 byte pointer.
>  24 - Text fields - one byte overhead.
>
>   Indexes don't matter and page size is 4K for Jet 4.00
>
>   Page size: 4096 - 96 leaving 4000 bytes for data.  Unicode uses two 
>bytes
>per character, so 2000 bytes/22
>
>   Each field could hold 90 bytes before "record too large" would occur.
>
>Jim.
>
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>
>In Drew's case, this is not necessarily an issue as he has a demonstrated
>propensity not to use bound controls.
>
>Queries, and particularly update or insert queries do not fail because of a
>field size limit any differently than in subseqent versions of Access.  
>They
>fail because users will use things like phone number fields that are 
>limited
>to 255 characters to hold a dozen phone numbers and include descriptions
>such as 'His wife's vacation phone number for January 2006 - 1 900 555 
>5555'
>and they do this in a sufficent number of fields that the record page size
>limit is exceeded.
>
>Data validation that includes record size limit restrictions at the user
>interface should preempt such problems.
>
>Pop quiz, if you have a table that permits two date fields, indexed, 3
>longs, 2 indexed a boolean field, a memo field and 22 text fields, 2
>indexed, how many characters can you store in all 22 text fields before you
>can't save the record?  What if 3 of the text fields are indexed?  Clue, 
>you
>used to get fewer characters in total as more are in indexed fields and I
>haven't bothered refiguring as our BE version changed in the rather foolish
>hope that MS didn't make this worse.  Oh, and give it a try with unicode.
>You'll need to know these things if you are doing record size validation.
>
>
>Ciao
>Jürgen Welz
>Edmonton, Alberta
>jwelz at hotmail.com
>
>
> >From: "Charlotte Foust" <cfoust at infostatsystems.com>
> >
> >I've had queries go belly up because every field was 255, but that was
> >in 97.  This is one of those issues that has caused a great deal of
> >"discussion" in the list, so you can check the archives to see the
> >arguments pro and con.  If you design a field to hold a specific value
> >size, then your controls will limit the value for you with less work by
> >the programmer.  If you allow them to enter 255 characters into a field
> >that is only supposed to contain a social security number, then you have
> >to do extra programming to make sure that all you get is a social
> >security number.  I suppose my objection to making them all maximum
> >length is that it's sloppy programming and suggests that you haven't
> >really thought out the design of the table.
> >
> >Charlotte Foust





More information about the AccessD mailing list