AccessBlog.net

News, links, downloads, tips and tricks on Microsoft Access and related

About Me Search
Alex
Name:Alex Dybenko

Location:Moscow, Russia

Friday, March 02, 2007

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

Labels:

5 Comments:

Blogger BasicElement said...

You saved the day! :)
It was really helpful for me and came in the righ time. Thank you!

Victor

5:18 PM  
Anonymous Anonymous said...

THANKS!!!!!!!!

1:02 AM  
Anonymous Jones said...

You can also use SQL

Something like

currentdb.execute("ALTER TABLE yourTable ALTER COLUMN newType")

should do well

6:14 PM  
Anonymous Anonymous said...

Awesome...this may not be the only way to do it but it worked. Thanks for posting!

5:49 AM  
Blogger Janet said...

Might be seven years later, but this code was exactly what I needed. Thanks.

11:23 AM  

Post a Comment

<< Home