Changing Data Type... Memory Issues

[Cosmicnoodle]

I am trying to change a text field (that has been used to store Dates) into a date/time field so that I can sort it correctly
However, when I try and change the data type, It gets to about 90% on the progress bar then tells me that my system does not have enough memory available, and that an error has occured, data type not changed etc.

My system is fairly powerful, and I don't think that it is strictly the problem.


Any ideas??

 

[Smart]

RE :Changing Data Type... Memory Issues

Not sure if this will work (haven't had time to test it)
Try creating another table (copy existing table and paste structure only)
change the text field to date format.
append from existing table to new table (if it works) delete records from existing table then append from new table to existing table

NB.

Back up your database before your start

 

[The_Doc_Man]

Using Start >> Settings >> Control Panel >> System >> Performance

Look at virtual memory tab, see how much you have allocated (or whether allocation is set for "Auto").

If your setting is too low, you could run out of virtual memory pretty quickly.

I suspect part of the problem is that Access is trying to make a copy of your data in case you want to "roll back" the operation. It is copying the entire table to do that. But to make that copy, it needs to expand its own virtual memory to a size large enough to hold BOTH COPIES AT ONCE. If your table is big enough, that can be a big problem. Rule of thumb: When you want to do something like this, you need twice the size of the table being modified + perhaps about 3-5%. (The extra is overhead for memory pointers and headers and stuff like that.) If this number is bigger than your physical memory, it will spill into your virtual memory. I.e. your system will start paging. And no, not beeping your pager. I'm talking major disk activity. If you then run out of page-file & swap-file space, you are screwed.

 

[Pat Hartman]

And yet another approach is to add a new date column to the table. Run an update query to copy the data from the text column to the date column. When everything is done, delete the old column. Both this and the Append query already recommended will get past the memory problem.