Change Text Field Length

[Lou Civitella]

How can I change the length of a text field programatically?

TIA,
Lou

 

[Guest]

How can I change the length of a text field programatically?

Hi Lou,

The Size property is read-only for an existing field so you need to create a new field with the new size, copy the data from the old field to the new field, delete the old field and rename the new field to the old name. You may lose data if the new size is smaller than the old size.

Something like this sets the size to 189:

Dim db As Database
Dim td As TableDef

Set db = CurrentDb
Set td = db.TableDefs("TableName")

With td

.Fields.Append .CreateField("tmp", dbText, 189)
db.Execute "update TableName set tmp = OldName;", dbFailOnError
.Fields.Delete "OldName"
.Fields("tmp").Name = "OldName"

End With

Jay