Error 3183 when running query

[teach314]

hello

I have SQL statements generated in VBA, that occasionaly hang when executed (there is no error message - it just freezes). To diagnose the problem, I put the SQL into a stand-alone query. When run, it gives me Error 3183 after about 10s - "Not enough space on temporary disk." When I click 'Help', it mentions that the problem involves the TEMP DOS Environment variable.

The problem is not in the query itself - it runs fine for the vast majority of the cases I'm dealing with. But, on occasion, I get this hanging problem. (the query involves an INNER JOIN between a table and a copy of itself. The table has approx. 4.5 million records.)

Here is my question. Is there a way to deal with this sort of problem without rewriting the code. If I need to, I will break the tables up into smaller chunks and process them in sequence, but I'd prefer an easier fix, like adjusting the TEMP DOS Environment variable if that's possible??? (by the way, I'm using Windows 7, 64-bit, and I have over 800 GB of free space on my C: drive. Despite the message, I don't think the capacity of the hard drive is the issue)

thanks you
Teach 314

 

[genomon]

Are there indexes/PKs on the table?

Beir bua agus beannacht!

 

[teach314]

Yes. There is a PK (Long), 3 integer fields all indexed, and 2 integer fields, unindexed.