table with too many fields

[Guest]

In a new data base, I have imported an excel table with about 180 fields and
25 records. When I change the data type property in the design mode for the
blank fields from text to number double and save there is an error message
too many definitions, cannot save; what is the limit to the max number of
fields in a table? If I split the table into two or three and create a query
with the 180 or 200 fields, will it accept so many fields?

 

[Guest]

255 is the maximum number of fields in a table; however, it often runs out
before them. For example your changing the datatype of a field used up one or
more of the 255. Then there is the maximum number of characters or bytes in a
record which is 2000 not counting OLE and Memo fields.

You probably do need more than 1 table. If the data is properly normalized,
there's a good chance that you could even need more than 3 or even 5. It's
rare to have more than 30 or 40 fields in a table within a properly designed
database.

It's possible to link that many fields in 2 or 3 tables in a query. They
also run out around 255 fields as do forms and reports. You'll want to create
a primary key, such as an autonumber, and put this field in each table. Then
you can do a 1-to-1 join between the tables.

 

[John Vinson]

In a new data base, I have imported an excel table with about 180 fields and
25 records. When I change the data type property in the design mode for the
blank fields from text to number double and save there is an error message
too many definitions, cannot save; what is the limit to the max number of
fields in a table? If I split the table into two or three and create a query
with the 180 or 200 fields, will it accept so many fields?

As Jerry says, this table cries out for normalization. If it's a
typical spreadsheet, it very likely has repeating fields (such as
fields named January, February, March or some other such series).

As a *temporary* expedient prior to moving the data into a set of
normalized tables, you should be able to get around the 255-field
limit you're hitting. Every time you *change* a field type it "uses
up" one of the 255 slots - but you can recover them by using Tools...
Database Utilities... Compact and Repair Database. Try changing ten or
twenty fields at a time, compacting after each. Or, build an empty
table with the desired fieldtypes, and import from Excel directly into
this table, rather than importing, letting Access guess at the
fieldtype, and fixing it up afterwards.

John W. Vinson[MVP]