SYMPTOMS
When you try to update or to delete a record of a
CRecordset
object, you receive the following error message:
- With 16-bit MFC database classes and 16-bit ODBC desktop
drivers:
DBMS: ACCESS, Version: 2.0
Query is too complex.
State:S1000[Microsoft][ODBC Microsoft Access 2.0 Driver]
- With 32-bit MFC database classes and 32-bit ODBC desktop
drivers:
DBMS: ACCESS
, Version: 2.0
Query is too complex.
State:S1000,Native:-3071,Origin:[Microsoft]
[ODBC Microsoft Access 2.0 Driver]
These examples show "Microsoft Access 2.0". However, it but could be any
of the Microsoft desktop drivers.
CAUSE
The error occurs when the cursor library is loaded and the
recordset retrieved by the CRecordset is opened as a snapshot object that
contains more than 40 bound columns.
By default, the cursor library
is loaded when a CRecordset is opened. MFC snapshots perform positioned updates
and deletes by using the ODBC SQL WHERE CURRENT OF syntax. The cursor library
changes the WHERE CURRENT OF clause to a full WHERE clause using all the
columns in the recordset.
For example, the MFC Database classes might
create the following SQL statement when updating a record:
Update table1
SET column1=?
WHERE CURRENT OF XXXXX
The cursor library knows which record the application is currently
positioned at and converts the WHERE CURRENT OF clause into a WHERE clause that
will update the current record only. For example, assume the recordset has
three columns. The cursor library changes the SQL statement to:
Update table1
SET column1=?
WHERE column1=<current value> AND column2=<current value> AND
column3 =<current value>
Here <current value> represents the value of that column before
the update is performed.
You can see that more than 40 columns in a
CRecordset will cause more than 40 AND predicates in the WHERE clause. The
helpfile for the ODBC desktop drivers (ODBCJET.HLP) states in the SQL
limitations section that no more than 40 AND predicates are supported. Thus,
the SQL statement is too complex.
NOTE: Long binary columns (those using RFX_LongBinary) are not bound
and do not count against the 40-column limit. However, an MFC ODBC application
that references the RFX_LongBinary function may encounter this error when the
recordset is opened. This is because the cursor library precedes calls to
SQLGetData with the execution of a SELECT statement with a WHERE clause as
described above.