Changing a field's data type

  • Thread starter Thread starter Peter Hill
  • Start date Start date
P

Peter Hill

Hello,

(using Access 2000)

What I'm trying to do is change a field's data type to
text (for now) through code.

My initial attempt was as follows:
=
Sub Change_Field_Type (table_name As String, field_name As
String)

Dim My_Table As DAO.TableDef

Set My_Table = CurrentDb.TableDefs(table_name)
My_Table.Fields(field_name).Type = vbText

End Sub
=

Unfortunately, it isn't that simple.
I now know why - you have to append a field then set that
data type and move data across to that field then delete
the original field and rename the new one. Is that
correct?

Anyway - what I need to know - is how do I append a new
field to a tabledef and then set it's datatype? And then -
move data across to it?

Regards,
Pete
 
See online help for the CreateField method. That will let you add the new
field to the table through code. Then get the data in there with an Update
SQL statement:

dim db as database, sql as string
set db = currentdb()
sql = "UPDATE MyTable SET NewField = OldField"
db.execute sql, dbfailonerror
msgbox db.recordsaffected & " records affected"
set db = nothing

HTH,
TC
 
Once the field has been added to the TableDef's Fields collection, its type
is read-only: you cannot change it through DAO methods.

Try TC's suggestion of adding a new field and running an Update query to
copy the data from the old field to the new field.

It's also possible to use the ALTER TABLE DDL statement to do this (but I've
never bothered)
 
If you want to follow Douglas' suggestion, the statement would be something
like this:
"ALTER TABLE MyTable ALTER COLUMN MyField TEXT(100);"
 
Cheers for all your help guys. I've come up with
something that works.

For anyones interest (as you can tell - I'm only a novice
at this, but it works!):

Function change_data_type(field_name As String, Table_Name
As String, new_type As String)

Dim mytable As DAO.TableDef
Dim dbs As Database
Dim data_type As Integer

Set dbs = CurrentDb
Set mytable = dbs.TableDefs(Table_Name)

' this next line is to look up the integer value of the
datatype which I have stored in another table

data_type = DLookup
("[Field_Constant]", "Data_Type_Constants", "[Field_Descrip
tor] = '" & new_type & "'")

mytable.Fields.Append mytable.CreateField
("Temp_Field_Holder_12345", data_type)

DoCmd.SetWarnings False
DoCmd.RunSQL "UPDATE [" & Table_Name & "] SET
[Temp_Field_Holder_12345] = [" & field_name & "]"

mytable.Fields.Delete (field_name)

mytable.Fields.Append mytable.CreateField(field_name,
data_type)

DoCmd.RunSQL "UPDATE [" & Table_Name & "] SET [" &
field_name & "] = [Temp_Field_Holder_12345]"
DoCmd.SetWarnings True

mytable.Fields.Delete ("Temp_Field_Holder_12345")

End Function
 
Back
Top