Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

How critical is text size?

Status
Not open for further replies.

Isadore

Technical User
Feb 3, 2002
2,167
US
I've been working with Access for many years now -- and the "horror" stories that I have heard about Access as a "reliable" database I have discounted -- my experience, for databases where the largest table is say, 50,000 records, is that Access is in fact fast and reliable (not to mention easy to use, user friendly window features, etc...)

One of the many 'horror' stories I have heard is the "truncation" of text fields. Somehow (lack of compaction? clean programming???) Access at some point (from what I've heard) "truncates" text fields, e.g.,

Now is the time for all good men...(say in a 255 textbox).

becomes...

Now is the time

...I have never seen this; but, it brings up a good question. Say I have a field for "STATE", I know it will always use 2 spaces, so, should I set the "length" to '2' also? There are many cases where a textfield will be set to say, 8 characters, never more or less, should I set the field to 8? or does it hurt to leave it at 50? or even 255? Does Access commit the entire 50 or 255 when the table is saved?

If it is best to set an 8 character text field to 8 then I have a lot of revamping to do (although I compact and repair on each open/close + have no redundant or excess code anywhere, nor do I store calculations (beyond a simple DLookUp)) in text objects (another horror story - formulas disappearing that are stored as the source for a textbox).

I am currently using Access for ASP.NET on the web and it works beautifully although again I hear horror stories on that to (web site is Right now visitors < 100 a day -- I suppose in my best estimation Access can hold its own up to 300 hits a day -- my best guess.

Just a thought or two. Thanks.
 
Hi Isadore,

You make quite a few points in this thread; I feel like adding my own comments to a few of them, so here goes:

(a) Access is a beautiful tool, when used in the right hands, for the right sort of applications.

(b) Unfortunately, because of its wide availability (a product of the Microsoft marketing engine, and the fact that its such an easy tool to start using), it is frequently used by the wrong people, to develop the wrong systems (eg. poorly trained 'programmers', or for applications beyond its intended scope). These sort of systems earn it bad publicity, especially in the corporate sector, where senior IT managers have a vested interest in retaining control of IT.

(c) You'll mostly find that those who knock Access the most, know very little about it. I recently reviewed a Filemaker book, which started off by knocking Access .... what a joke that was.

(d) Any tool can be well used, or badly used. Access is no exception. The opportunity though to use it badly is probably more widely available, than say, C++, because of its availability and ease of 'initial' use.

(e) Enough said on that ....... on to the text field. I've never heard of or had the truncation problem you speak of. Perhaps the protaganist would care to produce the source code and design of a system which demonstrates this problem. Bet there'd be dozens of real experts around here who could identify the cause and fix, or more likely look more closely at the overall design and implementation and prevent this sort of problem (and others) from occuring in the first place.

(f) As far as I know, the size of text fields does impact the size of the database. Simple exercise. Define a database with one table and one text field or length 10 characters. Then write a little function to populate it with 100,000 records say. Compact the db, and check its file size. Then re-define the field to say 100 characters. Modify the module to re-write the 100,1000 records (each character position of the record) (probably not necessary to do this, but why not). Recompact and recheck the size. It should have expanded somewhat.

Enough of my ramblings,

Be well

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)
 
I don't believe the truncation stories. I've never seen it, never heard of it. I've been using Access since 95, and paying attention to public forums on Access since then as well. Never heard of this one.

There was just a long thread about field size. With text fields in Access, they have absolutely no impact on the size of the database. There's not really much of a reason to set the field size unless you want to limit what gets put in there. For a state field, it makes tons of sense. And for an SSN field. But for a field to hold a customer name, there's no point.

[I just looked around for that thread, but I couldn't find it. Sorry.]

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks
Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.
 
Hi!

Found the thread, thread700-738563 it does primarily address the database size in relation to text field size and what's actually stored, but there's also a a brief discussion, and a link on performance.

JeremyNYC:
I've taken the liberty of recommending and linking to &quot;Fundamentals of..&quot; on your site in a thread. Hope that's alright.

Roy-Vidar
 
I ran a little test, as described below:

Create a new database with single, one field table. DB Size after creation: 92K

After populating single table with single text field of 10 characters with 100,000 records: 2.32 MB

After changing definition of text field from length of 10 to length of 100: 2.32 MB (ie. no change)

After repopulating all records with records of 100 characters each: 13.1 MB

ie. As previously stated, Access will claim the space as it needs it, and is using variable length text fields.

When I reverted the field length back to 10 characters and generated the records at that length and re-compacted the size reverted back to 2.32MB (as expected).

Indexing the 10 character field jumped the db size to 2.54 MB.

Re-indexing the field, after changing its definition to 100 characters (but without changing the data) did'nt affect the db size; not sure what to deduce from this, as all of my generated test records are identifcal.

In summary, these little tests all seem to tally with the other posts.

Steve Lewy
Solutions Developer
steve@lewycomputing.com.au
(dont cut corners or you'll go round in circles)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top