Too many fields defined error

[clloftus]

I created my DB in MS Access 2007, but recently converted to 2010. I don't think my issue is based on this conversion.

When adding new fields in a table, I am receiving the "too many fieds defined" error. I tried compating/repair; also copying the table and deleting the old. All with no luck.

Any help is greatly appreciated.

 

[pr2-eugin]

How many fields exactly are you dealing with?

 

[clloftus]

I have about 173 fields, and tried to add 6 additional. I only have 2 tables in the entire database with approximately 1600 records. I know I probably need to redo it and split the table into several tables. I will say I am not a guru by far, so taking that leap to making more table and figuring out how to move all the data without losing it is a little overwhelming.

Thanks in advance.

 

[MSAccessRookie]

Question:

Are so many Fields required, or does the Table have repeating Field Values in a format similar to the following:

Month1Value1
Month1Value2
Month1Value3
Month2Value1
Month2Value2
Month2Value3
.
.(8 More Months)
.
Month11Value1
Month11Value2
Month11Value3
Month12Value1
Month12Value2
Month12Value3

A Table Structured like this could be redefined as multiple Tables to make the issue go away

 

[clloftus]

It should be more than one table, I just haven't figured out how to move cert fields to another table without 1) losing the data and/or 2) messing up the joins.

I have a users table that contains certain data.

USER TABLE
Username
Organization
Project
Role

NETWORK TABLE
Network1
Network2
Network3
and so on

On the form, I have one user's data appear for record 1 which brings up all networks that that particular users has.

At first it was easy because there was only a few items in network, but that has grown tremendously. I should have made that separate tables, but time got away and now it is essential that I break them apart.

Again, problem is moving the data to separate tables without losing data and not messing up joins. I was trying to work around it and adding the last few networks without having to do the big table move and work on that a little at a time. But ...

Do you know an easy way to move the tables and delete unnecessary data without messing up the queries and joins?

Again any help is appreciated.

 

[MSAccessRookie]

Question:

Are you stating that there is a Row for each user that contains over 170 Fields representing Networks in the Table, or are there repeating Groups of Fields in the Table representing the Networks? The sample layout does not show enough of the picture for me to provide a proper answer.

The good news is that either of these cases has an easy method of redefining the Data into seperated Tables, but they might need to do it differently

-- Rookie

 

[clloftus]

There are 173 fields and a user could have one or more of these fields checked in their individual record.

Does that make sense?

 

[MSAccessRookie]

That should be easy to fix by adding two new Tables, but the Queries ad the coding will need to be modified to accomplish it. One way would look like the following.

OLD FORMAT:
 
tblUserNetworks
FK to UserID
Network1
.
[B][COLOR=sienna]{ More Networks }[/COLOR][/B]
.
Network 173
 
NEW FORMAT:
 
tblNetworks
NetworkID
Network Name
 
tblUserNetworks
FK to UserID
FK to NetworkID.

• Entries in tblUserNetworks would only exist where they apply.
• New Queries would create Lists of Users and their Networks.
• Existing Queries would need to be modified to use the New Queries instead of the old Table.

I am sure they will be more, but this would be the start.

-- Rookie