Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2016-08-13
17
Medium Priority
?
143 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 52

Accepted Solution

by:
Gustav Brock earned 2000 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 58
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 40

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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 58
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 58
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 52

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 58
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 52

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 58
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 40

Expert Comment

by:PatHartman
ID: 41764063
Isn't that what I suggested you do?
0
 
LVL 58
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Suggested Courses

916 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