Change field type in VBA

[Max]

I have the following in a module in an Access 2K database:

Public Function ChangeFieldName()
Dim dbs As Database, tdfloop As TableDef, i As Integer, MyField As String

Set dbs = CurrentDb()

With dbs
For Each tdfloop In .TableDefs
With tdfloop
For i = 1 To .Fields.Count
MyField = .Fields(i - 1).Name
If MyField = "OptionOrderCaption" Then
.Fields(i - 1).Name = "OptionUpgradeNo"
.Fields(i - 1).Type = "Number"
End If
Next i
End With
Next tdfloop
End With

The line .Fields(i - 1).Type = "Number" is not working. What am I doing
wrong?

Need to set a text field to a number field.

Thanks in advance.

 

[Douglas J. Steele]

You can't change the data type of a field in that manner: once it's been
added to the TableDef object, the Field's Type property is read-only. As
well, there's no Numeric data type, and even if there were, you wouldn't set
it using a text string: the Type property is numeric.

If you want to continue using DAO as in your example, you'll need to add a
new field of the correct type, run an update query to populate it with
existing data (you can ignore this step if there's no data in the field),
then delete the field that's no longer required.

Alternatively, in Access 2000 and up, you can use the ALTER TABLE ALTER
COLUMN DDL.

 

[DVDLIAO]

try to modify the code ...


.Fields(i - 1).Type = dbIntege