is there a maximum number of text fields?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have created a very simple database that has about 215 fields. 90% of them
are text fields. I am going back and trying to change some of the date/time
data types to text data types. After making the change, I try to save. Upon
attempting to save, the database prompts me that there are too many fields
defined.

The only thing that I could atribute this to is that there might be too many
text fields. Is this assumption correct? If so, how do I remedy?

I have tried to create new tables and create relationships, but that is
beyond my experience level.
 
There is a maximum of 255 fields in any one table. But the counter does not
reset when you delete a field. Also, if you change a field type that uses
up one of the 255 slots.

Try compacting your database and changing a few of the fields and saving the
changes. Then compact again, and do some more fields if needed.

Now, let me say that a table with that many fields is very, very rare. You
probably have a table design issue and need to split out your data into more
tables. ALSO, there is a 2000 character limit in a row, so you could run
into that as a problem.
 
John Spencer said:
There is a maximum of 255 fields in any one table. But the counter does
not reset when you delete a field. Also, if you change a field type that
uses up one of the 255 slots.

Try compacting your database and changing a few of the fields and saving
the changes. Then compact again, and do some more fields if needed.

Now, let me say that a table with that many fields is very, very rare.
You probably have a table design issue and need to split out your data
into more tables. ALSO, there is a 2000 character limit in a row, so you
could run into that as a problem.
Click to expand...


Just to add to John's point (in case it is not clear to the OP):
While you can have a record with 219 text fields, if each of those fields
contained "0123456789" then the record would break the 2000
characters/record limitation and so you couldn't save this record.
And to back up the other point, I have been designing databases for over ten
years and normally my tables might have 10,20,30 fields but almost never
more than that. If you feel you really need 219 fields, peraps you could
let us know why.
 
Thanks for the additional description/information. I was a bit terse in my
response. I really need to find or put together a FAQ for this issue.
 
Thank you for your replies. I am a construction manager. I have about 161
jobsites that I need to keep track of information.

They are identified by site number. Then there is over 200 pieces of
information that is needed to update the construction status for each site.
Therefore, all 161 sites have the same fields, but unique information for
each. The information is anything from address, site contacts, equipment,
subcontractors, material orders, PO tracking, Permits tracking, etc. All of
the fields having unique data.

I have created a DAP to use for data entry. I have created related tables.
It seems that it is only permitted to populate one table from the DAP, the
data for the related tables cannot be updated. I am investigating the
process by which I could separate the data, but it is important to be able to
populate all of the data from a single DAP.
 
I have come to the conclusion that my database is screwed.

I have reviewed the specification that states that the maximum number of
characters in a record is 2000. My current design will surpass that, easily.

A complete redesign is required. However, my question remains, Can a DAP
used for data entry, populate multiple related tables?
 
Sorry, I don't have an answer for this question.

Perhaps you should start a new thread with that specific question.
 
I am running into a similar problem of needing to populate multiple tabels.
Have you come up with a solution for yours?,Are you willing to share if so?
thanks.
 
Back
Top