Can't change data type to date in access table

[Ann B]

I added a field to a table in a access database, I changed the data type to
Date/Time. So there is no data currently in the fields. When I go to save
the table it is telling me that there is not enough memory or disk space to
save the changes to the table. This is the only thing I have open, and I
have made table changes in the past to this database. Any ideas on how to
get this change to save?

 

[tina]

when was the last time you ran a compact/repair on the database? if you
haven't done it today, try that first.

hth

 

[John W. Vinson]

I added a field to a table in a access database, I changed the data type to
Date/Time. So there is no data currently in the fields. When I go to save
the table it is telling me that there is not enough memory or disk space to
save the changes to the table. This is the only thing I have open, and I
have made table changes in the past to this database. Any ideas on how to
get this change to save?

How big's the table, both in fields and records? If it's big, you may have
better luck creating a new, empty table with the desired field types, and
running an APPEND query to populate it. Changing a field datatype in place
actually makes Access try to create two copies of the entire table in memory,
which will give this error if the table is too large. The append query is much
less of a memory hog.

 

[Jey]

I was actually just seaching the forum because I was having the same problem.
I wanted to add a double precision field to an existing table that has about
300,000 records. I got three error messages in succession; "some data may be
lost...", "can't change data type...", & "errors encoutered, data types were
not changed...". After reading a bunch of stuff here on the forums & some
trial & error I finally got it to work. Here is what I did:

-compact the database
-open the table in design view
-insert the field, and set it's data type,
-THEN save!

I think the errors were because the first times I was trying it I had saved
after adding the field but before before setting the data type, so it was
trying to change the data type instead of just inserting an empty field of
the type I wanted.

I think the underlying problem is the number of page locks involved in the
behind the scenes shuffling that must be done to change field data types. I
found this: http://support.microsoft.com/kb/161329
....but I'm glad I got it to work. I don't want to monkey around with my
registry!

Hope that helps,
Jey