Author: Calvin Smith
http://www.CalvinSmithSoftware.com/codedisk/sneakpeek.htm

Sub AlterColumnSize(tblYourTableName As String, _
YourColumnName As String, _
NewColumnSize As Integer)

On Error GoTo ErrorHandling_Err

    ' ---------------------------------------------------------------
    ' Purpose: Example of how to change the size of a column
    '
    ' Example usage:
    '       AlterColumnSize "tblYourTableName","TargetColumn", 255
    ' ---------------------------------------------------------------
    
Dim ThisDB As DAO.Database
Dim qdf As DAO.QueryDef
Set ThisDB = CurrentDb

' Create a dummy QueryDef object.
Set qdf = ThisDB.CreateQueryDef("", "Select * From Foo")

' Add a temporary column to the table.
qdf.SQL = "Alter Table [" & tblYourTableName & "] Add Column YourTempColumnName Text(" & NewColumnSize & ")"
'
'NOTE: The following line is an example of how to add a float (double) data type
'qdf.SQL = "Alter Table [" & tblYourTableName & "] Add Column YourTempColumnName Float"
'
qdf.Execute

' Copy the data from old column into the new column.
qdf.SQL = "Update DISTINCTROW [" & tblYourTableName & "] Set YourTempColumnName = [" & YourColumnName & "]"
qdf.Execute

' Delete the old column.
qdf.SQL = "Alter Table [" & tblYourTableName & "] Drop Column [" & YourColumnName & "]"
qdf.Execute

' Rename the temporary column to the old column's name.
ThisDB.TableDefs("[" & tblYourTableName & "]").Fields("YourTempColumnName").Name = YourColumnName

ErrorHandling_Err:
    If Err Then
        'Trap your error(s) here, if any!
    End If
End Sub