Solved

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

Posted on 2016-08-13
17
60 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 49

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 34

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
 
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 49

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
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 49

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 34

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
This Micro Tutorial will teach you how to change your appearance and customize your Windows 7 interface to your unique preference. This will be demonstrated using Windows 7 operating system.
This Micro Tutorial will give you a introduction in two parts how to utilize Windows Live Movie Maker to its maximum editing capability. This will be demonstrated using Windows Live Movie Maker on Windows 7 operating system.

760 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now