"Not enough space on temporary disk" for very large table

[Margaret Bartley]

I have a bit more than 2 million fairly small records in a database that's
311 MBs in size. There is only this one table, a simple totals query, and a
few linked tables in this DB. I compacted and recompiled it just before
trying to add an Atonumber field.

When I try to save the table with that new field, I'm getting a message,
"Not enough space on temporary disk".

I defragged the hard drive yesterday, and I've got 110 GBs left empty on a
148 BG hard drive, and no other programs open.

I suspect this error message is not exactly correct, that there is some
Access memory-management variable or file that is being overloaded. Any
hints or help? Maybe a better newsgroup to post to ?

Many thanks.

 

[Chris O'C via AccessMonster.com]

Make a copy of the table, but with no data. Add the Autonumber field to the
copy. Create an append query to append the records from the original table
to the new table and then run the query. When you're sure you don't need the
original table, delete it and rename the new table with the original table
name. Set up the relationships the same as the original's.

Chris
Microsoft MVP

 

[Jeff Boyce]

Everything Chris suggested ... and make a backup copy of your db before you
do it!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

 

[Margaret Bartley]

yep, That's what I did, and it worked.

Make a copy of the table, but with no data. Add the Autonumber field to
the
copy. Create an append query to append the records from the original
table
to the new table and then run the query. When you're sure you don't need
the
original table, delete it and rename the new table with the original table
name. Set up the relationships the same as the original's.

Chris
Microsoft MVP