Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

"too complex expression" error message

Posted on 2000-04-27
10
Medium Priority
?
297 Views
Last Modified: 2009-07-29
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?
0
Comment
Question by:Galumpen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 6

Expert Comment

by:simonbennett
ID: 2754891
Hi there..

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
0
 

Author Comment

by:Galumpen
ID: 2754923
The only thing the autoexec does, is to
hide the databasewindow, open
the main menu form, and movesize.

The database is appr. 100Mb.
0
 
LVL 3

Expert Comment

by:MikeRenz
ID: 2754940
do you have relationships across your tables?
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:Galumpen
ID: 2754960
Yes.
15 tables have relationships between
them.
0
 
LVL 3

Expert Comment

by:MikeRenz
ID: 2754977
this could be part of the problem.  Do you have joins on text fields?  Are they ALL numbers (long integer, or smaller)?
0
 

Author Comment

by:Galumpen
ID: 2755014
Most of the joins are between text fields.
A couple is between autonumber.
0
 
LVL 3

Accepted Solution

by:
MikeRenz earned 900 total points
ID: 2755059
you should not join text fields.  This is a HUGE no-no.  The tables which are joined by text fields, add ID fields (either number, or autonumber (depending on the type of table) and then make the joins on these ID fields.  It may take a little while to add all this to your database (a lot of update queries), but the speed increase should be dramatic.

This 'should' fix your complexity issue also.
0
 
LVL 58
ID: 2755314
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.

0
 
LVL 9

Expert Comment

by:perove
ID: 2755954
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

0
 
LVL 7

Expert Comment

by:JimMorgan
ID: 2756277
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
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question