System Resource Exceeded

[Stu]

I just got this message "System Resource Exceeded". I am
curently creating a very large query, with many equations
that is going to be very close to the maxspace allowed.
What is that message and is there anything that can be
done to work around it? Thanks - Stu

 

[Tom Ellison]

Dear Stu:

I can offer sympathy and then the solution I found. I think you're
going to like the sympathy better than the cure.

Very soon after I ran into the resources difficulty I began to
discover many other limitations of the Jet database engine. For a
period of time I was spending more time trying to get queries to work
than I was spending building forms and reports, and it really
shouldn't be that way. It got to the point that I was beginning to
think I didn't really know how to write good queries.

I decided to try the MSDE database engine as a substitute. I am now
writing queries that are far, far larger and more complex than those
with which I formerly had trouble using Jet. It turned out I actually
DID know how to write queries, but Jet couldn't run them.

So, I suggesting that you may be at a point where you have outgrown
the capacities of the Jet database. I cannot tell for sure from here.

An alternative, but perhaps very temporary solution, is to increase
the system resources. This depends on things like what OS you have
and how much memory the computer has. However, if you're pushing the
limits of this, and if your application will be distributed to
computers which would then also require hardware changes and tinkering
this may be a problem of itself.

Switching to a client/server setup does have the advantage of
requiring you to have only ONE computer that can run your queries, and
do so with good performance. With Jet, you must be able to run your
queries effectively on EVERY DESKTOP, which can become quite a
challenge.

Now, this may not really be the case for you, but it fits the pattern
I saw here.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Stu]

Thanks for your help Tom! -Stu

-----Original Message-----
Dear Stu:

I can offer sympathy and then the solution I found. I think you're
going to like the sympathy better than the cure.

Very soon after I ran into the resources difficulty I began to
discover many other limitations of the Jet database engine. For a
period of time I was spending more time trying to get queries to work
than I was spending building forms and reports, and it really
shouldn't be that way. It got to the point that I was beginning to
think I didn't really know how to write good queries.

I decided to try the MSDE database engine as a substitute. I am now
writing queries that are far, far larger and more complex than those
with which I formerly had trouble using Jet. It turned out I actually
DID know how to write queries, but Jet couldn't run them.

So, I suggesting that you may be at a point where you have outgrown
the capacities of the Jet database. I cannot tell for sure from here.

An alternative, but perhaps very temporary solution, is to increase
the system resources. This depends on things like what OS you have
and how much memory the computer has. However, if you're pushing the
limits of this, and if your application will be distributed to
computers which would then also require hardware changes and tinkering
this may be a problem of itself.

Switching to a client/server setup does have the advantage of
requiring you to have only ONE computer that can run your queries, and
do so with good performance. With Jet, you must be able to run your
queries effectively on EVERY DESKTOP, which can become quite a
challenge.

Now, this may not really be the case for you, but it fits the pattern
I saw here.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts



.

 

[JIMMIE WHITAKER]

You might try to use code instead of a query. I even have one long
procedure I wrote in visual basic with thousands of loops.
In access it took about 3 minutes. In vb it takes 3 seconds. I just made
an exe file and call it at the correct time. Remember, you can do anything
in code you can do with a query and more. Code on long complex procedures
is usually faster. And if you have visual basic, then it goes even faster
is exe. In my case it's doing progency (reverse pedigrees ) on dogs. Some
have 75+ pages of offspring and that's just a 5 generation. If i attempted
this usings querys, it would probably take 500 years. I run all through
arrays (VERY VERY FAST), and like I said from minutes down to seconds. You
just have to know how to program. Good luck