Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

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?
0
David_W_R
Asked:
David_W_R
  • 5
  • 4
3 Solutions
 
MacroShadowCommented:
Compact and repair should solve you're issue.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
David_W_RAuthor Commented:
As we progress thru time, dated value fields are replaced with fields for newer dates.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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)PresidentCommented:
<<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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
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)PresidentCommented:
<<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

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now