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

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
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jürgen Welz
Sent: Monday, December 05, 2005 1:55 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Why Change Field Size/was Change Field Size


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