Maximum Columns in a table in Access

[Tracey]

I understand the maximum columns allowed in one table in Access is over 200.
I only have 134 in one of my tables, but I keep getting an error message
telling me I have too many when I try and put new validations in and then
save. Anyone any ideas why and how to get around this? It's driving me mad!!

The exact error message is:
"This error can appear if you have exceeded the maximum number of columns
allowed in a table or the maximum number of locks for a single file.
If a table exceeded the maximum number of columns, close the database,
select Compact and Repair from the Tools - Database Utilities menu option.
This will remove column references from a table definition that were marked
as deleted (deletions do not actually occur until the database is compacted
and therefore they contribute to the column count).
If the maximum number of locks per file was exceeded, you can increase the
number by editing a registry entry for the local computer. However, this is
not a recommended option.
Find the MaxLocksPerFile registry value using the Windows registry editor
(regedit.exe) and increase the value.
The MaxLocksPerFile value is stored as part of the following key:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0
If you edit a value in the Windows registry, there is no guarantee that the
change will not corrupt the registry and render Windows unusable. Edit the
registry at your own risk. If you do, it is highly recommended you make a
backup of the registry before performing any edits."

I have tried compacting and repairing and this makes no difference, and I
don't k now what locks on a file are, so don't know whether this is the
problem instead!

 

[Duane Hookom]

If you really need more fields (this is rarely a good idea) you might try
copy and paste the table to a new table. This may allow you to add up to 255
(shudder) fields. Otherwise, try create a make table query based on your
existing table. See if you can add more fields in the "made" table.

I assume you understand normalization and have a good reason for more than
30 fields.

 

[John Vinson]

I only have 134 in one of my tables

Could you perhaps describe the nature of the fields in this table?

I have VERY rarely needed more than twenty or thirty fields in a
table. My very strong suspicion is that this table actually contains
one or more one-to-many relationships embedded in each record, and the
table would benefit from being split up into multiple tables with
one-to-many normalized relationships.

It may be that you are trying to solve the wrong problem: adding more
fields to a faulty design will make it worse, not better!

John W. Vinson[MVP]