Too Many Fields Defined

N

Novice2000

One of my associates is trying to add columns in this massive database and
gets this message. Is there a limit to the number of fields? SHe indicated
that she had removed many columns to make room for the new ones.
 
C

Cheese_whiz

255 fields in a table is the max. If you do a search in the Access help file
from within the program for "Access Table limitations" you'll get the spec
file which will tell you most of what you need to know.

I do think, however, that I may have read others indicate that the field
count may not adjust to removing fields, and therefore you may get that
warning even though you don't currently have more than the allowed columns in
your table. I also think there's a way to reset the count, but I'm not
positive about any of this, so maybe someone else will clarify.

Hope that helps.
CW
 
J

Jeff Boyce

If you and your associate will spend a bit of time in the tablesdbdesign
newsgroup, you'll find a strong consensus about the "proper" number of
fields in a table. "Proper" is too strong a term, but now that I have your
attention...<g>

Access is a relational database. It is rare to need more than 30 fields in
any one table in a well-normalized relational database. Your description of
multiple columns and a "massive" database suggest that you are working with
a design that would be appropriate ... for a spreadsheet!

If you'll provide a bit more description, the newsgroup readers will be able
to offer a bit more specific suggestions.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
N

Novice2000

Hi Jeff,

Scold me if you must, but I inherited this nightmare of a table. I have
tried in vain to get them to allow me to divide it up. It makes no sense to
have demographics and accounts receivable in the same table. It has all
account data in one place going back to 1999. I have been told that I can
tackle it in the off season.

The tablesdbdesign sounds interesting. Where would I find this forum?

As a temporary patch I counted the columns and there were 215 visible. The
"room" was created by my associate by deleting other columns. But, reading
elsewhere I found out that deleting columns in Access doesn't actually clear
up room for more columns. I was able to add columns after I saved the table
as something else. Thanks for you help.
 
D

Douglas J. Steele

Novice2000 said:
The tablesdbdesign sounds interesting. Where would I find this forum?
http://msdn.microsoft.com/newsgroup...ft.public.access.tablesdbdesign&lang=en&cr=US


As a temporary patch I counted the columns and there were 215 visible. The
"room" was created by my associate by deleting other columns. But, reading
elsewhere I found out that deleting columns in Access doesn't actually
clear
up room for more columns. I was able to add columns after I saved the
table
as something else. Thanks for you help.
Click to expand...

Did you try compacting the database, as I suggested elsewhere in this
thread?
 
B

BruceM

Did you try Douglas Steele's Compact and Repair suggestion (Tools > Database
Utilities)? That should have allowed you to add columns (after backing up,
of course).

It sounds as if you are adding data directly to the table. If so, that is
not a good idea. Tables are where data are stored; forms are for adding,
editing, etc. the data; reports are for printing. If you want the look of a
table you can use the from wizard to make a datasheet form, which will look
like a table, but will keep users out of the table's design area (or at
least make it necessary to go through extra steps to change the table
design). Or you may decide it is easier to work with data if you take
another approach to form design.

On another point, queries will let you filter the records by date. As for
breaking demographics and Accts Receivable into separate tables, a good idea
no doubt. Let me point out that a lot of people have experience migrating
data from spreadsheets (which is essentially what you have now, I think) to
a relational database, so time taken to see how others have tackled the
problem will be well spent.
 
J

Jeff Boyce

I don't scold ... but I have been known to use a gentle poke now and then
<g>...

It looks like you've already received a lead to the tables... 'group.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Top