Jim Dettman
jimdettman at earthlink.net
Mon Dec 5 14:36:45 CST 2005
Couple minor corrections: 7 - 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. 22 - Text fields - one byte overhead. Total: 93 bytes Page size: 4096 - 93 leaving 4003 bytes for data. Unicode uses two bytes per character, so 2001 bytes/22 Still comes out 90 characters.... Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Jim Dettman Sent: Monday, December 05, 2005 2:58 PM To: Access Developers discussion and problem solving Subject: Re: [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 -- AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com