changing data type: Insufficient memory

[Gillfish]

I have a large table (32 fields, 500 000 records). I am trying to "clean-up"
the data and want to change one field from text to number. I keep getting an
"insufficient memory or disk space" error. I have a 40GB hard drive with
27GB free, and compacted the database first, but still cannot get it to
change the data type. Any ideas?

 

[Sprinks]

Hi, Gillfish.

Have you tried doing it in pieces, based on a range of Val([YourTextField])?

Sprinks

 

[Douglas J. Steele]

How much space you have isn't necessarily the issue. How big is the MDB
file? It cannot be larger than 2 GB (1 GB if you're using Access 97 or
earlier).

Can you create a new database with the table in the correct format, link to
your existing database and run an INSERT INTO query to get the data into the
new table?

 

[Gillfish]

Thanks, that did work. I'll have to do that for a few more fields now, but
the path is now clear!
I'm curious...why should the mdb file not be larger than 2GB? (this one was
only 258mb)

 

[John Vinson]

I have a large table (32 fields, 500 000 records). I am trying to "clean-up"
the data and want to change one field from text to number. I keep getting an
"insufficient memory or disk space" error. I have a 40GB hard drive with
27GB free, and compacted the database first, but still cannot get it to
change the data type. Any ideas?

The problem is that if you try to change the datatype "in place" in
the table, Access must a) copy the entire table into memory, b) create
a new field in the table, c) write the entire table back, d) delete
the old field. It's very demanding!

I'd suggest creating a new empty table with the desired field type,
and then run an Append query to populate it. This makes far less
demand on memory since it's converting one record at a time rather
than the entire table at once.

John W. Vinson[MVP]

 

[Douglas J. Steele]

2 GB is the absolute largest that an MDB can be. As John explained in his
response to you, Access actually makes a new copy of your entire table (not
just the new column, I believe), so it's going to significantly increase the
size of the database.

I'm surprised, though, that 258 MB was too big.