Not enough space on temporary disk in Access 2003

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.
Liberty4allAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Liberty4allConnect With a Mentor Author Commented:
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
 
Kelvin SparksCommented:
What size has your mdb grown to?

Kelvin
0
 
Liberty4allAuthor Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
GozrehConnect With a Mentor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
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
 
Liberty4allAuthor Commented:
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
 
Liberty4allAuthor Commented:
Jim, I forgot to mention I'm using a select query.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
Liberty4allAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
Liberty4allAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
Liberty4allAuthor Commented:
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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
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
 
Liberty4allAuthor Commented:
My solution solved the problem.  Please see comments above.
0
All Courses

From novice to tech pro — start learning today.