|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Optimizing Short Text Field Sizes By Richard Rost 
6 years ago
Optimizing Short Text Field Sizes. Is Field Length that Important? Just how important is it to make sure you specify the correct field size for a Short Text field in your Microsoft Access databases? In this video, we'll take a close look. Joel from Reno NV (a Learning Connection Member) asks, "In one of the earlier Beginner lessons you showed how to set field sizes to reduce the size of the database. Like reducing the first name field to 20 from 255, etc. In one of the later lessons, I think you said it was no longer necessary to do that - that now Access keeps the database size down without reducing the field sizes. But I wasn't sure and thought I should double check with you before I start creating my database." MembersThere is no Extended Cut for this video, but watch the Extended Cut for the Compact Importance video! LinksCompact Importance: https://599cd.com/CompactImport
IntroIn this video, we will talk about optimizing short text field sizes in Microsoft Access, and whether it is still necessary to set smaller field sizes for text fields like first names or ZIP codes to save space in your database. We will discuss how Access has changed the way it stores text data over the years, the actual impact of field size settings on database size in modern versions, and when you might still want to specify a smaller field size to control data entry.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's lesson, we are going to talk about text field sizes and how important optimizing your text field sizes is in your databases. Today's question comes from Joel from Reno, Nevada, one of my Learning Connection members. Joel says in one of your earlier beginner lessons, you showed how to set field sizes to reduce the size of the database. It is like reducing the first name field to 20 from 255. In one of the later lessons, I think you said it was no longer necessary to do that. That now Access keeps the database size down without reducing the field sizes. But I was not sure, and I thought I should double-check with you before I start creating my database. Yes, in fact, I remember in some of my older classes, I have said this a lot that you should keep your text sizes small enough to hold the largest bit of data. Back in the 90s, when I started programming with Access, that was important. Not only were hard drives incredibly small, so disk space was at a premium. I started off with a 20 megabyte hard drive when I was working with Access. That is tiny. But Access worked, like a lot of older databases worked, by reserving the number of characters for each field, whether you used it or not. So if you specified, let's say this example here, 14 characters for a first name field, and most of your first names were only five to nine, let's say, well, that is wasted space for each record. Multiply that times a certain number of records, times a certain number of tables in your databases, times how many fields, that is a lot of wasted space. This is how older databases used to work. That is how I was taught. That is when I was learning how to build databases, that is how it worked. Records like this, each field had a fixed width. That is how much space it took, kind of like text files. Remember those old fixed-width text files? Fortunately, in recent versions of Access, that is no longer the case. Access will now reduce the amount of space that is used to store each record to the number of characters. Basically, it is one byte per character in your text fields, plus a little bit of overhead for each record. Kind of like a little header, a little bit of information, almost like a pointer, if you know any programming terminology. So if you leave your first name field at 255 characters, which is the max for a short text field, that is okay. It is not going to really hurt your database that much. In fact, recently I built a database just to test this. I wanted to test how efficient Access stored text information and I wanted to check and see how efficient compacting or repairing is. Now I am putting together a whole different video on compact and repair. But as far as text field efficiency goes, you can see a million records with one character in the field, whether it is a max of one or a max of 255, it took up the same amount of information. So Access does not care what that max is set to anymore. You can see 10 characters took up 23 megabytes. A full 255 took up 273. So it is consistently based on the amount of data that is in that field and not the maximum size of that field. This is something I have been teaching in my classes since the 90s, so I need to rethink the way I do things. That is how I came up when I learned databases to keep those file sizes small. You want to make sure that you are only storing 15 characters for your first name if your longest first name is ever going to be 12 characters, so you do not waste all that extra space. Moving forward, I have got to change that. I like to do the research now. Now that I am a lot more experienced with Access, instead of just going by what I read in books or going by what I was taught, I like to tear Access apart and figure this out. This is the result of my hours and hours of experimentation. So Joel, I hope that answers your question. Go ahead and use 255 characters if you want. The only major reason I can see making that smaller is if you want to limit that data entry in the database. For example, if you want to force your users to only enter two digits or two characters for a state field, for example, or five characters for a zip code, or six if you are in Canada, or a certain length for the Social Security number or a VIN number on a car. If you know it is going to always be that long and you want to limit it, then that is the reason why you could still use a smaller text size. But aside from that, go to town. If you want to use 255, use 255. Now members, I was not kidding when I said I spent a lot of time on this and I recorded all of it for you. I first sat down and I was just going to say, let's just build a database and test and see how the text works and if the compacting is efficient and all that. Well, I went crazy. So I built two different databases. One that you can see on the right there, the little blue guy that just adds the records. How many records do you want to add? What is the length of each record? Go, and I will just put a million records in the database using a record set. There, I will add that to the bullet list. Record sets. Those are pretty cool. Then I built another database to analyze the results from the first database. Basically, you set the first database up to run a loop. It will start with a thousand records at one character. It will open up the other database, make it run, wait for it to close, read the results in. I will show you how to use a sleep function, wait for the database to exit, compact it with VBA because the first database compacts the second one. I do not want to affect the results. How to read and write text files because we use a little text file for the settings to pass information back and forth. How to get file sizes, delete and rename files. It is three hours long. I am preparing it now, but it is related to this video, so I just wanted to mention it at the end. It is for members extended cut. It will be posted soon. How do you become a member if you want to learn more about compact and repair and all that crazy stuff I just mentioned? Click on the join button and you will be given access to all of my extended cut videos. If you are still remembering up, you will see all the options there when you click on the button. But do not worry, my TechHelp videos will always be free and there will be lots more coming. Make sure you like and share this video. Click on the subscribe button and ring the bell if you want to get notifications every time I release a new lesson. If you have not yet tried my free three-hour long beginner Access Level 1 video, then now is the time to go take it. Ignore if I mention in that video. I have to go back and look down and see if I mention in that one that you want to keep your file size small by keeping short text fields smaller. I think I probably did it because I have been teaching it the same way for 26 years now. So this is eye-opening for me. Of course, if you like Level 1, Level 2 is just a dollar and that is free for members also. If you want to see your question answered in a video like this one, go to my TechHelp page. Thanks for watching and we will see you next time. Keep learning. QuizQ1. Why was it important in older versions of Access to set text field sizes as small as possible?A. To save disk space due to fixed-width field allocation B. To improve query speed significantly C. To protect data integrity D. To increase the maximum number of tables Q2. How does Microsoft Access store short text fields in recent versions? A. Only uses space for the actual number of characters entered, plus some overhead B. Reserves space equal to the maximum field size regardless of data C. Converts all text to binary before storing D. Stores all text fields as 255 characters by default Q3. Which scenario would still justify creating a text field smaller than 255 characters in Access? A. To limit and control data entry length B. To decrease the database's maximum table count C. To speed up sorting operations D. To enable encryption options Q4. Richard mentions an experiment with one million records. What did this experiment demonstrate? A. The length of field size has little impact on database size, only data entered matters B. Field size maximum directly increases database file size C. Larger field sizes slow down record entry D. Only numeric fields are optimized for size Q5. When is it now considered unnecessary to reduce text field sizes in Access? A. When space saved is negligible due to how Access stores text data B. When text fields are used for lookups C. When working with Excel imports only D. When building forms instead of tables Q6. What is one byte per character in the context of Access text fields? A. The storage method used for each character in a text field B. The minimum allowed size for a text field C. The maximum compressed size for numeric fields D. A recommended field size for all short text fields Q7. What is one real advantage to specifying a smaller field size, according to the video? A. To restrict user input by limiting character count B. To increase Access startup speed C. To enable faster database backups D. To facilitate easier table linking Q8. What does Richard suggest members can watch for extended content, such as compact and repair demonstrations? A. Member-only extended cut videos B. Live streams on social media only C. Free public TechHelp videos D. Only in-person seminars Q9. Which of the following does NOT result from changing the maximum field size for a short text field in recent versions of Access? A. Direct reduction in database file size B. Better control over data entry (if used intentionally) C. No impact on database size if only a few characters are entered per record D. No significant effect on storage unless used for validation Q10. According to the video, which of the following is an outdated reason for reducing text field sizes? A. To minimize wasted disk space in older database systems B. To help users type faster C. To enhance graphics capabilities D. To make report generation automatic Answers: 1-A; 2-A; 3-A; 4-A; 5-A; 6-A; 7-A; 8-A; 9-A; 10-A DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks. SummaryToday's video from Access Learning Zone covers the importance of choosing appropriate text field sizes in your Access databases. This lesson was inspired by a question from one of my Learning Connection members who wanted some clarification on whether it is still necessary to manually reduce the sizes of text fields in their tables.Back in the early days, especially in the 1990s, disk space was very limited. When I first started with Access, I only had a 20 megabyte hard drive. At that time, databases reserved space for each field's maximum length, regardless of how much data you actually stored in it. So if you set a text field to 14 characters for a first name, even if most names were only five to nine characters long, each record would still use space for all 14 characters. When multiplied by thousands of records and several tables, this led to a lot of wasted space. I was taught, and I taught others, to keep text field sizes small—just big enough to fit the longest item you expected to hold in that field. However, this approach is now outdated because of how Access has changed. In more recent versions, Microsoft Access only uses as much storage as needed for the actual data in each text field, plus a very small bit of extra data for each record. If your field is set to 255 characters, which is the maximum for a short text field, but you only store ten characters in it, Access only uses storage for those ten characters. It is basically one byte per character, with a little overhead for each record. To test this, I created a database with one million records, each with only one character stored. Whether the field's maximum was set to one character or 255, the space used was the same. Similarly, adding more characters increased the file size in a way that directly reflected the number of characters stored, not the maximum possible length of the field. So in practical terms, Access no longer wastes storage space just because a text field has a higher maximum character count. Given these changes, there is no longer a need to keep text fields artificially small to conserve disk space. You can safely use the default size of 255 characters for your short text fields if you like. The only good reason to make a field smaller now is to limit what users can enter for data validation—for example, restricting a state field to two characters, or a zip code field to five or six characters depending on your location. For those who are interested, I have recorded a detailed, three-hour extended lesson for members. In it, I demonstrate how I built two separate databases to analyze how Access handles text field data. The first database generates large numbers of records with different field lengths, while the second analyzes the resulting storage size using VBA automation. I show how to pass information between the databases, gather file statistics, delete and rename files, and automate compacting. This extended cut will be available soon for members. If you want to expand your understanding of topics like compact and repair, becoming a member will give you access to these extended videos. You will find options on my website to join and see membership details. Remember, my regular TechHelp videos will always be free, so expect plenty more free lessons. I encourage you to check out my free three-hour Access Level 1 course if you have not already. If you enjoy that, Access Level 2 is just a dollar and is also included for members. If you would like to see your own Access question addressed in a future video, head over to my TechHelp page to submit your question. You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends. Topic ListImportance of optimizing text field sizes in AccessHow older versions of Access stored text fields Fixed-width vs variable-width text field storage How modern Access versions manage text field storage Impact of field size settings on database file size Testing database file size with different text field lengths When to use smaller text field sizes for data entry limits Best practices for setting text field sizes in Access |
||||||||||||||||
|
| |||
| Keywords: TechHelp Access short text field sizes efficiency optimizing PermaLink Optimizing Short Text Field Sizes in Microsoft Access |
