Microsoft KB Archive/125651

= You receive a "Query is too complex" error message when you try to update or to delete a record of a CRecordset object in Visual C++ =

Article ID: 125651

Article Last Modified on 11/21/2006

-

APPLIES TO

 Microsoft Foundation Class Library 4.2, when used with:  Microsoft Visual C++ 1.5 Professional Edition

 Microsoft Visual C++ 1.51

 Microsoft Visual C++ 1.52 Professional Edition

 Microsoft Visual C++ 2.0 Professional Edition</li></ul>

 Microsoft Visual C++ 2.1</li></ul>

 Microsoft Visual C++ 4.0 Standard Edition</li></ul>

 Microsoft Visual C++ 4.1 Subscription</li></ul>

 Microsoft Visual C++ 5.0 Enterprise Edition</li></ul>

 Microsoft Visual C++ 5.0 Professional Edition</li></ul>

 Microsoft Visual C++ 6.0 Enterprise Edition</li></ul>

 Microsoft Visual C++ 6.0 Professional Edition</li></ul>

 Microsoft Visual C++ 6.0 Standard Edition</li></ul> </li></ul>

-

<div class="notice_section">

This article was previously published under Q125651

<div class="symptoms_section">

SYMPTOMS
When you try to update or to delete a record of a CRecordset object, you receive the following error message: <ul> <li>With 16-bit MFC database classes and 16-bit ODBC desktop drivers:

<pre class="fixed_text">  DBMS: ACCESS, Version: 2.0 Query is too complex. State:S1000[Microsoft][ODBC Microsoft Access 2.0 Driver]

</li> <li>With 32-bit MFC database classes and 32-bit ODBC desktop drivers:

<pre class="fixed_text">  DBMS: ACCESS , Version: 2.0 Query is too complex. State:S1000,Native:-3071,Origin:[Microsoft] [ODBC Microsoft Access 2.0 Driver]

</li></ul>

These examples show "Microsoft Access 2.0". However, it but could be any of the Microsoft desktop drivers.

<div class="cause_section">

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= AND column2= AND column3 = Here 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.

<div class="resolution_section">

RESOLUTION
To work around this behavior, do one of the following:


 * Reduce the number of columns in your recordset. If you are using the CRecordset object that AppWizard/ClassWizard created for you, you can reduce the number of columns returned in the recordset by removing record field exchange(RFX) functions from the CRecordset's DoFieldExchange function.
 * Use dynasets. See the online books for more information about the differences between snapshots and dynasets. Special code must be acquired to use dynasets with the 16-bit MFC Database classes. CRecordsets which use a dynaset use the SQLSetPos ODBC 2.0 functionality to perform positioned updates and deletes. This means the MFC database classes do not need to construct an SQL statement to send to the ODBC driver. A call to the SQLSetPos function of the driver is all that is necessary to perform the update or delete.

<div class="status_section">

STATUS
This behavior is by design.

<div class="references_section">