Softtech
asked on
Microsoft can't change the data type. There isn't enough disk space or memory.
I have an MS Access 2000 database/MDB that contains a table containing 770,000 records in it. It consists of approx. 10 records, all text, none greater than 64 characters in width. I wanted to change the record length of one of the fields from 7 characters to 5. I used the DESIGN TABLE tool and changed this record's length value to 5 (from 7) and closed the window. After confirming that I was aware that I might lose some data due to the truncation, the progress bar began progressing and got to 75% when the following window popped up, stopping the conversion:
Microsoft can't change the data type. There isn't enough disk space or memory.
And yet I have gigs of free disk space, and 512mb of RAM.
I don't buy Microsoft's message that there isn't enough disk space or memory. Nothing I can see using ALT+CTL+DEL and checking the Memory Usage comes close to confirming that I am running out of memory during this procedure.
Microsoft can't change the data type. There isn't enough disk space or memory.
And yet I have gigs of free disk space, and 512mb of RAM.
I don't buy Microsoft's message that there isn't enough disk space or memory. Nothing I can see using ALT+CTL+DEL and checking the Memory Usage comes close to confirming that I am running out of memory during this procedure.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>> One method you could try is to add another field, with desired properties, and
>> update that field with 7 character field. The truncating will occur naturally.
I've tried that. Still same error.
>> update that field with 7 character field. The truncating will occur naturally.
I've tried that. Still same error.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>>Can't check this out, but can your try
>>Alter table yourtable alter column yourcolumn text(5)
You can't create a new table using SQL in Access 2000's TABLE > NEW window.
Where am I supposed to type a SQL command when designing the table?
>>Alter table yourtable alter column yourcolumn text(5)
You can't create a new table using SQL in Access 2000's TABLE > NEW window.
Where am I supposed to type a SQL command when designing the table?
ASKER
>> did you repair/compact first?
Yes.
>> What is the byte size of your fiel
The MDB is under 150mb.
Yes.
>> What is the byte size of your fiel
The MDB is under 150mb.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The alter table staement is sql actually ddl).
Query, New, Design View, close on list of tables
Select View in Toolbar, SQL view, and paste the statement (edited to fit your table and field names) and run query.
I am doing this without having Access on my PC here, so I can't confirm this method.
steve might be on the right track.....
Query, New, Design View, close on list of tables
Select View in Toolbar, SQL view, and paste the statement (edited to fit your table and field names) and run query.
I am doing this without having Access on my PC here, so I can't confirm this method.
steve might be on the right track.....
ASKER
>> Or make copy copy of your current table, adjust field properties, and append data to that table from original table.
I decided to
1) make a copy of the table, duplicating only the structure (leaving out the data)
2) change the new empty table so that the record lengths are where I want them
3) export the data from the old table to a TXT file
4) import the TXT data into the new table
This worked.
But hey, if I can do this then why in the @#$@#$ can't Microsoft do the same trick internally?
I decided to
1) make a copy of the table, duplicating only the structure (leaving out the data)
2) change the new empty table so that the record lengths are where I want them
3) export the data from the old table to a TXT file
4) import the TXT data into the new table
This worked.
But hey, if I can do this then why in the @#$@#$ can't Microsoft do the same trick internally?
Then you wouldn't need all of the Access experts with Genius certs :>)
Alter table yourtable alter column yourcolumn text(5)
as SQL