Change Data Type

[romar]

I'm using Access 2002 and need to increase some field sizes, some by increasing the maximum number of characters and others by changing from Text to Memo. When I try to do this I get the message "Microsoft Access cannot change the data type. There isn't enough disk space or memory"

I have two 320GB hard drives and 4GB of RAM and the Access file is a mere 280MB. Can anyone please explain the problem and tell me how to achieve my objective?

Roger

 

[burrcm]

IT may be a maximum lock limitation issue. By default Jet4 (and earlier) are set to 9500 max locks per file (to save allocating too much memory, but you have plenty, so it is not a problem). This causes issues when trying to update large files/tables. I generally run 100,000. To adjust the setting in registry, open regedit and navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0 and double click MaxLocksPerFile. Click decimal. You will see the value is 9500. Change this to 100000 and OK out.

Chris B

 

[romar]

Chris B,

You absolute star - that worked a treat. Many thanks for such a prompt and helpful reply. If you ever come to the UK I'll buy you a beer.

Roger

 

[burrcm]

Good result. I agree that unnecessary messing in registry is ill advised, however this particular restriction is an old setting which has not kept pace with available memory and regularly causes issues which need not occur. I always make this change.

Chris B

 

[romar]

Thanks, Pat. I've already successfully done it Chris B's way which seems easier than yours, especially as I don't know what an append query or RI are!

Roger

 

[gemma-the-husky]

thats so useful - ive had the same issue - now i know how to fix it