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

 

[JimBurke via AccessMonster.com]

I would think that if you wanted to make a change from text to date, or
anything else that could cause errors, you'd need a routine that would check
all fields for valid values, e.g.

Public Function CanConvert() As Boolean

SQLText = "SELECT IsDate(dateField) As ValidDate FROM tblName WHERE ..."

If DCount("*",SQLText) > 0 Then
CanConvert = False
Else
CanConvert = True
End If

End Function

If you have invalid values, don't run the ALTER, or else if you can, convert
all the invalid values to Null (or some valid value if possible), then run
the ALTER.

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

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

 

[Linq Adams via AccessMonster.com]

If you go into the Query Grid and change a Text field to a Date/Time datatype,
Access will flash a warning stating that X number of records have been
deleted, where X is the number of records where the data in the targeted
field isn't valid for the new datatype. Note that it says that "records have
been deleted," not thet they will be deleted, but it's already happened.
Doing it this way, you can back out of the change before the query is saved
and not dump the records, but I suspect that doing it by code may result in
the warning not appearing, with Access simply dumping the records.

Out of curiosity, why in the world would you need to do this thru code? If
you have proper permissions to make design changes, surely it would be
simpler to merely do so within the table design view, and if you don't, i.e.
the design view is locked down, as in an MDE, Access won't allow changes to
design, even thru code.

 

[Clifford Bass]

Hi Jim,

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

Clifford Bass

 

[JimBurke via AccessMonster.com]

Whoops, in the DCount you'd need criteria...

If DCount("*",SQLText, "ValidDate = False") > 0

I would think that if you wanted to make a change from text to date, or
anything else that could cause errors, you'd need a routine that would check
all fields for valid values, e.g.

Public Function CanConvert() As Boolean

SQLText = "SELECT IsDate(dateField) As ValidDate FROM tblName WHERE ..."

If DCount("*",SQLText) > 0 Then
CanConvert = False
Else
CanConvert = True
End If

End Function

If you have invalid values, don't run the ALTER, or else if you can, convert
all the invalid values to Null (or some valid value if possible), then run
the ALTER.

Hi Rob,

[quoted text clipped - 15 lines]