Change Data Type using a Macro

[Guest]

How do I change a table data type using a macro? Specifically I want to
change a data type from text to numeric.

 

[Guest]

Create a function in a module and run alter table

Function ChangeFieldType()

CurrentProject.Connection.Execute ""ALTER TABLE TableName ALTER COLUMN
FieldName Long"

End Function

And you can run this function from the macro

 

[George Nicholson]

Using a macro? not sure that you can.
Using VBA? You can do the following:
- Rename the "Old" field
- Add a New field of the new type to the desired table
- Copy data from Old field to New field
- Delete old field

That's what Access does under the hood when you change a field's data type,
*if* there is available disk space. Since it all this is under the hood the
user may be under the impression you can simply "change" a field's data
type, when you really can't; you gotta create a new field & copy data to it.

Compact when done to reclaim the file space claimed by the deleted field.

HTH,

 

[Pat Hartman \(MVP\)]

Just the thought of this gives me the willies. What are you trying to do?
Although you can use DDL, DAO, and ADO to manipulate structures, most
changes in an Access application are made by the developer via the GUI.
Usually structural changes from code are only made when distributing a new
front end that requires table changes in the back end. Is that what you are
trying to automate?