Too many fields error in design view

[Guest]

Hello,

I have a table that I imported from Excel. Unfortunately, I missed changing
a few of the datatypes during the wizard (okay, I skipped them, I admit it).

Now, when I am in Design view, and I attempt to change those datatypes,
Access returns an error indicating "too many fields". I suspect that Access
is in fact rewriting new fields (in the same table) and that it cannot
rewrite the whole table (at least up to the fields which I am correcting).

Can someone please suggest a workaround ?

 

[Rick B]

How many fields do you have? Do you plan to "normalize" this data and break
it into smaller tables and related tables? If so, can you do that first,
then start messing with the field definitions once you have smaller
manageable tables?

 

[Guest]

Yes, breaking the table up was what I am doing to work around the limitation.
Yes, the table is large, and smaller would be nicer, but this is one group of
fields that should stay together. Well, they aren't staying together anymore!

Thanks

 

[John W. Vinson/MVP]

Your analysis is correct: every field you change actually creates a new
field, cutting into your 255 field limit.

You can recover these lost slots by Compacting the database.

I'd really suggest creating properly normalized and datatyped tables, empty,
*first*; then Link to the spreadsheet and run Append queries to migrate the
data into your tables.

 

[Joseph Meehan]

Yes, breaking the table up was what I am doing to work around the
limitation. Yes, the table is large, and smaller would be nicer, but
this is one group of fields that should stay together. Well, they
aren't staying together anymore!

Thanks

It sounds like you really need to "normalize" the data. I don't know
how much you know about relational databases, but the lack of normalization
is a common problem for anyone coming from a spreadsheet background.

Using several related tables is not separating the data. The data stays
together even if it is in different tables.

For example If I have a list of coaches and I want a list of their
students, I might have 20 fields called student1 student2 ... and if I did
not go over 254 fields that might work, but it would not work nearly as well
as a separate table with just students listed and related to the coaches.

Please consider normalizing as it eliminates a lot of problems of which
you found only one .... so far.