Cant change the data type (1 Viewer)

[Gkirkup]

I have a table with about 300,000 records. About ten fairly small fields per record.
I am trying to change the length of a text field from 25 to 40 characters, and I get the error message, 'MS Access can't change the data type. There isn't enough disk space or memory'.
I have never seen this message before. I have about 64 Gig of free disk space. What can I do?

Robert

 

[plog]

This has to do with RAM, not your hard drive. Your table is just so huge it can't make the changes because of the processing required on all those records. So what you need to do is perform those changes on a smaller table. Here's how:

Step 1 when editing your data--make a back up. After that, create a new blank database and also open your existing database. In the existing one copy your table, then paste just the structure of it in the new database. Make the necessary changes in the blank table. In the existing database make an Append query and append the data from the existing database to the new table in the new database.

Verify the new table has all the data and is good. Then paste the table in the existing database replacing the old one.. Again, remember to do Step 1.

 

[Gkirkup]

Thank you. I will give that a try.

Robert

 

[gemma-the-husky]

you can probably equally do this by

1. create another field in the table with a larger field size
2. copy the original field into the new one
3. delete the original field
4. rename the new field

the trouble with this, and possibly with plog's idea as well is
a) name autocorrect may mess up your queries AND/OR
b) existing relational integrity settings may prevent you deleting / renaming tables without a bit of work.

 

[The_Doc_Man]

The other possibility isn't RAM, but virtual RAM. Check the size of your virtual memory from the System Properties (via Control Panel). I can't be more specific than that because Windows often hides that from you and also depending which version of Windows you are running, that setting might be hard to find.

If you have your page/swap file squeezed down too tight, you run out of VIRTUAL space because you can't allocate enough space in the page/swap file. If that happens, it looks like you ran out of physical memory, but you really didn't. Extending the size of the page/swap file might help.

It MIGHT be necessary (depending on your version of Windows) to do a defrag first before you try to extend the size of your page/swap file.