Data Type Conversion

[Guest]

Quick question....are there data type conversion limits?...meaning: I am
trying to convert a single column in a table from 'text' to a 'number' and it
keeps telling me that I "don't have enough disk space or memory" I have a
250 GB hard drive that is almost completely empty. The table is about
250,000 rows. So what are the limitations of Access or what am I doing
wrong? Thank you for your help

 

[Ken Snell [MVP]]

When you change the data type of a field in a table (in design view), what
ACCESS actually does is create a new field with the desired data type, copy
the data from the original field to the new field, delete the original
field, and then put the new field into the "slot" that the old field had (in
the Fields collection for the table).

So, you're actually asking ACCESS to copy a lot of data, which can cause
memory problems (RAM, etc.).

A workaround for this is to create the new field with the desired data type.
Then run an update query to copy the data from the old field into the new
field. Then delete the old field from the table. Then move the new field to
the "location" of the old field in the field list.

 

[George Nicholson]

To add to Ken's reply, a simple Compact (Tools>Database Utilities>Compact &
Repair) *might* "clear out" enough space for you to change your data type.
As Ken says, two of the steps in changing a datatype is creating a new field
and "deleting" the old field, except the old field (and its data) will
continue to occupy space in the file until you do a Compact (i.e., you might
not be able to see it, but it isn't really, really deleted until Compacting
occurs). The same is true for "deleted" records.

If you still get the same message after compacting, you can always do it
manually. Compacting before you do anything else is probably a good idea
anyhow.

HTH,
--
George Nicholson

Remove 'Junk' from return address.