Solved

Not enough space on temporary disk in Access 2003

Posted on 2014-02-04
16
1,642 Views
Last Modified: 2014-02-11
I'm trying to run a simple query on four linked tables and keep getting the message "Not enough space on temporary disk".  Each table has between 52,000 - 55,000 records.  The files are in a Citrix environment, and the server on which they are located has plenty of space and memory to handle this project.

I copied the files to my PC and get the same error message.  My PC has plenty of free space so I don't think the error has anything to do with lack of space or memory.  I compacted the files but that did not solve it.  I can open each linked table without a problem.  The fields on which I'm joining have been indexed.  The data files are confidential so am not able to post them.  

Any suggestions on what I should try next would be appreciated.
0
Comment
Question by:Liberty4all
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
16 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
ID: 39834298
What size has your mdb grown to?

Kelvin
0
 

Author Comment

by:Liberty4all
ID: 39834331
The file containing the query is only 228 KB.  The "back end" file containing the source tables is 258 MB and has been compacted.
0
 
LVL 10

Assisted Solution

by:Gozreh
Gozreh earned 250 total points
ID: 39834597
are you sure that all 4 table are joined correctly ??  it sounds that this caused by whatever joins you have in your query which tend to multiply the data to millions of records.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 58

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 39835482
What type of query?  More then likely, your running out of locks.

You also should check the environment variables TEMP and TMP to see if they point to valid drives/directories.

Jim.
0
 

Author Comment

by:Liberty4all
ID: 39836418
To answer Gozreh's question, the joins are all inner joins and the fields used are correct.  I tried using right outer joins but got the same error.

Jim, can you please explain what I must do to check the environment variables TEMP and TMP to see if they point to valid drives/directories?  I have not done this before.
0
 

Author Comment

by:Liberty4all
ID: 39836425
Jim, I forgot to mention I'm using a select query.
0
 
LVL 58
ID: 39836454
<<Jim, can you please explain what I must do to check the environment variables TEMP and TMP to see if they point to valid drives/directories?  I have not done this before. >>

 Drop to a command prompt (DOS box) and type:

SET

followed by enter.

In there, you will see TEMP and/or TMP.  See where there pointing to.

JET looks for these when it's going to create temp files on disk as part of recordset operations.  This is outside of the page cache it also maintains.

Jim.
0
 

Author Comment

by:Liberty4all
ID: 39836502
Windows cannot find a directory for SET and TMP in the command prompt window but TEMP is located at C:\WINXP\TEMP.  The C drive has 18.3 GB free space which = 39.4%.  Does this answer the question?
0
 
LVL 58
ID: 39836576
<<command prompt window but TEMP is located at C:\WINXP\TEMP. >>

  You need to do that in the Citrix environment as well.  When you run under Citrix, you actually have a session on the server.

 With that said, I don't believe this is your problem, but it's one of those things that's often overlooked and easy to check.

Jim.
0
 

Author Comment

by:Liberty4all
ID: 39836621
I don't have access to the TEMP directory on Citrix and no TMP directory in Citrix exists.  However, a server technician did check to see if there was adequate space on the server for running this type of project and said there was.  

I'm creating a work around by using only two tables at a time in my queries and then having each query create a temporary table that is joined to others to create one large table.  This reduces the number of records to only 264 in each table.  The downside is this requires having to create many more queries but at least it should work.

I will keep your comments on checking for temporary disk space for future reference.  If you have any more thoughts on what I should try next I would appreciate it.
0
 
LVL 58
ID: 39836705
<<If you have any more thoughts on what I should try next I would appreciate it. >>

 Do have one.  In your code, try doing:

DAO.DBEngine.SetOption dbmaxlocksperfile, 200000

Before you run this and see if you still have a problem.

Also, make sure in your queries that you specify only the field names you need.  Don't use * if you really don't need all the fields.

Jim.
0
 

Author Comment

by:Liberty4all
ID: 39837039
I added DAO.DBEngine.SetOption dbmaxlocksperfile, 200000 to the code before running the query and got "system resource exceeded" which, I'm guessing, means the same thing as "Not enough space on temporary disk".  

I never use "*" to select fields in my queries as I only select those which are needed.
0
 
LVL 58
ID: 39839725
<<I added DAO.DBEngine.SetOption dbmaxlocksperfile, 200000 to the code before running the query and got "system resource exceeded" which, I'm guessing, means the same thing as "Not enough space on temporary disk".  >>

 Not quite.   You've gotten around the one problem and now have another as a result of the complexity of the query.

Is A2003 running on a 64 bit multi-core system?

Jim.
0
 

Accepted Solution

by:
Liberty4all earned 0 total points
ID: 39840699
Jim,

I don't know if Access 2003 is running on a 64 bit multi-core system, but the good news is I found the solution.  My original query had two inner joins based on State and AA (area defined by counties).  After I finished creating my work around I noticed the total number of records in the queries was exponentially higher than what it should be.  I added a third join on a field named Institution which is a count of records by institution.  When looking at the tables it does not appear such a join is needed but it obviously makes a difference.  I created the original tables many years ago and forgot that a third join was probably necessary to work with them.

I appreciate all the time you spent trying to help solve this for me.  The fault was mine but I just could not see it.

Bryan
0
 
LVL 58
ID: 39841595
Glad to hear you figured out a way to optimize it, because that's where we were heading; the operation was just too complex and needed to be re-factored.

Jim.
0
 

Author Closing Comment

by:Liberty4all
ID: 39849691
My solution solved the problem.  Please see comments above.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

691 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