Solved

Access 97 - out of temporary disk space

Posted on 1998-11-16
10
649 Views
Last Modified: 2011-10-03
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?
0
Comment
Question by:loweb
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 27

Accepted Solution

by:
aburr earned 100 total points
ID: 1968540
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
 
LVL 2

Expert Comment

by:Belz
ID: 1968541
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
 

Expert Comment

by:Eli_Linkov
ID: 1968542
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
 

Author Comment

by:loweb
ID: 1968543
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
 

Author Comment

by:loweb
ID: 1968544
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:loweb
ID: 1968545
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
 
LVL 2

Expert Comment

by:Belz
ID: 1968546
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
 

Author Comment

by:loweb
ID: 1968547
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
 
LVL 2

Expert Comment

by:Belz
ID: 1968548
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
 
LVL 27

Expert Comment

by:aburr
ID: 1968549
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

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

707 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

13 Experts available now in Live!

Get 1:1 Help Now