Galumpen
asked on
"too complex expression" error message
I've got an Access-97 database that was
autoconverted from an Access 2.0 database.
Every now and then (when the database
is getting too large) i get the error
message
"This expression is typed incorrectly,
or it is too complex to be evaluated.
For example,a numeric expression may
contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables"
when I try to open a form, or even enter
the database.
If I run a "compact database" the
problem dissapears for a week or so,
until the database has grown larger.
Then it pops up again.
What exactly is my problem?
Does this occur because it is an
autoconverted base, or is there
somthing I can do about it?
autoconverted from an Access 2.0 database.
Every now and then (when the database
is getting too large) i get the error
message
"This expression is typed incorrectly,
or it is too complex to be evaluated.
For example,a numeric expression may
contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables"
when I try to open a form, or even enter
the database.
If I run a "compact database" the
problem dissapears for a week or so,
until the database has grown larger.
Then it pops up again.
What exactly is my problem?
Does this occur because it is an
autoconverted base, or is there
somthing I can do about it?
ASKER
The only thing the autoexec does, is to
hide the databasewindow, open
the main menu form, and movesize.
The database is appr. 100Mb.
hide the databasewindow, open
the main menu form, and movesize.
The database is appr. 100Mb.
do you have relationships across your tables?
ASKER
Yes.
15 tables have relationships between
them.
15 tables have relationships between
them.
this could be part of the problem. Do you have joins on text fields? Are they ALL numbers (long integer, or smaller)?
ASKER
Most of the joins are between text fields.
A couple is between autonumber.
A couple is between autonumber.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The problem is is that there are certain built in limits for how much memory is used to process queries. In A2, only a 64K segment was available. Starting with A95, this restriction was removed, but it's still possible to bump into the limit (the limit seems to float and has never been documented, so it's hard to pin down).
There is also a limit on the length of an SQL statement.
As Mike suggested, fixing the joins is the first step and probably will be the only!
Good luck!
Jim.
There is also a limit on the length of an SQL statement.
As Mike suggested, fixing the joins is the first step and probably will be the only!
Good luck!
Jim.
You could also also try to import the whole database into a new one, the autoconverter are not completly perfect, by doint this all the "sysobjects" are "fresh" and this might do the trick!
Also upgrade to SP-2 for office if this is not done
perove
Also upgrade to SP-2 for office if this is not done
perove
One of the first things that I would do is to 'decompile' the DB and then recompile it. This fixes a multitude of sins.
Add /Decompile to the command line to call Access. If the program runs when opened, hold down the shift key to prevent it from starting. When the decompile is finished, immediately quit Access.
Open Access normally, holding down the shift key so that it will not run. Open any code module and 'Compile and Save All'. If there are any major snafus from the autoconversion from Access 2.0, this should catch them.
Please be sure that you have all the service packs and service releases up to date. That is SP-2b for Access and SR-3 for Jet.
Jim
Add /Decompile to the command line to call Access. If the program runs when opened, hold down the shift key to prevent it from starting. When the decompile is finished, immediately quit Access.
Open Access normally, holding down the shift key so that it will not run. Open any code module and 'Compile and Save All'. If there are any major snafus from the autoconversion from Access 2.0, this should catch them.
Please be sure that you have all the service packs and service releases up to date. That is SP-2b for Access and SR-3 for Jet.
Jim
Do you have anything running in the Autoexec macro? If so, what does it do?
It sounds like you have some code somewhere like...
Do while not rs.eof
str = str & "'" & rs![FieldName] & "',"
rs.movenext
loop
str = left(str,len(str)-1)
SQL = SQL & " WHERE Field IN(" & str & ")"
....or similar.
Does any line of code get highlighted? (Im guessing no, otherwise you would have mentioned it)
How large is your .mdb at the moment? Acc97 can handle up to 1GB (but would return a different message I would have though)
Any other info?
Good Luck
Simon