Results 1 to 11 of 11
  1. #1
    Athar Khan is offline Advanced Beginner
    Windows 7 32bit Access 2013 32bit
    Join Date
    Mar 2017
    Posts
    63

    Text Field Size


    Does it make any difference (in DB size) if I set the text field size to less than 255 (for example 55 only) in Microsoft Access? Doing so, Can I save some bytes if I have enough amount of repeating data in that text field?

  2. #2
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    There is *no* penalty for declaring 255. Access will only use what you put in there. It does not reserve the space.

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by RuralGuy View Post
    There is *no* penalty for declaring 255. Access will only use what you put in there. It does not reserve the space.
    Not to hijack the thread but I've been wondering about this: does access reserve space for other data types that are left blank, nulls? For strings what if the value *was* 255 chars and the user changed it to something shorter, is the extra space still used? Does a compact/repair come in to play here?

  4. #4
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Access does reserve for some data types: numeric fields for example. Nulls are simply undefined so what is to reserve? I believe the data engine deals with the other issues properly but I don't know the exact answers.

  5. #5
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by RuralGuy View Post
    Access does reserve for some data types: numeric fields for example. Nulls are simply undefined so what is to reserve?
    This is what I meant, if I have a field of numeric datatype, not required, and the user simply never inputs a number into that field, does the database still reserve space for that numeric even though it's null at the moment? It just seems like if it didn't then that could lead to fragmentation issues, but I know next to nothing of what I'm talking about so that's why I ask.

  6. #6
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    I'm sorry but I don't know the answer to your question. Is that subject causing you some sort of issue? If so, can you describe it for us?

  7. #7
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Quote Originally Posted by RuralGuy View Post
    Is that subject causing you some sort of issue? If so, can you describe it for us?
    Nope, just trying to get a feel for how it works under the hood. I thought the questions correlated well with the original post. Better understanding can only lead to better design, right?

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    You could always experiment - create a db with 100k records, then add a numeric field and see if the db increases in size.

  9. #9
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    1,142
    Thanks for the suggestion.

    I created a table with an ID autonumber field, a string field f1 and a numeric (byte) field f2. Used vba to populate the table with 100,000 rows. After each step below I ran a compact & repair before I recorded the file size.

    The initial database, with no values in f2 was 3,792,896 bytes
    Updating the f2 field of every record in the table to 128: 3,780,608 bytes (less than initial ??)
    Updated the f2 field of every record in the table to random: 3,780,608 bytes
    Deleted f2 entirely, then added f3 currency datatype but didn't add any data: 4,083,712 bytes
    Changed f3 to integer datatype: 3,883,008 bytes

    So it would appear that the database does reserve space for numeric datatypes even if they are null.

    --EDIT--
    I did a little further experimenting in regard to text fields per OP.

    Created a table with an ID autonumber field and a string field f1, max length 255. created 100k rows.
    I initialized the f2 fields all with 26 chars, database size: 5,419,008 bytes
    I updated all f2 field to each contain 254 chars: 28,368,896 bytes
    I updated all f2 fields to 1 char (PRE compact & repair): 28,401,664 bytes
    And after compact & repair: 2,768,896 bytes

    So it would appear that if a user edits a value of a text field that the database will still use up the space it took to hold the extra chars from before until one runs a compact/repair.

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    don't forget indexes will also add to file size - and text indexes are considerable larger than numeric.

  11. #11
    RuralGuy's Avatar
    RuralGuy is offline Administrator
    Windows 10 Access 2013 32bit
    Join Date
    Mar 2007
    Location
    8300' in the Colorado Rocky Mountains
    Posts
    12,922
    Thanks for the experiments. The results are as I would have expected.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. how to change text field size from 20 to 26
    By Azariah in forum Database Design
    Replies: 4
    Last Post: 07-02-2015, 09:15 AM
  2. Table Text Field Size Implications
    By thebigthing313 in forum Database Design
    Replies: 3
    Last Post: 12-05-2014, 12:01 PM
  3. Rich text - text color and size
    By Subs in forum Reports
    Replies: 3
    Last Post: 11-08-2013, 10:36 AM
  4. Replies: 2
    Last Post: 03-20-2013, 04:25 PM
  5. Replies: 1
    Last Post: 04-29-2010, 05:15 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums