Hi!
I just had to test myself too, out of curiosity (well, like to test things to be convinced too;-)). I've also been told (and believed) that the field sizes determines the space allocation/usage, but, both by Dhokom, alr0 and my own test, I think I'll have to abandon that view, at least when dealing with Access.
BNPMike, did do any testing? Do you have any interesting results? The please enlighten us. Your first question, what is “completely illogical”, that the databases really are the same size, or the assumption that since they are the same size, the fieldsize does not matter? As to your second question, this discussion has not been about Access storing 255 characters nomatter fieldsize. It's been related to that it doesn't matter for the size of the DB what your field size is, Access seems to store only the characters actually in the field, setting a fieldsize less than number of characters you want to store of course cut's it off.
Testenvironment:
WinXP/AXP, 1 table, 1 field, no indexes, using Dhokom's code to append 1 000 000 records with the letter "a". First example, the field size of the three databases where 1, 10 and 255 characters (as in the heading below).
All sizes in KB
[tt]
Field size 1 10 255
Before adding 112 112 112
After adding 15 812 15 812 15 812
After compact 15 820 15 820 15 820[/tt]
Using decompile did not do any further size alterations, but I'm a bit curious to why compact/repair added 8KB. Also a bit curious, 1 mill “a”'s should equal 1MB storage, what are the other 14.8 MB's?
Further testing, now appending "abcdefghij" in the two latter bases, emptying them first.
[tt]
Field size 10 255
Before adding 112 112
After adding 22 724 22 724
After compact 22 732 22 732[/tt]
Again I'm a bit uncertain as to why compact/repair adds 8KB.
Last test, this time again with 1 000 000 records, appending the previous string * 10 (=100 characters) into the databases, now having field sizes 100 and 255.
[tt]
Field size 100 255
Before adding 116 116
After adding 111 220 111 220
After compact 111 244 111 244[/tt]
Start size is a bit larger, probably because I've altered the code.
If field size matters to the allocation/size of db, I would expect there to be significant differences in size when storing the exact same date in two/three different databases with different field size. There are no difference at all in size. I don't dare to make a conclusive statement, but I think this little test supports everything said by Dhokom and alr0.
Still, there are some troubling thingies to me:
1 - should this knowledge alter the way one decides upon fieldsizes of text fields?
2 - back to tdion's original question - how is additional data stored (when adding a third character in a field which originally had 2 characters)?
Per 1 - We've only discussed the storage of the data. Would the field size on BE tables effect the network traffic? (Would there be more traffic if retrieving a table with fields having field size 255 vs field size 1 - or would also the network traffic be related to actual size/number of characters per field per record? I'm not able to do any testing on that, would be nice if someone could) Another thing to consider when deciding upon field sizes, is the possibility of your app being upsized/upscaled to run on another database. I've had more then one app, originally intended to be running only on access, but when the number of users increased -> SQL server or other db's. I think other databases might use field size in allocating space, so it might be worth considering.
Per 2 - I haven't any Idea, but I'd believe the compact/repair should do some housecleaning of that type (it should at least reindex the db)
Roy-Vidar
