Query too complex

[KentJ]

I have been fighting with this error for a couple of days now! I issue a simple query like "SELECT * FROM Clients WHERE (Category ="Garage")" which returns a recordset that is one row by about 90 columns. The data populates about 70 bound text boxes (it's a busy page!) When I issue a recordset.update VB returns a "Query too complex" error.

The help indicates that this is due to too many fields in the SELECT clause. Indeed if I pare down the * to a few explicit field names the .update works swell. (The error occurs somewhere between 16 and 43 fields in the SELECT clause)

Is there really a limit to the number of fields in a SELECT clause? Could it be as low as 43????

BTW I am using VB6, Jet 3.51, and an Access97 db.

[J Staniforth]

It seems a lot of fields in one table. Perhaps you ought to be asking why your original table has so many fields.
You may be able to make your system more efficient if you investigate this.
Regards,
John.

[KentJ]

I am by no means a database design guru, the table contains info about clients, each row is a different client, each column a unique attribute of the client. I obviously could break the table into many tables, but then I woud have 1 to 1 relationships abounding, and lots of JOINS in my SQL.

I sort of assumed that KISS was wise, and large and simple was better than small and complex. But I don't really know which is better.

Are there any rules-of-thumb for how many columns tables should have?

[J Staniforth]

rules-of-thumb :

Yes it's fare comment to apply KISS if you can - I am no believer in overkill.
However, you clearly have encountered a problem taking this approach in this instance and I assume that the database holds a large number of records.
If you develop the tables as you have it will never be the most efficient solution in terms of operation (although it may be in terms of development & maintenance - probably more important to you).
It all depends on the quantity of records, the number, size and type of fields making up those records and the spec of the machine running on.
Making the design more complex will solve your operational problem, for instance apply lookup tables to any repeating fields and see if this helps - ie Business Field could use a lookup code and refer to a table of businesses which hold there description.

Regards