jaustinr
asked on
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".
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".
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ste5an,
Thanks for additional link.
jarummel
Thanks for additional link.
jarummel
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 <--