Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 7721
  • Last Modified:

Access 2010 - Memory allocation for Query

I have 2 Sony laptops and have been successfully been running an Access query successfully for several years.  I've decided to bring a backup Sony laptop to the same level.  Running the exact same Access files, the backup computer says query too large or temp storage not sufficient.
Computer 1 (works great) Sony SZ120, 32Bit, Vista Ultimate, 2GB, 300GB (141GB free), Access 2007
Computer 2 (problem) Sony Z520N, 64bit, Windows 7 Professional, 8GB, 300GB (125GB free), Access 2010

I have tried; compacting database, removing fields from query, deleting unused tables.  The .accdb file for computer 1 is 1,785,288KB and 514,444KB for computer 2.  Is there something with Windows 7 (64)/Access 2010 that is less efficient for memory management or is there something else I may be overlooking? The specific error (3138) is "The query cannot be completed.  Either the size of the query result is larger than the maximum size of a database (2GB) or there is not enough temporary storage space on the disk to store the query result".
0
jaustinr
Asked:
jaustinr
  • 2
  • 2
1 Solution
 
ste5anSenior DeveloperCommented:
hi,

the problem is the file size of 1,7 GB. While running a query it often needs temporary storage. In your case this means that the normal size plus the temp size hits the 2GB file size limit of Access:

http://office.microsoft.com/en-us/access-help/access-specifications-HP005186808.aspx

Thus the correct error message.

The question is: These different file sizes are for the same database file after running compare & repair?

There are only to thinks you may try:
1. Using SQL Server 2008 Express as back-end database
2. Splitting your database into front-end and back-end - due to the size eventually multiple back-end files- and using linked tables.

mfG
--> stefan <--
0
 
jaustinrAuthor Commented:
stefan,
Thanks for the info.  I suspected this issue.  What was surprising to me is that the exact same file/query runs ok on a Vista/32/Access 2007 machine and not the Win7/64/Access 2010 machine.  I guess the later must take up more overall space.  With 8GB the WIn7 computer should not be limited by storage. Also, I compacted the file and it starts at around 500MB and after I try an run the query shows to be around 1GB.   I appreciated the link to all the Access specifications.  Are there any MS tools (or 3rd party) to analyze an Access file to see where I might be coming close (or over) any of these boundaries?

jrummel
0
 
ste5anSenior DeveloperCommented:
hi,

no there is no such tool. The problem is that any non trivial query or operation can use a larger amount of temporary space. This cannot be guessed. The only thing you can do is to use a separate file for temporary tables and objects:

http://www.granite.ab.ca/access/bloatfe.htm
0
 
jaustinrAuthor Commented:
ste5an,

Thanks for additional link.

jarummel
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.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now