Modify Data type in Access Macro from text to number

I have a process that I would like to automate.  The process is using an access table that has a field that was imported in as "text".  I would like to change the data type to number.  I can manually do it by just going into the the table.  What is the code to modify the data type FieldA from "text" to "number"?
moriniaAdvanced Analytics AnalystAsked:
Who is Participating?
Rey Obrero (Capricorn1)Commented:
currentdb.execute "alter table NameOftable alter column NameofField number"
Leigh PurvisDatabase DeveloperCommented:
That'll do it.  Though IMHO you should be pretty precise about the data type you want.
The above will give you a Double number type by default.  Which is OK if you want it - but
CurrentDb.Execute "ALTER TABLE TableName ALTER COLUMN FieldA Double"
would be more explicit.
And of course if you wanted a long integer then you should be specific about that:
CurrentDb.Execute "ALTER TABLE TableName ALTER COLUMN FieldA Integer"
An integer:
CurrentDb.Execute "ALTER TABLE TableName ALTER COLUMN FieldA SmallInt"

and so on.
Same idea though - and for a single operation likely the best choice.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.