Hello all. I am in the process o 1

[tdion]

Hello all.

I am in the process of upgrading an MS database. The text fields in the old database are 200 characters large. I want to count the largest entry in the fields and make the new fields (replacing the old one) 10 characters larger.

For example, if the largest text entry in a field is "hello world" then the field size is 11, and I will make the new field 21. This will cut the size of the database 179 characters per record. The extra 10 characters are a safety net for new records.

Does anyone have a freeware tool to show the largest entry in a field or have a script? I'm not a VB programmer.

 

[LarryDeLaruelle]

Create a query based on the table where the field reside.

In a field cell type:

Size: Len([fldName])

replacing fldName with the appropriate name.

Set the sort for this column to descending.

Run the query.

The largest entry should sort to the top.

Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[WildHare]

Actually, unless you are an absolute purist, this is unnecessary. Jet uses variable length records, so you won't really "save" any room. Even if you have every text field in your database set to 255 characters, if you store 3 characters, that's all there is to it...

Jim

If at first you don't succeed, skydiving probably isn't for you!
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare.htm

 

[dhookom]

There is really no "size" reason to be concerned about. Access will only use the bytes required to store the actual data values.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[cmmrfrds]

Are you sure that Access will use the space in a text field that has no content on the right? Does Access store the full defined size of the text field? It may shrink the unused space out when it stores the record. I don't know the answer but I would ask that question before going through the trouble of trying to account for it. I hope that Access is advanced enough to do this.

 

[tdion]

Thanks for the responses. Are you sure about that WildHare?

I've heard that Access stores unused spaces (as " " chars) from more than one source.... thus making it a bloating DB concern....

 

[WildHare]

Jet uses variable length records. Try it and see. Create a table - fill it with about 10 255-character text fields.

Store a single character in each field of a record.

Slap in about 10K records.. (Copy and Paste is good for this - you can double the size with each C/P operation.)

Do a compact and repair, and check the size of the db.

Then redesign the table to be 1-character fields.

Compact and Repair.

Check the size again...

I just did this and the size did not change when I shortened every field from 255 to 1 character.



Don't be sexist - Broads hate that!Another free Access forum:

http://www.forumco.com/jmhare

More Access help stuff at

http://www.jmhare.com/wildhare.htm