If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
changing data type: Insufficient memory
I have a large table (32 fields, 500 000 records). I am trying to "clean-up"
the data and want to change one field from text to number. I keep getting an "insufficient memory or disk space" error. I have a 40GB hard drive with 27GB free, and compacted the database first, but still cannot get it to change the data type. Any ideas? -- G. |
#2
|
|||
|
|||
Hi, Gillfish.
Have you tried doing it in pieces, based on a range of Val([YourTextField])? Sprinks "Gillfish" wrote: I have a large table (32 fields, 500 000 records). I am trying to "clean-up" the data and want to change one field from text to number. I keep getting an "insufficient memory or disk space" error. I have a 40GB hard drive with 27GB free, and compacted the database first, but still cannot get it to change the data type. Any ideas? -- G. |
#3
|
|||
|
|||
How much space you have isn't necessarily the issue. How big is the MDB
file? It cannot be larger than 2 GB (1 GB if you're using Access 97 or earlier). Can you create a new database with the table in the correct format, link to your existing database and run an INSERT INTO query to get the data into the new table? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Gillfish" wrote in message ... I have a large table (32 fields, 500 000 records). I am trying to "clean-up" the data and want to change one field from text to number. I keep getting an "insufficient memory or disk space" error. I have a 40GB hard drive with 27GB free, and compacted the database first, but still cannot get it to change the data type. Any ideas? -- G. |
#4
|
|||
|
|||
Thanks, that did work. I'll have to do that for a few more fields now, but
the path is now clear! I'm curious...why should the mdb file not be larger than 2GB? (this one was only 258mb) "Douglas J. Steele" wrote: How much space you have isn't necessarily the issue. How big is the MDB file? It cannot be larger than 2 GB (1 GB if you're using Access 97 or earlier). Can you create a new database with the table in the correct format, link to your existing database and run an INSERT INTO query to get the data into the new table? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Gillfish" wrote in message ... I have a large table (32 fields, 500 000 records). I am trying to "clean-up" the data and want to change one field from text to number. I keep getting an "insufficient memory or disk space" error. I have a 40GB hard drive with 27GB free, and compacted the database first, but still cannot get it to change the data type. Any ideas? -- G. |
#5
|
|||
|
|||
On Fri, 21 Jan 2005 07:21:02 -0800, "Gillfish"
wrote: I have a large table (32 fields, 500 000 records). I am trying to "clean-up" the data and want to change one field from text to number. I keep getting an "insufficient memory or disk space" error. I have a 40GB hard drive with 27GB free, and compacted the database first, but still cannot get it to change the data type. Any ideas? The problem is that if you try to change the datatype "in place" in the table, Access must a) copy the entire table into memory, b) create a new field in the table, c) write the entire table back, d) delete the old field. It's very demanding! I'd suggest creating a new empty table with the desired field type, and then run an Append query to populate it. This makes far less demand on memory since it's converting one record at a time rather than the entire table at once. John W. Vinson[MVP] |
#6
|
|||
|
|||
2 GB is the absolute largest that an MDB can be. As John explained in his
response to you, Access actually makes a new copy of your entire table (not just the new column, I believe), so it's going to significantly increase the size of the database. I'm surprised, though, that 258 MB was too big. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Gillfish" wrote in message ... Thanks, that did work. I'll have to do that for a few more fields now, but the path is now clear! I'm curious...why should the mdb file not be larger than 2GB? (this one was only 258mb) "Douglas J. Steele" wrote: How much space you have isn't necessarily the issue. How big is the MDB file? It cannot be larger than 2 GB (1 GB if you're using Access 97 or earlier). Can you create a new database with the table in the correct format, link to your existing database and run an INSERT INTO query to get the data into the new table? -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) "Gillfish" wrote in message ... I have a large table (32 fields, 500 000 records). I am trying to "clean-up" the data and want to change one field from text to number. I keep getting an "insufficient memory or disk space" error. I have a 40GB hard drive with 27GB free, and compacted the database first, but still cannot get it to change the data type. Any ideas? -- G. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Rapid input Via datasheet | RudyR_Seattle | General Discussion | 4 | January 31st, 2005 01:33 AM |
changing proportion of chart and data table | Dawn Parks | Charts and Charting | 3 | January 6th, 2005 01:18 AM |
Prevent excel from changing numeric data ranges into dates? | Tay F | General Discussion | 1 | November 6th, 2004 03:20 AM |
VBA Code problem error 9 | Speedy | General Discussion | 19 | October 15th, 2004 09:05 PM |
Importing Excel spreadsheet into Access - data type problem | Andrew Good | General Discussion | 2 | August 27th, 2004 09:39 AM |