Too many fields error in design view

#1 (**permalink**) 28-Jul-2006, 08:14 AM

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,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/10630-too-many-fields-error-design-view.html
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).

Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10630
Can someone please suggest a workaround ?

*

#2 (**permalink**) 28-Jul-2006, 08:14 AM

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,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10630
then start messing with the field definitions once you have smaller
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10630
manageable tables?

--
Rick B

"Justin" wrote in message
news:0ABA5E2B-6012-4007-9E95-22C36908914B@microsoft.com...
> 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 ?

#3 (**permalink**) 28-Jul-2006, 08:14 AM

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

"Rick B" wrote:

> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10630
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10630
> manageable tables?
>
>
> --
> Rick B
>
>
>
> "Justin" wrote in message
> news:0ABA5E2B-6012-4007-9E95-22C36908914B@microsoft.com...
> > 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 ?
>
>
>

#4 (**permalink**) 28-Jul-2006, 08:14 AM

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.

"Justin" wrote in message
news:0ABA5E2B-6012-4007-9E95-22C36908914B@microsoft.com...
> 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,
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10630
> 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 ?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10630

#5 (**permalink**) 28-Jul-2006, 08:14 AM

Justin wrote:
> 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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10630
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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=10630

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

--
Joseph Meehan

Dia duit