Access: Not enough space on temporary disk error

Question

I am getting the following error:

"Not enough space on temporary disk. (Error 3183)"
"You tried to perform an operation that requires more space than is available on the temporary disk. Your temporary disk location is based on the TEMP DOS environment variable, which was set when your system started."

I am using Access 2007 as a front end. The database is on SQL Server 2008. This happens when I am using a form which it's record source is a SQL query and am going to a specific record using the navigation control. The table has about half a million records. I am on XP SP3 with 4GB RAM and there is 1 GB free.

Answer 1

You are most likely running into the 2GB limit on MS Access tables. Possibly in a temporary table being create/used during the query.

This is probably caused by whatever joins you have in your query, which tend to multiply the data. And with a million rows, all you have to do is multiple it by 2K of data per row to hit that limit)

You can solve this in a couple ways.

• You can move the biggest limiter in your where clause to the end (as I recall, the SQL is execute from the bottom up).
• You can simplify the query
• You could break the query up into subqueries, each which its own where clause, and then do a join on the results.

You might also be able to force the query to run on the SQL Server side; which I think happens by default but can be thwarted by an inefficient query (e.g., a calling a function that returns varchar in the where clause)