Solved

Not enough space on temporary disk in Access 2003

Posted on 2014-02-04
16
1,406 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
16 Comments
 
LVL 22

Expert Comment

by:Kelvin Sparks
Comment Utility
What size has your mdb grown to?

Kelvin
0
 

Author Comment

by:Liberty4all
Comment Utility
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
Comment Utility
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
 
LVL 57

Assisted Solution

by:Jim Dettman (Microsoft MVP/ EE MVE)
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Jim, I forgot to mention I'm using a select query.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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
Comment Utility
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
Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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
Comment Utility
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 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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
Comment Utility
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 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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
Comment Utility
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 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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
Comment Utility
My solution solved the problem.  Please see comments above.
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Access 2010 ComboBox Requery Not Working 17 24
Splitting out Data 14 27
Access MDB/PDF 21 28
Max per month 3 13
In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

772 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

12 Experts available now in Live!

Get 1:1 Help Now