"Query is too Complex" Error

[1001011010]

For over a year now our company has been running a Tracking software written in VB6 with a Microsoft Access backend.

Just recently we've been getting a "Query is too complex" on some of our forms. Now, I don't have the source code for our tracking system, but I'm 99% sure that all these forms are doing is Selecting and then Updating single records in the Access database.

I highly doubt the problem is with the VB6 program since it has been working perfectly till now.

Has anybody been able to resolve such an issue before? This error is really hampering our productivity.


Note: For about 2 months now we've begun compacting our database nightly using JetComp. Could this be the cause of our agony?

 

[Golom]

First, JetComp is really intended for disaster recovery. It will do things that the integrated "Compact and Repair" facilities in Access cannot do. There is also the possibility that the version of JetComp that you are using is not completely compatible with the version of JET that you are using (have you upgraded to a different version of Access?)

Second, are you building queries dynamically in VB? I have found that, in some cases, "Query too Complex" can be the result of malformed SQL (spurious quotes or use of special characters like [,],?,#, etc. for example) rather than an SQL statement that is really too complex. You will need to get into the code and check to see if some of the generated SQL involves these problems.