Solved

System resource exceeded and Query is too complex in Access 2007 after installing Windows 7 64-bit

Posted on 2016-08-13
17
88 Views
Last Modified: 2016-08-21
My MS Access database worked on Windows 7 32-bit. Then I installed Windows 7 64-bit and I get these messages in different parts of my database. Using Access 2007 (x86):

System resource exceeded
Query is too complex

The only other change I did was added 4 GB of RAM.

Really frustrating!
0
Comment
Question by:intoxicated_curveball
  • 6
  • 5
  • 3
  • +1
17 Comments
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 41755246
Try to trim those queries.
And /or please provide an example of one the queries that are too complex.

/gustav
0
 
LVL 57
ID: 41755383
a. Make sure the DB compiles.

b. Open the query in design view, try executing, and see if your prompted for anything.

Jim.
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 41755738
You might need to reinstall Office.  Having  64-bit Windows with 32-bit Office isn't a problem but I've always avoided upgrading Windows because of all the moving parts.  I just wait until I get a new computer unless there is something urgent.  

Going from 32-bit Windows to 64-bit is somewhat of a surprise since Windows would tend to be tied to the hardware and the hardware must have started out as 64-bit since that couldn't change without replacing the motherboard so I don't understand why 32-bit windows was ever installed.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 57
ID: 41756176
<<System resource exceeded>>

 With this error, there are any number of things that can trigger this.  However with this:

<<Query is too complex>>

 It's usually a reference/compile problem.   But to cover all the bases:

1. One thing that might have been done under the old OS is that the MaxLocksPerFile may have been set to a higher value.   In your app, add the line:

 DAO.DBEngine.SetOption dbmaxlocksperfile,200000

 You can also do this in the immediate (debug) window.

2. The other thing I would check is that the environment variables TEMP and/or TMP point to a valid drive/directory and that there is plenty of free disk space there.   Open a command prompt and type SET followed by a return to see where they are pointing.

3. Make sure you DB is not approaching the 2GB limit.  Do a compact and repair on it.

4. Last, try turning off any anti-virus product.   You may be getting some inference there.

Jim.
0
 

Author Comment

by:intoxicated_curveball
ID: 41756780
Jim: What are the steps to compiling the DB? Sorry if obvious, I never do this.

To your other post:

1. I tried to add DAO.DBEngine.SetOption dbmaxlocksperfile,200000 before the line of code that produces the error, but the error still occurs.... "system resource exceeded".

2. No issues with TEMP/TMP directories (plenty of space as well).

3. DB is only 2.5MB (after repair and compact).

4. Haven't installed any Anti-Virus. This is a fresh install of Windows 7 Home 64-bit.  Note: did first big chunk of Windows Updates last night. It did not help any of the issues.

Additional information, not only did this DB work on my Windows 7 32-bit (same computer) it also works fine (currently) on my Windows 7 Pro 64-bit pro at work! Only difference is I'm using MS Office 2010 (32-bit I think) at work (not MS Office 2007). So.. I may look into getting MS Office 2010 here.

PatHartman: I don't feel like reinstalling will do anything since this is literally a fresh install of MS Office and MS Windows.

Gustav Brock: Since it works on the other (almost same) systems, I'm not interested in reworking the queries but getting it to work on my home computer.

Thanks,
Chris
0
 
LVL 57
ID: 41756799
<<Jim: What are the steps to compiling the DB? Sorry if obvious, I never do this.>>

 Open DB with shift key held down.

 Wait till you see the Database window.

 Key Alt/F11 to bring up the VBA editor.

 Click on "Debug" on the menu bar and then "Compile"  it should do this without error.

<<Gustav Brock: Since it works on the other (almost same) systems, I'm not interested in reworking the queries but getting it to work on my home computer.>>

 This may be your only option if the compile works.  Access has some internal limits that float a bit based on what's going on.   From version to version it works a bit differently in each.

In the past, queries needed to "compile" into a single 64K memory segment or you got "Query too complex".  Microsoft lifted that limit, but they never said to what.

My guess is that Access has an internal memory heap it uses for various tasks and when it run's out, it's "System resources exceeded".

 Another limit like this is the table ID's.   The spec states it's 2048, and yet sometimes you can open more and sometimes you cannot.   This is another limit that seems to float a bit as one minute a query will run, and the next not when your right on the border.

 It is entirely possible that you were right on the border with this, and moving to a 64 bit OS is pushing it over the limit because Access is working with the OS differently.   Likewise it works in 2010 just because 2010 works a bit differently.

Jim.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41756829
Since it works on the other (almost same) systems, I'm not interested in reworking the queries but getting it to work on my home computer.

Well, yes, we are all lazy, but if it turns out as the only option?
Why not check out just one query? It may be nothing more than switching to SQL view, edit back and forth, and run again to rebuild the query optimizer.

/gustav
0
 

Author Comment

by:intoxicated_curveball
ID: 41756942
Jim: I clicked compile, nothing really happened, and then the option was grayed out after. Not sure if that's expected or not.

What I'm probably going to try next is Access 2010, since that's what I'm using at work.

Gustav: I did try removing one of the joins to see what would happen and no longer got the "System resources exceeded", but I just don't know how to trim my joins down at this time so I'll have to "table" this for now. I'm still able to use the program by VPN'ing to my work computer at least.
0
 
LVL 57
ID: 41756954
<<Jim: I clicked compile, nothing really happened, and then the option was grayed out after. Not sure if that's expected or not.>>

 That means it compiled with no errors.

Jim.
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 41757426
OK. What happens if you add the join back?

/gustav
0
 

Author Comment

by:intoxicated_curveball
ID: 41759951
Same issue if I add the join back.
0
 
LVL 57
ID: 41759963
Alias the table names.   Right click on the tables, select properties, and rename them to something short like "H1", "D1", etc.

That may get you in under the limit.

and curious, how many tables, joins, and fields are we talking about?  

Jim.
0
 

Assisted Solution

by:intoxicated_curveball
intoxicated_curveball earned 0 total points
ID: 41763264
Guys, I installed Office 2010 (32-bit) and it resolved all of my issues.
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 41764063
Isn't that what I suggested you do?
0
 
LVL 57
ID: 41764221
He didn't reinstall; he installed a different version of Office and that really wasn't a "fix" either, but more of a work around.

Jim.
0
 

Author Closing Comment

by:intoxicated_curveball
ID: 41764359
OK I modified my solutions here. Gustav, since reworking the queries was the technical solution (that I was unable to achieve at this time) and mine was the work around that worked for me.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
This Micro Tutorial will go in depth within Systems and Security in Windows 7 and will go into detail regarding Action Center, Windows Firewall, System, etc. This will be demonstrated using Windows 7 operating system.

733 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