Access 97 - out of temporary disk space

I have a '95 machine with 5 gigs of hard drive.  I am trying to run a fairly complicated report.  It involves taking fields from about 20 other tables.  There's about 70 fields and the only way I could get them all into a report was to create a query to bring them in together.  Then I could create a report based on that query.  The problem is when I ran the query last time (I had just added one more field that I'd forgotten) it took about 45 minutes and then it said it had run out temporary disk space.  Is this normal?  One table does have 40,000 records and the others have numerous other sizes, but I thought this 5 gig machine could handle it.  Any ideas?
lowebAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
aburrConnect With a Mentor Commented:
But what else is running on your machine. All of your HD may not be available to Access. Increase the size of youn swap file.
0
 
BelzCommented:
Can you take your one query and break it down into smaller queries.  Can you filter the larger tables done sooner and user smaller tables in your final query.  I had a table with 500,000 records that was being attached to a table with 200 records.  It runs a lot quiker to filter(or sum) the large table down to the records I will be using then perform the query joins
0
 
Eli_LinkovCommented:
1. RAM size?
2. Free disk space?
3. Swap file type?

You did not enter this data, but try to increase all of these, in this order.
0
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
lowebAuthor Commented:
How do you increase in the swap file in Windows '95? I know in Nt and OS/2 you can but I didn't think it was possible with '95.

0
 
lowebAuthor Commented:
Eli, I really don't have the option to increase the RAM size.  I have deleted every program and data off of the hard drive so that there is almost all of the 5 gigs available.  I'm not sure about the swap file type.  This is a '95 machine and I didn't think you could increase the swap file.
Thanks!
0
 
lowebAuthor Commented:
Belz:
I've already broken the one file down from 90,000 records to 45,000 records each.  Because all tables are related I really can't filter or reduce any of them.
Thanks!
0
 
BelzCommented:
Are you actually using all 45,000/90,000 recordsin the result set. If not can you create a query that would create a record set containing only the fields you want and the records you want.  Then in a second query use the results of the first query and do your relations to the other tables and queries.
0
 
lowebAuthor Commented:
Yes, I'm using all 45,000 records in the report.  What the user is trying to do is for each species (the 45,000 record table) have two group headers that contain the location and sample data (both different tables) for the species.  So all species with location 1 and sample number 1 would be grouped under the appropriate headings, etc.  Also there are 10 - 15 tables that contain data based on codes that have to be drawn into the report, too.  Ie: If the code is 21 then the report pulls in the county name, Boone from the county table.  As you can see it's pretty complicated.  I may just be taxing the capabilities of Access and the machine.  The user is asking a lot!
Thank you for your help.

0
 
BelzCommented:
By the way their is not a SWAP file in Win95 theirs a virtual memory setting.  It functions differently than a swap file from Win3.11.  Go into the "Control Panel" then in the the "System".  Then under the "Performance" tab.  The "Virtual Memory"  button.  I would not set this!!!  It should be set to "Let windows manage my virtual memory". Swap files died under Win3.11.  If your using Access97 you may want to download the new JetEngine v3.52 from MS  it fixes a lot of little weird errors and expands some limitations.  Chances are you are running v3.5 or v3.51.
0
 
aburrCommented:
I would expect 5 GB to be big enough also, especially if you have taken everything else off. You can set the maximum size of the virtual memory (swap file) from control panel. Display the Systems Properties sheet. Click the Virtual Memory tab. There you can set the max memory to be used.
You can look in System monitor, Memory Manager:Swapfile size to see just how much virtual memory is being used. (Yes, it does say Swapfile size)
-
Another possibility is to increase the Jet databse engine's internal cache. Do this by adjusting the MaxBufferSize ISAM settings in the Windows registry. There is no hard and fast rule for what to set the MasBufferSize. The default is 512. Some general settings are 2048 for computers with 16MB RAM, 3072 for computers with 24MB.
The settings need to be placed under the WindowsRegistry key: \HKEY_LOCAL-MACHINE\SOFTWARE\MICROSOFT\
JET\3.0\ISAM. If you jet engine is not 3.0 look for what you have. Be careful of changing the registry. Back it up first.  
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.