How to reset number of fields in a table

I remove some fields in a table and replace them with others periodically.   Apparently Access doesn't reset the total number of fields as they are removed, and continues to accumulate the count, which leads to an error when the table is referenced.

I've seen a possible fix being to copy the table with a different name, delete the original, then name the copy with the original name.

This didn't seem to work.

I have about 90 fields, and I believe the limit is 255.

1. How do I "see" how many fields Access thinks this table has, and
2. how do I resent the count?
David_W_RAsked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor President / OwnerCommented:
Without seeing the table and knowing what it is your working with from a data perspective I can't say.   But it's not a normal matter of course to be removing and adding fields to a table to the point where your bumping into the 255 field limits.

Jim.
0
 
MacroShadowConnect With a Mentor Commented:
Compact and repair should solve you're issue.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I didn't think the field count in table was cumulative, but it could be.  It certainly is for form controls.

As MacroShadow said, do a compact and repair and try again.  You may find however your stuck with creating a new table.

The real question though is why are you having to remove fields and replace them?   That's not a normal thing to do.

Jim.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
David_W_RAuthor Commented:
As we progress thru time, dated value fields are replaced with fields for newer dates.
0
 
David_W_RAuthor Commented:
By the way, Compact & Repair and copying/renaming didn't solve the error of "too many fields defined" .
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<As we progress thru time, dated value fields are replaced with fields for newer dates. >>

 You need to modify your design.  It's the design that's the problem, which is why your bumping into the problem with too many fields.

Jim.
0
 
David_W_RAuthor Commented:
Modify in what way?
0
 
David_W_RAuthor Commented:
Assuming there's a better way to configure the fields, maybe with generic names that can roll with time, how do I reset the table count for the immediate purpose of using it?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor President / OwnerCommented:
<<Assuming there's a better way to configure the fields, maybe with generic names that can roll with time, how do I reset the table count for the immediate purpose of using it? >>

 You can't.  You need to re-create the table.

Jim.
0
 
David_W_RAuthor Commented:
Ok then, I'll re-create the table.  :) Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.