How to change field type using DAO

Access makes this task very easy – you just open a table in design view, select new field type and save table. But how you can do this in code? There are 4 basic steps – create new field, copy data from old field to new, delete old field and rename new field to old. There is a sample DAO code to perform this task for a sample table shown on the picture:

Function ChangeFieldType()

Dim dbsData As DAO.Database

Dim tdf As TableDef, fld As DAO.Field

Set dbsData = CurrentDb

'---Create New Field
dbsData.TableDefs.Refresh
Set tdf = dbsData.TableDefs("MyTable")
Set fld = tdf.CreateField("MyFieldNew", dbText, 50)
'Optional: set default value
fld.DefaultValue = "0"
'We set ordinal position, just after old field
fld.OrdinalPosition = 2
'And append
tdf.Fields.Append fld

'Copy values from old field to a new one
dbsData.Execute _
"Update MyTable Set MyFieldNew=MyField", dbFailOnError

'Delete old field
tdf.Fields.Delete "MyField"
tdf.Fields.Refresh

'Rename new field to old
tdf.Fields("MyFieldNew").Name = "MyField"
tdf.Fields.Refresh
'Done!
Set tdf = Nothing


End Function