Can't change the data type

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Trying to increase the length of a field from 3 to 25. Try to save table in
design view and get the message that "can't change the data type. Not enough
disk space or memory."
I really have plenty of memory - 36g. What can I do? Thanks.
 
sdhi said:
Trying to increase the length of a field from 3 to 25. Try to save table in
design view and get the message that "can't change the data type. Not enough
disk space or memory."
I really have plenty of memory - 36g. What can I do? Thanks.
Click to expand...

How large is the table and file? When you change a data type via the GUI what
Access actually does in the background is...

Creates new field with a temp name in the new type and size.

Copies all data from old column to new one

Deletes original column

Renames new column to old column name

If your file is near the maximum allowable size or if your disk is nearly full
Access might run out of room when attempting the above process.
 
The table has 90,000 records with 89 fields in each record. How do I set the
space available for Access to use, since I have a large amount of free space
available? Thanks.
 
The file I imported had 80m the database itself shows 175 m under properties.
It doesn't appear to be approaching 2 gig.
I just set up this computer a week ago. Seems like the first time I started
access there was a place to set workspace(?). I remember the defaults were
both "2". Can't remember where to find those settings. Or is there some other
way to increase the field size?
 
The file size limits are predefined and can't be changed. I believe
the max size for Access XP is 2 GB. I think Access 97 was 1 GB. You
could try compacting your current database to free up some space. If
possible, delete any unneeded tables before compacting.. another option
would be to export that table (structure only not data) into a new
empty database. Then make design modifications. Link to original
table, and append data into new table. If it works, then you could
delete original table, import new table into old database. Make sure
the table name stay the same.... ofcourse, any route you go, your
first step will be to make a couple of backups in case anything goes
wrong.


Mark
(e-mail address removed)(no dashes)
http://access-pro.tripod.com
 
The table has 90,000 records with 89 fields in each record. How do I set the
space available for Access to use, since I have a large amount of free space
available? Thanks.
Click to expand...

I'd suggest copying the table (in the tables database window) to a new
table, *design only* - i.e. don't copy the data, yet. Change the field
size in design view of the new, empty table. Then run an Append query
to migrate the data. Check the result; delete the old table; and use
Tools... Database Utilities... Compact and Repair to recover the lost
space.

Note that 89 fields is an ENORMOUSLY wide table. I know of very few
entities with 89 non-repeating independent attributes; I suspect that
you have one or more one-to-many relationships embedded in each
record. You may want to consider restructuring the table.

John W. Vinson[MVP]
 
Good ideas! Thanks. The reason for 89 fields is I have to export the whole
thing as one .csv file. I discovered this problem toward the end of the
process.
 
Good ideas! Thanks. The reason for 89 fields is I have to export the whole
thing as one .csv file. I discovered this problem toward the end of the
process.
Click to expand...

It is *NOT* necessary to have all the data in one table to export data
to a .csv file. It's perfectly possible (and routine) to export from a
Query joining multiple tables.

John W. Vinson[MVP]
 
Back
Top