field data type errors

[saraua]

I am trying to change 2 fields from a text to a number data type and am
getting the error message "Microsoft Office Access can't change the data
type. There isn't enough disk space or memory". Is compacting the database
the only solution for this? I have done this and am still getting this error.

 

[Jeanette Cunningham]

saraua,
use a different approach.
First backup the table for safety of your data.
Create 2 new fields with the number data type you want.
Use an append query to add the current data from the 2 text fields to the 2
new number fields.
Delete the 2 text fields you no longer want.

Jeanette Cunningham

 

[KARL DEWEY]

Use an append query to add the current data from the 2 text fields to the 2
new number fields.
Better make that an UPDATE query instead of append.

 

[John W. Vinson]

use a different approach.
First backup the table for safety of your data.
Create 2 new fields with the number data type you want.
Use an append query to add the current data from the 2 text fields to the 2
new number fields.
Delete the 2 text fields you no longer want.

It may be even more efficient to create a completely new table. Open the table
in design view; choose File... Save As to save the table definition, *table
definition only, no data*, to a new empty table. Change the datatypes in this
empty table, and then run an Append query to migrate the data. After you check
that all is well (and that your backup, made before this all started, is
working and intact!!) you can delete the old table, rename the new one, and
Compact and Repair the database.

The reason for the error is that Access tries to keep a copy of the entire
table - old version AND new version - in memory while it's making the change.
If the table is at all large this will fail.