Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Too many fields defined

Too many fields defined

Too many fields defined

I have an Access97 table that contains approximately 240 fields, which has been functioning just fine. When I changed the data type of several of these fields from Date/Time to Text, and then attempted to save this file, I got, "Too many fields defined. You tried to perform an operation that involves more than 255 fields. Reduce the number of fields, and then try the operation again." I let the table go unsaved, and opened it again, and changed a single field, and got the same message. Next I copied just the structure of that table to a new table name, opened it changed one field and saved it OK. But when I continued down through the fields attempting to save each change as I went, I still got that error. Any ideas?

Thanks, Gary

RE: Too many fields defined

Hi Gary,
    I cannot say I've ever seen an error message like that before. One thing I will pose to you is: Is there any way you could break down the table? If you run the table through the "Table Analyzer" (Tools-Analyze-Table), does it suggest anything to you? I tend to suspect that a table with as many fields as yours could probably be split, which will improve the performance of your Db, probably reduce upkeep and probably better follow the "rules" of relational database design. Give it a go and keep everyone posted on your findings. Good luck,


RE: Too many fields defined

Thanks for your reply.
I finally got everything changed by simply opening a copy of the table (struct only), changing on the copy, saving it as I went along, and finally appending the data back into the changed table. (Believe it or not, virtually all of those some 240 odd fields are needed. viz. they are all data items related to a patient.)

RE: Too many fields defined

Hi Guys,

For the future, there is an easy way of getting around this
error. Let's say that the table which containes the 240
fields is called "Customers". Make a copy of this table (including data) and call it "Customers1". This new table
can be edited without any problems. Change the name of "Customers" to "Customers11" (for exampel) and then
change the name of the new table "Customers1" to "Customers". If your happy with the changes made and every thing works ok, delete "Customers11".


RE: Too many fields defined

I strongly suspect that your db is not normalized properly. I have no doubt that you need all the information you're capturing, but I do believe that you are putting most of it in the wrong table. I'll give you an example. I'm going to assume that many of the fields are direct responses to health related questions, i.e. various codes for types of services performed, medications, procedures, etc... In all likelyhood, a patient will have many such records. Possibly, one for each visit. If this is the case, then many of these fields are left blank. Only used if/when that particular procedure is performed.

Correct me if I'm wrong, but in all my experience, I've never seen a case where anyone needed to collect 240 different specific pieces of information that apply to one entity. Usually, when someone builds a table with that many fields they misunderstand what an entity and it's descriptors really is. They have fields that, on first blush, appear to be descriptors of the entity but actually describe a dependent entity. An example would be an office visit, a prescription, a procedure, all of which HAPPEN to a patient but are not really a part of that patient.

Think of it this way: An entity is something you can hold in your hand(literally or figuratively), if your hand were big enough, that cannot be broken down into individual components without damaging it. A descriptor is an attribute that the entity has that is not likely to change and is unique to the entity. In your case, an entity is a patient, descriptors would be name, current address, SSN, DOB, etc. Something that DOES NOT describe the patient is their temperature, medication they're on, the doctor they're seeing today, a procedure performed on them while at the hospital, etc....

RE: Too many fields defined

Related question:

So then, where is the counter?  I'd like to be able to get a count on the number of fields in a table.  Is there a simple way to do this?

Eliza J.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close