Solved

Microsoft can't change the data type.      There isn't enough disk space or memory.

Posted on 2006-11-10
11
2,775 Views
Last Modified: 2010-05-18
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.
0
Comment
Question by:Softtech
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 6

Assisted Solution

by:mwharff
mwharff earned 20 total points
ID: 17916797
Can you make a copy of the table structure and change the record length in the new table and import the data into the new table?

Hoping to help

Mike
0
 
LVL 26

Accepted Solution

by:
jerryb30 earned 20 total points
ID: 17916805
The cryptic message is MS's way to say:  This is too hard.
I am not sure of the upper limit on changing data type/property.
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.
Or make copy copy of your current table, adjust field properties, and append data to that table from original table.
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 17916814
Can't check this out, but can your try
Alter table yourtable alter column yourcolumn text(5)
as SQL
0
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

Author Comment

by:Softtech
ID: 17916831
>> 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.

0
 
LVL 39

Assisted Solution

by:stevbe
stevbe earned 20 total points
ID: 17916843
did you repair/compact first?

What is the byte size of your fiel ... Access can only handle 2 gig including interanl structures which I think it is trying to duplicate asnd then swap out ... it is not actually changing the existing field, it is adding a new one, updating the data and then would drop the old one but it can never finish because it cfrosses it's internal threshold before it adds all the *new* data in.
0
 

Author Comment

by:Softtech
ID: 17916850
>>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?
0
 

Author Comment

by:Softtech
ID: 17916858
>> did you repair/compact first?

Yes.

>> What is the byte size of your fiel

The MDB is under 150mb.

0
 
LVL 7

Assisted Solution

by:rvidal393
rvidal393 earned 20 total points
ID: 17916865
This article explains your situation and has a couple of ways to fix it.

http://support.microsoft.com/kb/209940/EN-US/
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 17916910
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.....
0
 

Author Comment

by:Softtech
ID: 17916933
>> 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?

0
 
LVL 26

Expert Comment

by:jerryb30
ID: 17916997
Then you wouldn't need all of the Access experts with Genius certs :>)
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question