More than 40 fields in ADO Query is too Complex

[Darkbob]

Hi there. I've been using DAO in a VB6 program (everything working well) but I'm changing things to ADO so I have an easier time migrating to SQL and eventually out of VB6 entirely.

I'm a total ADO noob so sorry if I've made some really basic error here.

I ran into the weirdest problem. If I understand this properly, it seems ADO is limited to updating records with only 40 fields or less. My database has about 60 fields. I know... bad design. But too hard to change that right now if I don't need to.

I read on the Microsoft site I should include this: myData.Properties("Update Criteria").value = adCriteriaKey but I have to say I didn't really understand the article and may not have done things properly but it made no difference.

Every time I try to update any record in my database I get a "Query is too complex" error.

HELP!

Here's some code:

Set myData = New ADODB.Recordset
myData.CursorLocation = adUseClient
myData.CursorType = adOpenDynamic
myData.LockType = adLockOptimistic
myData.Properties("Update Criteria").value = adCriteriaKey
myData.Open mstrSQL, ConString$, adOpenDynamic, , adCmdText

myData.movefirst
myData("MyField")="SMITH"
mYData.update

"Query is too complex"

[DataMiser]

what is in mstrSQL ?

The place where you have constring$ should be a connection object

[Darkbob]

what is in mstrSQL ?

The place where you have constring$ should be a connection object

Thanks for your reply. Yes, well aware. The connection string is working fine. I can read the database fine and update tables with fewer than 40 fields. This is a well known issue for ADO databases with more than 40 fields but I cant find a work-around. Or rather I cant make it work. I didn't include my connection string it for brevity.

[westconn1]

google found this

SUMMARY
The following error occurs when using RDO or ADO to update data in an Access (Jet) database:
Query is too complex
Due to a limitation of the Jet database engine, this error can occur if the recordset to be updated contains more than 40 fields.

Using the Jet 3.51 engine, the error occurs if the recordset to be updated contains more than 50 fields. With the Jet 4.0 engine and later, the error occurs if the recordset to be updated contains more than 99 fields.

Using the Jet 4.0 engine and later, you may also see the errors:
Expression too complex
-or-
Too many defined fields
This article describes the problem scenario, the Jet limitation, and several workarounds.

There are several ways to avoid this behavior:

For new applications, use DAO when working with Access databases. DAO was designed for this purpose, and will not issue a SQL query to perform the update. Note that RDO and especially ADO were designed to operate efficiently with many other types of databases and are recommended for applications that will interact with various servers or that will be upsized in the future to use Microsoft SQL Server, for example.
When opening the recordset to be updated (such as the RDO OpenResultset or ADO OpenRecordset methods), select specific, and 40 or fewer fields.
Use a server-side cursor:

read the whole page https://support.microsoft.com/en-us/kb/192716

[Darkbob]

Yes, thanks for that reply. I read that one. I'm trying to get rid of DAO but I guess I may have to keep it around for updating this one large table. Or redesign the database.

I was looking at this part from that article...

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
rs.Properties("Update Criteria").Value = adCriteriaKey
rs.Open ...
...

But couldn't get it to work.

[westconn1]

according to the article, if you use jet 4.0 you can have up to 99 fields

[Darkbob]

according to the article, if you use jet 4.0 you can have up to 99 fields

Interesting! I wonder how you upgrade to Jet 4... time to hit the Googelizer!

Update:

Well shoot. After Googlizing I found out I'm already using DAO 3.6 in SP6 which means I'm already using Jet 4.

Here's my connect string (and those are semicolons followed by a D not weird emoji's):

ConString$ = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DataPath$ & "myDB.MDB" & ";Jet OLEDBatabase Password=" & MasterPassword$ & ";Jet OLEDBatabase Locking Mode=1" & ";"


I know the article says 99 fields but it really doesn't work. Up to 40 is fine, more than 40 is toast.

[gibra]

myData.Open mstrSQL, ConString$, adOpenDynamic, , adCmdText

ConString$ is totally wrong, in this line.

The Open methods require a ADODB.Connection object already open, of course.

Example:

    Dim CN As ADODB.Connection
    Set CN = New ADODB.Connection
    CN.ConnectionString = ConString$
    CN.Open 
    CN.CursorLocation = adUseClient

...
    myData.Open mstrSQL, CN, adOpenDynamic, , adCmdText
...

P.S. Remove all DAO references, if any, use only ADO(DB).

[Darkbob]

ConString$ is totally wrong

Thanks for the help with the connection string. I'm sure it was wrong and it's nice to have it done properly. Sadly the end result is the same.

Once the database is open, any attempt to use the .update function on the record set generates a "Query too complex" error because I have too many fields.

Editing other tables in the same database works fine so I know things are open properly.

This seems to be just a fundamental limit of ADO.

This program is designed to emulate an old paper form of some type. There are 10 rows. Each row has 9 fields plus a few extras up top for name and date, etc. I thought it was about 60 fields but the entire record has almost 100 fields. Maybe even more. Uggg. It would be more sensible to break the record up into smaller segments and link them together. But that's a lot of coding. *sigh* Time to get typing.

[dilettante]

Use a server-side cursor. That's what your code implies are after anyway, since you request adOpenDynamic which only applies to server-side cursors.

[gibra]

Thanks for the help with the connection string. I'm sure it was wrong and it's nice to have it done properly. Sadly the end result is the same.

Because you still open the recordset in ReadOnly mode, due to the fact that in Open method the appropriate parameter is missing:

myData.Open mstrSQL, CN, adOpenDynamic, [missing], adCmdText

Therefore the default will be: adLockReadOnly (not writable recordset).



You sould use the appropriate parameter, i.e. adLockPessimistic:

myData.Open mstrSQL, CN, adOpenDynamic, adLockPessimistic, adCmdText

[Darkbob]

Thanks for the tips on the connection string. Very helpful but not for this particular issue.

From what I can see a client side cursor does allow dynamic recordsets plus it allows lots of other things I'm using (and shouldn't be) like Bookmarks.

I know my code above doesn't show it but previous posts have helped fix that up. The record set is open, the cursor is set right, the locks are set right. Honest.

Here's the code:

 ConString$ = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & CBSDataPath$ & "myData.MDB" & ";Jet OLEDB:Database Password=" & MasterPassword$ & ";Jet OLEDB:Database Locking Mode=1" & ";"
   Set ADO1 = CreateObject("ADODB.Connection")
   ADO1.ConnectionString = ConString$
   ADO1.Open ' opens the ADO database
 
   mstrSQL = "select * from myTable"
   Set myRecordset = New ADODB.Recordset
   myRecordset.CursorLocation = adUseClient
   myRecordset.CursorType = adOpenDynamic
   myRecordset.LockType = adLockOptimistic
   myRecordset.Properties("Update Criteria").value = adCriteriaKey
   myRecordset.Open mstrSQL, ADO1, adOpenDynamic, adLockOptimistic, adCmdText

myRecordset.movefirst
myRecordset("Field1")="Smith"
myRecordset.update

ERROR - Query too complex

There may be minor tweaks to the connection string to make is nicer looking but that's seriously not the problem. I promise. It's the number of fields. Just way too many of them.

Now just trying to figure out how to remove fields from an ADO database but that's another thread.

[IkkeEnGij]

just a remark,probably not important but,
a client-side cursor is always a static cursor, no matter what is specified

[dilettante]

according to the article, if you use jet 4.0 you can have up to 99 fields

Yes, indeed. But one must actually read the article to see how to get there.

A client cursor ain't gonna cut it.

[Bobbles]

Rather than solve this problem, there may be a way to avoid it ?
I have not used the update method of a rs for years.
I use ACTION Updates instead
I never maintain a connection
I get rs and load into grid (or whatever) close rs and disconnect
When user modifies a record, I do an ACTION Update (no rs involved)
Something like this -

myStr = "UPDATE Addresses SET [Name] = '" & (sName & "X") & "' WHERE [Name] = " & "'" & sName & "'"

Perhaps if you switched to ACTION (which I love), it might avoid your problem (no rs)

Rob

[dilettante]

Actually updates to an ADO cursor generate such queries, but without the hazard of SQL injection your naive example demonstrates.

This too is spelled out in that linked article if one only takes the time to read and understand it.

[Bobbles]

"Are you talking to me ?"

[Bobbles]

DarkBob,
I have been composing an example project that demonstrates ACTION Inserts, Modifies and Deletes.
But I am extremely hesitant to attach it, as it will invite more insensitive/hurtful abuse.
Can you ask the offender to back off

[Darkbob]

DarkBob,
I have been composing an example project that demonstrates ACTION Inserts, Modifies and Deletes.
But I am extremely hesitant to attach it, as it will invite more insensitive/hurtful abuse.
Can you ask the offender to back off

OK you haters - yes I'm looking at you! Back off and let Bobbles post his project!

[Bobbles]

Project is attached

[IkkeEnGij]

This behavior occurs because the default behavior of ADO and RDO is to use each field in the recordset to determine the record to be updated on the server. That is, when the update is attempted, a SQL Update query is sent to the server. Part of this query is a WHERE clause that is used to identify the record to be updated. An AND clause appears within the WHERE clause for each field to be used in that identification. Updating a recordset with more than 40 fields involves a WHERE clause with more than 40 ANDs.

and

4.Ensure that a unique key is used to identify the record to be updated, rather than the default behavior of using every field. A unique key (though not necessarily a primary key) must be defined in the underlying recordset for this technique to work. •To do this in ADO (this will not work in versions prior to ADO 2.0), set the Recordset object's "Update Criteria" property to adCriteriaKey. This will work both for the Update() and UpdateBatch() methods:

---seems logical to me---
if the recordset is created from a select that does not include a key
how is the update to know what row to update in the table ?

---on the other hand---
say you have a table with 39 fields
and a recordset created with only 3 fields,none of wich is a key (or any combination of the 3 is a key)
how is the update to know what row to update in the table ?

could it be that every select,no matter how many fields, always retrieves all the fields ???
but only shows the selected fields ???

[IkkeEnGij]

could it be that every select,no matter how many fields, always retrieves all the fields ???
but only shows the selected fields ???

but what if there are duplicate rows in the table ?
not even retrieving all the fields will suffice to know what row to update
but then:
if a table has duplicate rows, it is not a relation
and hence all bets are off when using sql

but i am not staying on the subject...sorry...just had to say it

[IkkeEnGij]

but, after seeing the example, the following is right on target

if a table has duplicate rows, it is not a relation
and hence all bets are off when using sql

i would even put it stronger:
if a table CAN have duplicate rows, it is not a relation
and hence all bets are off when using sql