VBA to modify table design

[Paul Turley]

I need to modify a column definition (fom Text (50) to Text (100)) in code.
Against an Acess back-end, I don't think I can use ALTER TABLE so I assume
it must be done in VBA...

 

[Douglas J. Steele]

Actually, you can't do it using VBA. You'll have to add a new field of the
correct size, run an update query to populate the new field, delete the old
field then rename the new field. That's what Access does behind the scenes
when you do this through the GUI. Don't forget to compact your database once
you're done.

On the other hand, you are supposed to be able do this using ALTER TABLE in
newer versions of Access, but I think you need to run the DDL through ADO,
not DAO.

 

[Allen Browne]

Following up on Doug's suggestion, if you use Access 2000 and later, you can
use DDL through ADO to alter the field size.

Example:

Sub ModifyFieldDDL()
Dim strSql As String
strSql = "ALTER TABLE MyTable ALTER COLUMN MyField TEXT(100);"
CurrentProject.Connection.Execute strSql
End Sub

 

[Paul Turley]

Thank you!