CompareAllSearchableValues creates an update state too long for Access

[sprockett]

My access table has over 100 columns. When I use the CommandBuilder to
create my update statement with this option I can see that it is
creating three criteria per field in the WHERE clause.

example:
AND ((? = 1 AND `WellConfig` IS NULL) OR (`WellConfig` = ?))

When ran the Jet engine throws an error that the statement is too
complex.

Old ADO updating using RecordSets did concurrency checking in a
simlilar fashion (I suspect ) and updates this same table fine. I
assume that is because only a simple...

AND `WellConfig` = ?

....criteria was generated per field.


What am I missing?

 

[Otis Mukinfus]

My access table has over 100 columns. When I use the CommandBuilder to
create my update statement with this option I can see that it is
creating three criteria per field in the WHERE clause.

example:
AND ((? = 1 AND `WellConfig` IS NULL) OR (`WellConfig` = ?))

When ran the Jet engine throws an error that the statement is too
complex.

Old ADO updating using RecordSets did concurrency checking in a
simlilar fashion (I suspect ) and updates this same table fine. I
assume that is because only a simple...

AND `WellConfig` = ?

...criteria was generated per field.


What am I missing?

If your Access database will not be used by more than one user at a time you can
stop this by going to the advanced properties of the wizard that generates the
code and turning off concurrency handling. If not, then you will need to try to
figure out a way to reduce the number of columns in the table, or write your own
queries.
Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com