Out of memory or disk space error when trying to change data type

[Puzzled Paul]

I am trying to change the data type of one of the fields in my database from
'Number' to 'Text', access thinks for a while, seems to get to 50% judging by
the progress bar and then pops up the msg that it cannot continue as there is
either insufficient memory of disk space.

The database has 2.8m records, with 7 fields per row. Around 225mb once
compacted on disk.
I have around 81GB of free disk space on my main drive, and 3GB of total
ram. RunningVista and Acces 2007. Virtual memory is set to be managed
automatically. I've tried closing most other programs (other than a browser)
but it doesn't help. In any case, I don't believe that this operation can
really take THAT much memory that I should have to close everything to change
the datatype.

I'm about ready to stab Access in the throat with a butter knife (if I can
locate said throat).

 

[Maurice]

Paul,

In such cases I try the following trick see if that can help you.

Create a new field in the table and set it's datatype to number.

Now create a query and update the newly created field with the textfield you
want to change. Run the query. When done succesfull remove the textfield from
the table and you are set.

NOTE: Be sure to test this on a backup copy of the database first !!

hth

 

[John W. Vinson]

On Thu, 16 Apr 2009 09:32:01 -0700, Puzzled Paul <Puzzled

I am trying to change the data type of one of the fields in my database from
'Number' to 'Text', access thinks for a while, seems to get to 50% judging by
the progress bar and then pops up the msg that it cannot continue as there is
either insufficient memory of disk space.

The database has 2.8m records, with 7 fields per row. Around 225mb once
compacted on disk.
I have around 81GB of free disk space on my main drive, and 3GB of total
ram. RunningVista and Acces 2007. Virtual memory is set to be managed
automatically. I've tried closing most other programs (other than a browser)
but it doesn't help. In any case, I don't believe that this operation can
really take THAT much memory that I should have to close everything to change
the datatype.

I'm about ready to stab Access in the throat with a butter knife (if I can
locate said throat).

The problem is that this operation requires that the entire table be loaded
into memory... twice, the old version and the new one. That's a lot of memory,
especially with the program loaded too.

It's much simpler to do this in a different manner. Create a new, empty table
by copying and pasting your table, choosing the "design only" option. Change
the datatype in this table (it's empty, you'll have no memory problems). Then
run an Append query to migrate the data - this works record by record, not
using the entire table, again no issues.

When you verify that the new table is ok, drop all relationships to it, rename
or delete it, and rename the new table to the old name; reestablish
relationships and you should be good to go.