Not Enough Space Temporary Disk (2003)

Topic

New Reply

Hello.

Before posting I searched the Lounge but the respective threads did not help.

I have an .mdb database with several tables. One of them (henceforth “the table”) has some 400,000+ records, and is a portion of a bigger table. The database is 1.2 GB big, so being conservative the table should be at the very most 600 MB.

Now, the problem. I want to insert a primary key field in the table. I first tried creating an autonumber, autoincrement long field using the GUI (ie, not via SQL commands). Before saving I also set it to be the primary key. When I save, Access takes a while and then throws the message:

“Not enough space on temporary disk”

The prompt has two options: “Ok” and “Help”. When I choose the latter, the following message appears:

---

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.

For example, you may be trying to create a query that creates temporary files larger than the temporary disk. Reduce the size of the temporary files by accessing smaller amounts of data at one time or increase the size of the temporary disk.

You can increase the amount of available temporary disk space in several ways:

Select fewer records. Dynaset-type, forward-only

Reply | Quote

Replies

I’m guessing that the HD actually does have plenty of space on it? How much free space do you have on the Temp file drive?
Peter

Reply | Quote

Hi Peter,

Yes, my only drive is C: and there are 8 Gig free. Thanks.

Reply | Quote

I think you’re running into the file size limit for Access. This limit is 2 gigabytes minus the space needed for system objects such as the MSysIndexes table. My guess is that Access makes a temporary copy of the table when you’re adding a field and/or index, causing the total size to approach 2 GB.

If you really need databases this big, consider using SQL Server instead of Access to store the data.

Reply | Quote

Hello Hans,

Thanks for your response. I thought that since the table, at most, represented 600 MB out of the 1.2 GB, adding a new field shouldn’t take as much as the whole table and thus it shouldn’t be an MDB size limite issue. However your consideration of the temporary copy of the table made me rethink about it.

What I tried doing is import the table to a new .mdb file and then tried adding the autonumber field there. The size limit should indeed be far from being an issue. However, I got the same prompt. I checked the %temp% folder and the files showed a similar behaviour, increasing to 222 MB, a short pause, then the prompt.

I somehow happened to think that maybe the 222 MB limit was to this “temporary” file where Access would work when adding the field (maybe rewriting the whole table as you say), and so deleted some fields (some of the text fields, which I think are more HD consuming) in the hope maybe the JETE22A.tmp file wouldn’t rocket to the limit. I don’t know if I was right or not, but the fact is it worked.

Thanks for your help!

Reply | Quote