How many fields are allowed on an Access 2002 table?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Darrell - 10 Apr 2005 04:21 GMT

How many fields are allowed on an Access table?

Reply to this Message

Duane Hookom - 10 Apr 2005 05:05 GMT

255 or about 220 too many.

Signature

Duane Hookom
MS Access MVP

> How many fields are allowed on an Access table?

Reply to this Message

Darrell - 10 Apr 2005 18:29 GMT

I can't get to even 140 with ut an error that says I have too many fields
identified.

> 255 or about 220 too many.
>
> > How many fields are allowed on an Access table?

Reply to this Message

Duane Hookom - 10 Apr 2005 19:31 GMT

If I tell you how to create more fields, would you promise to read up on
normalization?
http://www.ltcomputerdesigns.com/JCReferences.html#DatabaseDesign101

You may have defined and then deleted some fields. You might want to try
compact and repair your database and then Access should allow you to add
more fields. As suggested by Allen and me, anything over about 50 fields is
suspect for normalization issues.

Signature

Duane Hookom
MS Access MVP

>I can't get to even 140 with ut an error that says I have too many fields
> identified.
>
>> 255 or about 220 too many.
>>
>> > How many fields are allowed on an Access table?

Reply to this Message

Jeff Conrad - 10 Apr 2005 19:33 GMT

If you rename a field and/or delete a field, one of those
255 "spots" is used up. I believe compacting the database
will reset that number, but I'm not 100% sure.

Still, 140 fields seems to be about 100 too many.

Signature

Jeff Conrad
Access Junkie
Bend, Oregon

> I can't get to even 140 with ut an error that says I have too many fields
> identified.
>
> > 255 or about 220 too many.
> >
> > > How many fields are allowed on an Access table?

Reply to this Message

Rick Brandt - 10 Apr 2005 21:42 GMT

> If you rename a field and/or delete a field, one of those
> 255 "spots" is used up. I believe compacting the database
> will reset that number, but I'm not 100% sure.
>
> Still, 140 fields seems to be about 100 too many.

There is also a limit to the total bytes per row (2K I think) that you can hit
before the field quantity limit (the error is the same).

Signature

I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

Reply to this Message

Allen Browne - 10 Apr 2005 05:07 GMT

255.

That's 3 or 4 times what you could ever need in any normalized table.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> How many fields are allowed on an Access table?

Reply to this Message

Darrell - 10 Apr 2005 18:29 GMT

If 255 fields are allowed why do I recieve "Too Many Fields " when I reach
100 to 140.

> How many fields are allowed on an Access table?

Reply to this Message

Allen Browne - 11 Apr 2005 02:10 GMT

After deleting fields, you may need to compact the database before they
become available again:
Tools | Database Utilities | Compact

Please do follow through on the link Duane gave you, or search
"normalization" to find more links for yourself.

The issue Rick raised about the entire record not being more than about 2k
characters wide is the more practical limitation.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> If 255 fields are allowed why do I recieve "Too Many Fields " when I reach
> 100 to 140.
>
>> How many fields are allowed on an Access table?

Reply to this Message

Darrell - 11 Apr 2005 02:23 GMT

Everyaone who responded:

Thanks for the information and advice. when you'r just learining this stuff
I found the manuals don't tell all.

Darrell

> After deleting fields, you may need to compact the database before they
> become available again:
[quoted text clipped - 10 lines]
> >
> >> How many fields are allowed on an Access table?

Reply to this Message