MS Access cant change the data type ; there is not enough disk space or memory.

[barret bondon]

Error message:
" MS Access cant change the data type ; there is not enough disk space or
memory."
(but of course there is)

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0
Set up to 50000 Does nothing. (this from the MS help webpage for this
issue)

Copied all tables into new MDB; no difference.
Copied data into new table structure that uses much fewer total characters;
does nothing.
Max 80 field, one memo, 90000 records.
Removed recently added fields, does nothing.
I can delete fields but cant change field length.
This is happening both on 2007 and on 2003; 2003 file type.

 

[John W. Vinson]

Error message:
" MS Access cant change the data type ; there is not enough disk space or
memory."
(but of course there is)

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0
Set up to 50000 Does nothing. (this from the MS help webpage for this
issue)

Copied all tables into new MDB; no difference.
Copied data into new table structure that uses much fewer total characters;
does nothing.
Max 80 field, one memo, 90000 records.
Removed recently added fields, does nothing.
I can delete fields but cant change field length.
This is happening both on 2007 and on 2003; 2003 file type.

I've seen this error before. The reason is that if you change a datatype in a
table, Access must keep two copies (old and new) of the entire table in memory
at the same time.

The solution is to create a new, empty table with the new datatypes (copy and
paste the existing table, design view only, and change the datatypes), and
then run an Append query to migrate the data. When you verify that the data is
all there and correct, delete the old table; rename the new one; reestablish
any relationships, and compact the database.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[barret bondon]

John:
At this point I'm only trying to change field length.
I understand your method, and just used it, and will continue to need be ,
but got very worried as it suggested data corruption ( the fear of that is
keeping me up late; this is a large client and a well used system).
RAM spikes almost not at all in my tests, and I've been doing this since
Access 95 and never saw this ( and it does seem ratehr odd) but if it
doesn't suggest corruption to you I wount worry.
Thanks for the help

 

[barret bondon]

This MAY have solved it:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0
set to 50000 from default of 9500 and a
( I should have known; it's MS not linux)
Reboot

 

[David-W-Fenton]

This MAY have solved it:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Jet 4.0
set to 50000 from default of 9500 and a
( I should have known; it's MS not linux)
Reboot

You mean the MaxLocksPerFile setting?

I don't know why you feel the need to take a jab at Microsoft for
this. It's a setting that works just fine for 99% of operations. It
was chosen to maximize performance. Sometimes you have to do
something that requires more headroom, and you can change it.

BTW, you don't have to do it in the registry -- you can change it on
the fly for the DBEngine object in Access for a particular
operation, if you're doing it in code.

That said, I have to change it five or six times a year. When done,
I set it back to the default, since I figure MS has a lot better
ideas about what the defaults on its products should be than I do.