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

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!
intoxicated_curveballAsked:
Who is Participating?
 
Gustav BrockCIOCommented:
Try to trim those queries.
And /or please provide an example of one the queries that are too complex.

/gustav
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
a. Make sure the DB compiles.

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

Jim.
0
 
PatHartmanCommented:
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
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.

 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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
 
intoxicated_curveballAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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
 
Gustav BrockCIOCommented:
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
 
intoxicated_curveballAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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
 
Gustav BrockCIOCommented:
OK. What happens if you add the join back?

/gustav
0
 
intoxicated_curveballAuthor Commented:
Same issue if I add the join back.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
 
intoxicated_curveballAuthor Commented:
Guys, I installed Office 2010 (32-bit) and it resolved all of my issues.
0
 
PatHartmanCommented:
Isn't that what I suggested you do?
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
 
intoxicated_curveballAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.