changing data type: Insufficient memory

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gillfish - 21 Jan 2005 15:21 GMT

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?

Signature

Reply to this Message

Sprinks - 21 Jan 2005 15:43 GMT

Hi, Gillfish.

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

Sprinks

> 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?

Reply to this Message

Douglas J. Steele - 21 Jan 2005 15:48 GMT

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?

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

>I have a large table (32 fields, 500 000 records). I am trying to
>"clean-up"
[quoted text clipped - 3 lines]
> 27GB free, and compacted the database first, but still cannot get it to
> change the data type. Any ideas?

Reply to this Message

Gillfish - 21 Jan 2005 17:41 GMT

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)

> 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
[quoted text clipped - 11 lines]
> > 27GB free, and compacted the database first, but still cannot get it to
> > change the data type. Any ideas?

Reply to this Message

Douglas J. Steele - 21 Jan 2005 18:41 GMT

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.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Thanks, that did work. I'll have to do that for a few more fields now, but
> the path is now clear!
[quoted text clipped - 21 lines]
>> > 27GB free, and compacted the database first, but still cannot get it to
>> > change the data type. Any ideas?

Reply to this Message

John Vinson - 21 Jan 2005 18:18 GMT

>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]

Reply to this Message