Too many fields defined

[Guest]

In Access 2003, I have a select query with 168 fields. I f I try to add
another field, I get the "Too many fields defined" error. The query is based
off a query with 195 fields, and is connected to a report.

Why am I not getting the 255 field maximum? I've tried compact/repair
database, and rebuilding the query from scratch.

Thanks.

 

[Larry Linson]

In Access 2003, I have a select query with 168
fields. I f I try to add another field, I get the "Too
many fields defined" error. The query is based
off a query with 195 fields, and is connected to
a report.

Why am I not getting the 255 field maximum? I've
tried compact/repair database, and rebuilding the
query from scratch.

For what purpose are you using this Query? In general, it's better to
display information returned from a Query in a Report, or allow user
interaction with the data in a Form. 168 discrete Fields would make for too
busy a Form or Report to be very usable for a human. Those will
considerable experience in relational database begin to be concerned that a
database does not follow relational design principles when they hear of a
Table with even a third that many Fields.

Yes, I know that doesn't answer your question. There is also a Record Size
limitation of 2048 bytes through Access 97 and 4096 bytes for Access 2000
and following, excluding Memo and OLE Object Fields.

But, it is possible that someone might, with more information, be able to
make some suggestions that would attain the result you want without
encountering this problem.

Larry Linson
Microsoft Access MVP