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

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.

--
HTH,
George


"Pat" <(E-Mail Removed)> wrote in message
news:AC0F1000-6D8D-4F22-931C-(E-Mail Removed)...
> 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
>
>

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.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"Pat" wrote:

> 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
>
>

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

Pat

"Jerry Whittle" wrote:

> 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.
> --
> Jerry Whittle, Microsoft Access MVP
> Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.
>
>
> "Pat" wrote:
>
> > 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
> >
> >

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

"George Nicholson" wrote:

> 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.
>
> --
> HTH,
> George
>
>
> "Pat" <(E-Mail Removed)> wrote in message
> news:AC0F1000-6D8D-4F22-931C-(E-Mail Removed)...
> > 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
> >
> >
>
>
>

On Tue, 6 Nov 2007 07:42:02 -0800, Pat <(E-Mail Removed)> wrote:

>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]

John, thanks for the advice.

Pat

"John W. Vinson" wrote:

> On Tue, 6 Nov 2007 07:42:02 -0800, Pat <(E-Mail Removed)> wrote:
>
> >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]
>