Changing a field from text to date

[Dimitris]

In a table there is a field which has dates but the field is a text field.
(The data is inserted like: 5/12/2005. (d/m/y). I want to make it a date
field but Access won't let me change it from design because it says I don't
have enough memory. My database has 1,5 million records.
I want to change it because I want to sort the dates by year, but I can't
cause it's a text field. It sorts them by the first number which in my case
it is the day.
Is there any solution?
Thanks
Dimitris

 

[Brendan Reynolds]

The message is a little misleading in that it's actually free hard disk
space that is the problem here, rather than RAM. You could try freeing up
hard disk space on that PC, or moving the database to another PC if you have
one available with more free hard disk space. The extra space will only be
required during the conversion, so after the conversion you can move the
database back, if necessary. You might want to compact it before moving it
back, though, as that's another operation that requires lots of free hard
disk space.

 

[Dimitris]

It is not a hard disk space problem. I have lots of free hard disk space.
And I have the same problem when I move the database to my laptop. Is it
normal for a database with 1,5 million records to change with no problem
the type of a field from text to date?

Thanks
Dimitris

 

[Brendan Reynolds]

In order to change the data type of a field, what the database engine
actually does is to create a new field with the desired data type, copy all
of the data from the old field to the new one, then delete the old one. This
is why a lot of free hard disk space is required during the operation. I'm
not aware of any cause other than lack of free hard disk space that would
cause this particular message in this particular situation. Of course, just
because I am not aware of another cause does not necessarily mean that no
other cause exists. How big is that database, and how much free hard disk
space do you have? Is your hard disk divided into more than one partition?
(It's possible to have lots of free space, but it may be unavailable to the
Windows swap file if it is on another partition.)

 

[Dimitris]

My hard disk is: 74,5 gb, 50gb free space.
My database: 1.175.000 kb

 

[Brendan Reynolds]

I'm not in a position to test this idea, but it occurs to me that possibly
the increased size of the database during the conversion process might be
pushing it over the 2GB limit. If so, you might be able to work around the
problem by doing something like the following ...

1) Create a new, empty database. Import only the table you want to change,
choosing the option to import structure only, not data.

2) Change the field type in this empty table.

3) Link the old table.

4) Use an append query to copy the data from the old, linked table into the
new one.

5) Import any other tables, this time choosing the option to import both
structure and data.

 

[Douglas J Steele]

Can you add a new field to the table (of type Date), then run an Update
query to populate that new date field from the old text field (using CDate)?
You can then delete the old text field once you know that the new field's
okay.

 

[John Vinson]

In a table there is a field which has dates but the field is a text field.
(The data is inserted like: 5/12/2005. (d/m/y). I want to make it a date
field but Access won't let me change it from design because it says I don't
have enough memory. My database has 1,5 million records.
I want to change it because I want to sort the dates by year, but I can't
cause it's a text field. It sorts them by the first number which in my case
it is the day.
Is there any solution?
Thanks
Dimitris

Just to add another possible solution: changing a datatype in a large
table often does generate this (somewhat misleading) error message.

Another way to get the same result is to create a new table, empty,
with the desired datatype. Compact your database to be sure there's
space (or create the new table in a new database, if necessary). Then
run an Append query to migrate the data from the old table into the
new one. Rename the old table to tablename_old, the new table to the
correct name, reset your relationships to the new table, test
everything, delete the old table and use the new one... and compact
again to free up the space formerly occupied by the old table.

John W. Vinson[MVP]