System resource exceeded

[fdormoy]

Hello,

I am facing a very annoying problem.
I have a dtabase made up of a table with 31 fields and 2.7 milion records.


The overall size of the database is 0.7 gig only.
When I make a query, and it can be a very simple query, sometimes it works, sometime I have a System resource exceeded message.
When I have this message I make a compact-repair and then after that, in some cases it solve the problem and on other cases I stiil have this System Resource Exceeded.
Then I try to make a second compact-repair and some times it solves the problem and in other cases not.
Then I try to exit Access and turn off the computer to restart and opening the Access file, it solves the problem and in other cases it does not solve it.
It looks like this error message is not systematic and may be influenced by certain configuration that I can't control.
Could someone tell me how to cope with that problem?
Thanks in advance

I give you more precisions:

I am using an I-Mac computer with 8Gb RAM and a virtual PC using Parallels 9 for Mac in which I installed Microsoft office 2010

[burrina]

Complex queries are generally the result of this and sometimes a bad design issue as well. When all else fails, try to simplify as best as possible.
With your unique setup, it could also present a problem!

[fdormoy]

I guess the problem is that Access software is putting many restrictions that are not necessary because computers evolved rapidly, with more RAM memory, more powerful processor, and unfortunately Access did not evolve the same way. It is too bad that Access designers did not make the effort of adapting to that computer trend

[John_G]

Did you try splitting the database so that the tables are in their own file? By doing this, the front-end will be much smaller. The queries may be more successful, since they run in the front-end.

John

[fdormoy]

It would be rather difficult to split the database since many tables are inter-related.

[burrina]

Splitting the db does not change your relationships.

[John_G]

When you split the database, you should really move ALL the tables to the back-end, except possibly those used for temporary storage or working data.

Any relationships you have defined are kept in the back-end, and changes must be made there (i.e. by opening the back-end database itself).

John

[fdormoy]

Is there any tutorial explaining how to do that ?