Change Field Property

[DEI]

Is there any way to programmatically change a field's datatype property in a
table? Either with DAO or ADO?

I tried to do it with the .type property with the code below but it does not
work - it might be read only.

CurrentDb.TableDefs(2).Fields(2).Type = dbDate and I get an error.

Thank you,

DEI

 

[Clifford Bass]

Hi,

You will probably need to add a new field, with a new name, of the
desired type, copy the data from the existing field to the new field, change
other settings such as Required, delete the existing field and then rename
the new field to the existing field's name. If it is part of an index you
may need to make changes to your index(es). I suspect this is what Access
does for you automatically when you change the field type in the table
designer.

Also, I would recommend against referring to a particular table or a
particular column using an index number since that could easily change and
you would end up modifying the wrong table and/or column. Better to use
their actual names:

CurrentDb.TableDefs("tblMyData").Fields("Some_Column").Type

Hope that helps,

Clifford Bass

 

[Rob Wills]

You can do it directly using SQL

Currentdb.Execute "ALTER TABLE [tblname] ALTER COLUMN [fieldname] DATE"

But you need to be careful that you don't corrupt existing data

 

[Clifford Bass]

Hi Rob,

Are there restrictions on the ability to change between types? Say
text to date. What would happen when there are invalid dates in a text field?

Corruption would be one of my worries with a significant type change.
Backup is of course always important when doing that kind of change. The
advantage of creating a new column and copying is that it can expose, without
nearly the risk, the problem spots. You can see if the conversion of the
data worked correctly. Then you only delete the old column once you are sure
that all is well.

Clifford Bass

 

[Rob Wills]

Totally agree - hence my warning at the bottom...

Obviously it's not such an issue if you're going from Date to Text...

 

[Clifford Bass]

Hi Jim,

That is a good idea regardless of which method you use to change the
type.

Clifford Bass