Field limits

[D Pavlichek]

There is an existing MS Access database in my organization
that is reaching capacity. We have (by a visual count) 193
fields and should be able to go to 255. Yet when we try to
add another line and save, we get the "Too Many Fields
Defined" error.

I'm no Access expert and would appreciate any feedback I
can get on this problem. I'm trying to buy a little time
for the department while we find an upgrade/upsize
solution.

DP

 

[Rick Brandt]

There is an existing MS Access database in my organization
that is reaching capacity. We have (by a visual count) 193
fields and should be able to go to 255. Yet when we try to
add another line and save, we get the "Too Many Fields
Defined" error.

I'm no Access expert and would appreciate any feedback I
can get on this problem. I'm trying to buy a little time
for the department while we find an upgrade/upsize
solution.

Most likely an improper design with that many fields. However; this might
be a "lifetime limit" where even fields that were previously deleted still
count against you. Recreating the table from scratch would get those back
for you. There is also a 2KB limit on the data per-row and you might have
reached that.

 

[John Vinson]

There is an existing MS Access database in my organization
that is reaching capacity. We have (by a visual count) 193
fields and should be able to go to 255. Yet when we try to
add another line and save, we get the "Too Many Fields
Defined" error.

I'm no Access expert and would appreciate any feedback I
can get on this problem. I'm trying to buy a little time
for the department while we find an upgrade/upsize
solution.

Any time you *change* a field, one of the 255 field "slots" gets used
up. You can recover them by Compacting the database.

HOWEVER - a Table with 193 fields is almost certainly not properly
normalized! 60 fields is a VERY wide table, and I've never needed
more. I very much suspect that you have some one-to-many relationships
embedded within each record of this table. Correcting your data
structure will be much more productive than attempting to upgrade to a
different RDBMS; if it's a bad design in Access it'll be a bad design
in SQL/Server or DB2 as well!