Evaluate a table's text fields and alter accordingly

[NauticalGent]

Good morning folks,

I recently was given a db to look into. Aside from a few other issues, there are about 50 tables and ALL of the short text fields are sized at 255. I wrote a quick sub that looped through the TableDefs and determined the max length of the data in each field.

I then attempted to alter each field's length with VBA but ran into some issues - I have to figure out a way to "release" the table after I have evaluated it before I can alter it.

But before I go down that rabbit-hole, as anyone else done anything like this? If so I would be very interested in hearing what you have to say on this.

Thanks in advance!

 

[theDBguy]

Just curious, why do you want to do that? I think Text fields do not occupy any more space than what they contain.

 

[CJ_London]

Agree with DBG - but if you want to go ahead, suggest using the alter table sql

 

[NauticalGent]

Just curious, why do you want to do that? I think Text fields do not occupy any more space than what they contain.

Ok, did not expect that answer! I've always thought they shouldn't be any larger than necessary. My concern was for performance more than being tidy, but if Devs of yours and CJ's caliber are questioning it, I guess I should reconsider my thoughts of this.

Agree with DBG - but if you want to go ahead, suggest using the alter table sql

Alter table SQL is what I used and it works, but when I try it after evaluating the table's fields, it fails because the table is still locked.

Since you two say the juice isn't worth the squeeze, thanks BTW, I will address more important issues. There are many!

 

[The_Doc_Man]

The last time I saw a definitive article on the subject, which was YEARS ago - when I was still with the Navy - the consensus was that the 255 byte size for Short Text fields was a MAXIMUM, not an allocation. The way I remember it, and this is through the filter of being somewhat older now, all text fields (short or long) do NOT appear in the middle of the record. The FieldDefs for the given record lay out numeric fields in the order they are declared. Text fields are ALWAYS stored as a type of string descriptor that includes a relative position and a size. The string descriptors point AFTER the actual record to the actual bytes of the string. Doing it that way means you don't have to have a record where the position of numbers depends on whether there was or wasn't a text field defined earlier. It is ONE of the reasons (but not the only one) for records in a table changing position after an update. There is some debate on the case of shortening the strings, but there is no debate that if the string becomes longer, you HAVE to rewrite the entire record.

Here is a link to a relevant discussion: https://www.access-programmers.co.uk/forums/threads/understanding-text-record-memory-usage.318503/

 

[Pat Hartman]

My understanding also is that text fields are variable length so I only provide a specific, shorter, length if I want to limit the input to a specific number of characters. If you have a very large set of data with a number of text fields of varying length and the time to test, you could compare the gross size of the db with all text fields defined as 255 and all text fields defined as much shorter lengths.

 

[jdraw]

John,
I think the issue is that 255 will only store the number of characters entered. And 255 is max for short text. If you only store 25 characters, that's what is used. The problem is the "other" side of the question -- if you say the short text is, say 10, then trying to add additional characters (beyond 10) is problem.