-2147467259 error: "The query is too complex" 1

[Bresart]

Hi, in the code
Dim cn as ADODB.Connection
strQuery = "UPDATE tblTaxonomia INNER JOIN tblCalendario ON tblTaxonomia.Id = tblCalendario.idEspecie SET tblCalendario.visible= No WHERE (" & condWhereNotLike & ");"
Set cn = CurrentProject.Connection
cn.Execute strQuery


i get the error '-2147467259 (80004005)' in running time: "The query is too complex".

It occurs when the string 'condWhereNotLike' is too long. The condWhereNotLike string is similar to this:

"tblTaxonomia.Id <> 500 And tblTaxonomia.Id <> 501 And tblTaxonomia.Id <> 502 And ..."

Any idea for going around that?

Thanks in advance.

 

[PHV]

You may try to replace this:
"tblTaxonomia.Id <> 500 And tblTaxonomia.Id <> 501 And tblTaxonomia.Id <> 502 And ..."
with this:
"tblTaxonomia.Id Not In (500,501,502, ...)"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Bresart]

Thanks PHV.

Would it give an error when too many Ids are included in the expression?

 

[dhookom]

Would it give an error when too many Ids are included in the expression?

Please try it and let us know. Many times it is easier and faster to try something rather than come here with a question. Trial and error is how many of us learn.

Duane
Hook'D on Access
MS Access MVP

 

[Bresart]

Thanks dhookom. The try-error method isn't the best one, and in this forum it's not the only way of doing.

PHV, the code stops with that modification, surely because the SQL statement is longer than 1.024 characters, that's the limit for SQL query like Access advises in other case in the same DB. I have 293 records, and the Ids are three digits long, four digits with the comma; it results more than 1.024 characters.

 

[dhookom]

Create a table of all the unique values so you don't need to build a string of values where you can use a table of values.

Duane
Hook'D on Access
MS Access MVP

 

[Bresart]

Thanks dhookom. I tried that but:

The form is based in the table tblTaxonomia, and this procedure needs that the recordsource of the form be a query with the table tblTaxonomia and this with a one-to-one relation with this new table. Changing the recordsource property of the form would give other problems.

Is there another way of updating the form recordset with the query result?

Isn't there a less arduous way of doing the same, the running of a SQL query with very long string, than changing the recordsource property of the form?

 

[dhookom]

I'm not sure what a "form" has to do with this update query question.

strQuery = "UPDATE tblTaxonomia INNER JOIN tblCalendario ON tblTaxonomia.Id = tblCalendario.idEspecie SET tblCalendario.visible= 0 WHERE tblTaxonomia.ID Not In (SELECT ID FROM NewTable)"

Duane
Hook'D on Access
MS Access MVP

 

[Bresart]

Thanks dhookom. Replacing the variable by

(SELECT ID FROM NewTable)

works.