Change Field Property



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,


Clifford Bass


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

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:


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

Clifford Bass

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question