Changing field length in large database.

[samcoinc]

I am trying to change a field lenght of a large database (access 97) It is at 50 right now and want to change it to 25. When I try to change it and save the table I get a error when the bar is about 3/4 the way accross the bottom.

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

then

not enough space on temporary disk

This is error 3183. In the help it says that the TEMP DOS enviroment variable location doesn't have enough space. (summerising). Now I have a 100+gb drive that isn't close to being full. I also changed the MaxLocksPerFile registry dword to 8,000,000 as my table has around over 3 million records. I have tried this on a xp and 2000 machine same results. Next I am going to try it on a 98 machine. I could do it by breaking up the table into 2 differnt ones and do each one individually but there is an autonumber field used as a reference number (I know I know). So that would screw up the numbering scheem (Yes I know there are ways arund that also). this database is about 400MB

Couple of questions
1. Is there a way around this? I have not found a solution on line yet.
2. Will lowering the field length make the database smaller (the data that is in there now is less than 25 charecters and the field is set to 50.) I have gotten mixed info on this. there are a few fields I would like to reduce.

Thanks
sam

 

[neileg]

You'll get a negligible reduction in file size if you simply eliminate empty space. Have you done a Reapir & Compact on the file?

I don't understand why you are getting this message, but here's a workaround.

Create a new field in your table with the right 25 character length. Create an update query that uses Left() to take the first 25 characters from your old field and populates the new field with this data. Delete the old field and rename the new field to the same name as the old field.

 

[samcoinc]

Thank you - That worked. I though had to run a select query before the update to only give me top 1,000,000 records at a time that where not updated - so the update query had to run 4 times (3.6 million records). I could not update them all at once. - same error

That had never crossed my mind. (I have done similar stuff though). My Employee calls me the phyco-query master

sam