"can't change the data type" error while extending text field value in Access 2010

Question

I have an access database with 900K records in it. I realized that there is a text field that is not long enough, and I decided to change the length to 255 from 50 characters in the design view.

WHen i saved the table, it prompt me with an error "X database can't change the data type. There isn't enough disk space or memory".

I have a 4GB of ram in my win7-64 bit workstation, and only about 40 % are used.

I googled it, and there is a microsoft article that says it's because of the****MaxLocksPerFile that by default is set to 9500 locks. The article suggested a change in registry value. However, i can NOT find any such registry entry. The article is intended for MDB access type. Access 2010 has an accdb type.

Does anyone know how to fix this?

Answer 1

Does it work if you compact the database before trying to change the field type?

If not, try the following:

Copy and paste the table with the structure only option.

Modify the field size in the copy - since it has no records, that shouldn't be a problem.

Use an append query to append all records from the original table to the copy.

Answer 2

You may find it easier to add a new field to the table, run an Update query to populate the new field with the existing value, then delete the old field.

---

Doug Steele, Microsoft Access MVP

http://www.AccessMVP.com/djsteele (no e-mails, please!)

Answer 3

I have had the same problem for well over a year. Why is it that Microsoft engineers can not fix this major, broken code, issue. It is very true I have lost confidence in the integrity of Access. Can you blame me!

Bob

Answer 4

I also am experiencing a similar problem.

I too am running Office 2010 x64 w/ Access on Win 7 x64 on about 600k-row table, 

I already modified these registry entries:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\ACE\MaxLocksPerFile

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Office\14.0\Access Connectivity Engine\Engines\Jet 3.x\MaxLocksPerFile

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Jet 3.x\MaxLocksPerFile

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Jet 4.0\MaxLocksPerFile

I set them all to 1000000000. The change-data-type seemed to work longer, almost got done, but returned the same "error."

I'm wondering whether increasing the LockDelay key or LockRetry key would do the trick, but I'm afraid to mess with it too much.

The idea that my system might be low on resources is... Just plain wrong, I'm not even using a third of my available RAM, a quad-core 9550, and I have a lot of space left on my 1TB hard drive (I'm not trying to show-off here, just saying that my system is half-decent, so it is able to handle whatever "lock" number you throw at it.)

The whole notion that if we have a big database, we should somehow circumvent the features that Access provides is practical - but not for Microsoft. Why not just use some MySQL no-interface query console if we have to run append queries in order to tackle large databases? What's the whole point in this nice, user-friendly interface if we can't use it?

I guess I'll go back and to this via query, but this should be a big hint-hint for Microsoft what to patch up in their Office 2010.

To jesbuddy07: you can actually just make a separate column in your table, name it something like <short whateverhaveyou>, set the text length to what you need (make sure it's enough to get all of the longer text entries in) and run the following simple UPDATE query:

UPDATE [put Table Name Here] SET [short whateverhaveyou] = [whateverhaveyou];

In order to do more complex type conversions (i.e. you have a Y/N column with text entries and you want to convert it to Yes/No format), you can use something like

UPDATE [Some Table] SET [bindata whateverhaveyou] = 1 WHERE [textdata whateverhaveyou] = 'Y';

,and the like. You can probably easily extrapolate on how to adapt this to multiple conversion types.

Answer 5

This is a great work-around.  If you copy the table to "Copy of..." and then rename the original to "Copy2" or something, then you can rename the "Copy of..." to the original table name, and all the queries will still be linked to it. 

After changing the field types, you can append the data in the new table by doing a copy / paste of the "Copy2" table and selecting append.  Very nice!

--Alex