Changing Field Size

[Lulu]

I undertand how to programmatically create a new field using a construct such
as :

Set fld = db.TableDefs("tblMyTable").CreateField("NewName", dbText,50)
db.TableDefs("tblMyTable").Fields.Append fld

My dilemma is how to change field size programmatically on an already
existing field. So far, all the Knowlegde Base info etc that I have found
talks about creating NEW items or enumerating existing items, but I have yet
to stumble on CHANGING existing items.

Any guidance please?

 

[Douglas J. Steele]

Add a new field, run an Update query to transfer the value from the existing
field to the new field, delete the old field then rename the new field to
the old field's name. That's what Access does under the covers when you do
it through the GUI. (Of course, this will only work if the field isn't part
of a relationship)

 

[Allen Browne]

In JET 4 (Access 2000 and later) you can change the field size by executing
a DDL query statement.

This example changes Table1.Field1 to a 100-character text field:
strSql = "ALTER TABLE Table1 ALTER COLUMN Field1 TEXT(100);"
dbEngine(0)(0).Execute strSql, dbFailOnError

 

[Lulu]

Of the two solutions offered, I ended up using Allen's example. Very tidy.
Almost elegant.

My thanks for the advice from Douglas and Allen.