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
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
Labels: access field type DAO
5 Comments:
You saved the day! :)
It was really helpful for me and came in the righ time. Thank you!
Victor
THANKS!!!!!!!!
You can also use SQL
Something like
currentdb.execute("ALTER TABLE yourTable ALTER COLUMN newType")
should do well
Awesome...this may not be the only way to do it but it worked. Thanks for posting!
Might be seven years later, but this code was exactly what I needed. Thanks.
Post a Comment
<< Home