"Can't change data type" error - Access 2003

[Pat]

I'm trying to reduce the field size of text fields in my database. They
defaulted to 255 characters when I created my database but in many cases I
only need a size of around 20. When I enter the reduced size for one field
in the table design view and try to save the change to that field I get the
following errors:

"Microsoft Access can't change the data type. There isn't enough diskspace
or memory."

When I click OK to the above I then get the following message:

"Errors were encountered durnig the save operation. Data types were not
changed. Properties were not updated."

I'm not actually changing the data type, just the field size. Some
background:
- about 1 million records in the database
- database size is 0.73 GB
- my PC has 2 GB of RAM and Pentium duo core processor
- my database is on my hard drive while I work on it (I back it up on our
system server) and the hard drive has 62 GB free out of 75 GB total.

Is my database simply too big to do this kind of operation? I was looking
at some old posts on this topic and I think one possible solution is to
create a new field of the size I want, then use an update query to copy the
data over, then delete the old field, then rename the new field to the same
name as the old one. But this is relatively time-consuming compared to just
changing a field size in design view.

Any trouble-shooting tips or alternative work-around suggestion would be
appreciated.

Thanks,
Pat

 

[George Nicholson]

Thing is that "simply changing a field size in Design view" is really doing
those manual steps behind the scenes. Well, no, I guess its doing more than
that and it's the "more" that's causing the "can't do that" message.

When you change the datatype OR fieldsize of a field, Access creates
newfield of the specified type/size, copies existing data to it, deletes
oldfield and then renames newfield to oldfield. Space occupied by oldfield
is not recovered until you do a Compact & Repair.

So, Compact your mdb, then try to change field size in design view again. I
would suggest just doing one field at a time, compacting after each.

If it still won't let you downsize a field then, as you say, you can do it
manually. I have to do the same thing occasionally: create newfield(s),
update newfield(s), delete oldfield(s). For some reason I have no trouble
doing this manually but Access seems to require a lot more overhead and
can't manage it when I "just" change field size in Design view. Fortunately,
its not something you should find yourself doing often enough for it to be
that much of an issue

Just remember to Compact along the way.

 

[Jerry Whittle]

You may be running into a limit that I've never heard of. You could create a
new table with the same structure except the new field sizes. Then do an
append query to pour the records from the other table. Delete the original
table and rename the new one. However this could be a problem if you have
relationships built between the tables expecially with referiential integrity
enables.

Therefore I have a better suggestion: Don't Worry. Be Happy! Access text
fields do not waste space. If a field in a record only has 25 characters in
it, Access only stores 25 characters and not the entire 255. Setting a field
size is really only good for stopping gross errors such as someone typing in
a 20 character ZipCode. The field size is also used by the wizards to attempt
to size text boxes correctly in forms and reports.

 

[Pat]

Thanks Jerry, yes my concern was the space I thought was being taken up by
the text fields. Thanks for clearing that up.

Pat

 

[Pat]

Thanks George, good to get someone else's experience with the same problem.
Tried compacting but then got the same error messages trying to change the
size of a field.

Pat

 

[John W. Vinson]

I'm trying to reduce the field size of text fields in my database. They
defaulted to 255 characters when I created my database but in many cases I
only need a size of around 20.

It's not strictly necessary to change the size of the field. Access doesn't
store trailing blanks, so the amount of disk space used will not change if you
reduce the size of these fields; if the field contains 11 bytes now, it will
still take up 11 bytes on disk, whether the field size is 20 or 255.

When I enter the reduced size for one field
in the table design view and try to save the change to that field I get the
following errors:

"Microsoft Access can't change the data type. There isn't enough diskspace
or memory."

Any trouble-shooting tips or alternative work-around suggestion would be
appreciated.

My best suggestion would be to *MAKE A BACKUP*; compact your database (to be
sure there's enough space for the old and new tables); create a new, empty
table with the desired field sizes; run an Append query to migrate the data
from the current table into the new one; CHECK EVERYTHING; delete the old
table and compact again.

It's a fair bit of work, but it will avoid the problem you're having (or that
adding the new field will also have) - Access must keep a copy of the entire
old and new table in memory to make the change.

John W. Vinson [MVP]

 

[Pat]

John, thanks for the advice.

Pat

It's not strictly necessary to change the size of the field. Access doesn't
store trailing blanks, so the amount of disk space used will not change if you
reduce the size of these fields; if the field contains 11 bytes now, it will
still take up 11 bytes on disk, whether the field size is 20 or 255.


My best suggestion would be to *MAKE A BACKUP*; compact your database (to be
sure there's enough space for the old and new tables); create a new, empty
table with the desired field sizes; run an Append query to migrate the data
from the current table into the new one; CHECK EVERYTHING; delete the old
table and compact again.

It's a fair bit of work, but it will avoid the problem you're having (or that
adding the new field will also have) - Access must keep a copy of the entire
old and new table in memory to make the change.

John W. Vinson [MVP]