How Do I Diagnose Expression Too Complex PLease?

[gray]

Hi

Access 2007, MDB file, Local Machine

Here's a cracker!... I have a VB module in Access which I run from a Macro Action... It fails with Error 16 - Expression Too Complex.

When I run it in debug it works perfectly... many times over!

Is there some way I can at least find the line number it fails at in non-Debug?

Or any other clues anyone?

Thanks

Later... I changed nothing and suddenly it works normally again... Later still ... it fails with Expression too complex again....I wonder if this is a system resource issue of some kind??

I've a feeling it's failing when I use this SQL to populate an ADO RS...

SELECT TBL1.* FROM Addrs_To_Cntcts_Home AS TBL1 WHERE (TBL1.Record_Locked <> True OR TBL1.Date_Record_Lock_Last_Updated <> #31/03/2012 16:31:41# OR TBL1.Record_Lock_Last_Updated_By_Unique_No <> 6 ) AND (TBL1.Deleted=False OR TBL1.Deleted=True) AND Addrs_Unique_No IN (SELECT Unique_No FROM Addrs AS TBL2 WHERE (TBL2.Deleted=False) AND TBL2.Unique_No IN (757,761,777,780,782,806))


P.S. I know some of the brackets are irrelevant to SQL here but I use them to identify parts of the clause in order to line-edit the source.

 

[jdraw]

This seems to select all records in TBL1

(TBL1.Deleted=False OR TBL1.Deleted=True)

Why do you need it?
Why do you use alias names?
Also Addrs_Unique_No may beed a TBL1 qualifier.

Just some thoughts to consider.

 

[gray]

Hi

Sorry for the late reply...

I use (TBL1.Deleted=False OR TBL1.Deleted=True) because I dynamically line-edit the SQL string (using the replace function) so it acts as a placeholder.

Why do you use alias names? Long story this... but I often join tables for my form recordsources... and again I line-edit the SQL so even if a table is not joined I leave an alias in there.

Done some further tests and the proc does not alwqays fail when populating an RS.

Thinking it might be an SQL transaction that might not have commited in time I put a pause in with a DoEvents... the proc now fails at that pause....

As I say it runs perfectly when I am in Debug.... I'm sure it's related to some system resource cos' when I close the DB and re-open it... the same code flies thru' perfectly!

Another Arthur C Clarke Mystery of the Universe....

 

[jdraw]

You seem to do a lot of syntax critical work via a Replace function which may be part of the issue.
Do you not have SQL and some logic in your vba to handle the various conditions?

Another thought would be to use some Debug.print statements to display your "modified" SQL before actually running. You may see something in the rendered SQL.

Just my $.02

 

[gray]

Hi

Thanks for the reply, always gratefully rec'd! .... Yes, I do a fair bit of string manipulation for SQL calls.

Since re-starting the Db everything has run perfectly. The Replaces are only done on quite modest-sized strings but some of the resulting strings do get quite long (nowhere near the string limit tho')... I might break them up a little and make sure I clear the string variable (with SQL_Str ="") before re-assigning it .... maybe the memory is not being relaeased propely.... thnx