Text format change failure

[siricli]

When I tried to change the text format of a table with
more than 10M records, a message of "insufficient memory"
was poped up. The action was to change the length of one
the fields from 7 to 10 characters. The action was
successful if the table was alone without other tables
and queries. I just wonder if any limitations for the
tables of Access and the reason behind.

Could anyboby help ?

 

[Ken Snell [MVP]]

When you ask ACCESS to modify the properties of a field, what ACCESS
actually does is create a new field with the "new" properties, copy the data
from the old field to the new field, and then delete the old field. The new
field then is renamed to the old field's name and relationships, etc. are
reestablished. If you have a lot of data or the table already has a lot of
fields, you can get this memory problem.

When I get this problem, I use one of these methods:

(1) Create a temporary table that is a copy of the original table's
structure. Use an append query to copy the data into the temporary table.
Use a delete query to delete the data from the original table (watch out --
if you have referential integrity set with cascade deletes, this will not
work). Modify the field in the original table. Use append query to copy
original data back into the original table. Delete the temporary table.

(2) Create a new table with the desired fields that I want, and then use an
append query to copy the data into the new table. Use the new table in place
of the old table.