field data type errors

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

saraua - 11 Apr 2008 23:13 GMT

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.

Reply to this Message

Jeanette Cunningham - 11 Apr 2008 23:23 GMT

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

>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.

Reply to this Message

KARL DEWEY - 11 Apr 2008 23:33 GMT

>>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.

Signature

KARL DEWEY
Build a little - Test a little

> saraua,
> use a different approach.
[quoted text clipped - 12 lines]
> > the only solution for this? I have done this and am still getting this
> > error.

Reply to this Message

John W. Vinson - 12 Apr 2008 00:04 GMT

>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.

Signature

John W. Vinson [MVP]

Reply to this Message