There isn't enough disk space or memory

Question

I'm trying to change the field format of 1 column in a table. But I keep getting a"There isn't enough disk space or memory" error...I've searched for solutions but I haven't found anything that applies to MS Access 2013

Answer 1

If this is a large table, it may be that you're getting the error because Access brings the entire table into memory, changes the field format, and writes it all back out.

Try instead taking it in steps:

1. Remove (but note) all the relationships to the table
2. Rename the table to (say) tablename_Old
3. Copy and paste the table, DESIGN VIEW ONLY, to a new table with the original name
4. Change the fields as needed
5. Run an Append query to migrate the data from _old into the new table
6. Reestablish relationships
7. Check everything, if it's ok delete the _old table

Answer 2

Thanks, John

Isn't there a way to change the memory allotted to this operation? Having to manually update fields every time this problem arises doesn't seem sustainable in the long run

Answer 3

You're right, it is structure only.

The text vs. number problem would arise any way you do it - changing the field type will cause an error if the existing data is not numeric. I'd suggest first running a cleanup query, e.g.

SELECT * FROM yourtable WHERE [fieldname] IS NOT NULL IsNumeric([fieldname]) = False;

Manually correct any nonnumeric values (or use update queries if there are a lot and there's some pattern you can use), and then use

Val([fieldname])

as a calculated field in your append query to convert the text string to a number.

Answer 4

also, the field in the original table is a text field...doesn't this conflict with the append query since the field in the new table is not text but numeric?

Answer 5

Not sure what you mean with DESIGN VIEW ONLY. Do you mean pasting the table as STRUCTURE ONLY?