add fields

[Frank Xia]

I encountered a strange problem when I am trying to add a column into a table
with less than 150 fields. The error message is "Too many fields", " The
maximum is 255 fields". However, when I try to do samething on a similar
table (previous backup copy) I can add fields I want. I really need some help
here from you guys.

Thanks inadvance for any feedback!

 

[Rick B]

A table should normally have twenty or thirty fields at the most. If you
have 150 fields, you should stop and rethink your database design.

To answer your question, I think that it also tracks fields that may have
been added, then deleted. Doing a compact and restore might fix this.
Creating a new table and importing your records would certainly fix it.

But, it sounds like you have a seriously flawed design if your table has
that many fields. You are welcome to post the structure of your database
and we could help you normalize it.

 

[SusanV]

Have you tried a compact and repair? I've run into this before and that
always straightened it out...

 

[Frank Xia]

Thank you both very much! Rick and Susan. After I ran compact and repair, I
can add column as I desired.
Rick mentioned the number of fields in a table could be a serious flaw in
the design. I was not aware of that. The table is used to store all audit
information for a loan with Loan Number as a primary key. It looks like
following:

Loan #, audit date/time, auditor, Status, Que1, Que2, ... ..., Que 120

I did try different kind normalization but because I use this table as part
of my vb.net backend database I try to avoid any insert and update failure.
If I downsize the table with using smaller tables associated with
relationships, there is a big challenge for me to insert and update the
records. If you can give me more idea on this, that would be great!
I know It is unusual for a table has about 150 fields but why ACCESS allows
maximum 255 fields to be created if large number of fields could cause
serious flaw?

 

[Duane Hookom]

You might want to take a look at "At Your Survey" found at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane. This is
a more normalized approach to your "QueX" fields.

 

[Vincent Johns]

Thank you both very much! Rick and Susan. After I ran compact and repair, I
can add column as I desired.
Rick mentioned the number of fields in a table could be a serious flaw in
the design. I was not aware of that.

I think he meant not that it will cause your database to fail, but
rather that a large number of fields can
- be difficult to keep track of; what does each one mean?
- occupy more disk space than needed, if some fields have Null values
in most records

Even with a large number of fields per record, you can reasonably expect
Access to process transactions correctly.

The table is used to store all audit
information for a loan with Loan Number as a primary key. It looks like
following:

Loan #, audit date/time, auditor, Status, Que1, Que2, ... ..., Que 120

I did try different kind normalization but because I use this table as part
of my vb.net backend database I try to avoid any insert and update failure.
If I downsize the table with using smaller tables associated with
relationships, there is a big challenge for me to insert and update the
records. If you can give me more idea on this, that would be great!
I know It is unusual for a table has about 150 fields but why ACCESS allows
maximum 255 fields to be created if large number of fields could cause
serious flaw?

The database that Duane Hookom advertised in another message may be a
good model for you to use (I haven't examined it), but you might also
think about ways to cluster your "Que..." fields into related groups,
putting each such group into a separate Table, and with a [Loan Number]
key in each record of the new Table linking it to your original Table.
For example, maybe some of your records relate to unmarried persons, in
which case any fields related to a spouse need not be included. You
could lump those fields into a [Spouse] Table, which would contain
records for some values of [Loan Number] and not others (thus saving space).

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.