"too many fields defined" error

[kd1s]

Doesn't help me much. I had nothing to do with the original design of this
database but it's got 215 fields in it. Through various changes I end up with
the increment going much higher (changing types to number/double, etc.)

This is what annoys me most about MS Access. But I'm stuck because I want
this to work and don't have any other options at the current time.

If anyone at MS is listening, I know you have an enterprise class database
product but Access needs to be able to handle more than 255 fields. It's 2008
already.

And yes, I know, 3rd normal form. As I said, I had no influence on the
design of this database. It's sad I know but it's the reality of it.

 

[John W. Vinson]

Doesn't help me much. I had nothing to do with the original design of this
database but it's got 215 fields in it. Through various changes I end up with
the increment going much higher (changing types to number/double, etc.)

This is what annoys me most about MS Access. But I'm stuck because I want
this to work and don't have any other options at the current time.

If anyone at MS is listening, I know you have an enterprise class database
product but Access needs to be able to handle more than 255 fields. It's 2008
already.

And yes, I know, 3rd normal form. As I said, I had no influence on the
design of this database. It's sad I know but it's the reality of it.

The getaround would be to create a new table with the modified field types,
run an Append query to move data into it, and delete the old table.

A good workman never blames his tools. You would really be better off
normalizing the database structure and migrating the data rather than
struggling with what is intrinsically a flawed design, regardless of Access'
limitations.

 

[Roger Carlson]

With all due respect, John, without knowing the exact circumstances and
business rules, you can't be sure that 3rd normal form is appropriate. In
an OnLine Transactional system, sure. But in a datamart-type application,
3rd NF is not. I have several applications of the latter type which have
over 200 fields. Although I've yet to bump the 255 limit, it's conceivable.

It's all about the business rules, one of which may be: "Use this table
design, period."

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Roger Carlson]

Well, if you can't normalize the structure, the only work-around is to
create another table with a One-to-One relationship and add your additional
fields to that. It's kind of a bother, but it can be done. Of course,
since a query is also limited to 255 fields, you'll never be able to return
the "full" table.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Douglas J. Steele]

With due respect, Roger, kd1s's problem doesn't seem to be that he needs to
exceed 215 fields, but that because he's changing characteristics of the
fields, "shadow fields" are being created. Compacting should cause those
fields, resetting the field count to below 255.