progrmatically modify field type ms access

' Make sure you have a reference to the library:
' Microsoft ADO Ext. 2.x for DDL and Security
Option Compare Database
Function ModifyTables()
Dim cat As New ADOX.Catalog 'Root object of ADOX.
Dim tbl As ADOX.Table 'Each Table in Tables.
Dim col As ADOX.Column 'Each Column in the Table.
Dim strSQL As String
'Point the catalog to the current project's connection.
Set cat.ActiveConnection = CurrentProject.Connection

'Loop through the tables.
For Each tbl In cat.Tables
If InStr(tbl.Name, "Doc") Then
'Debug.Print tbl.Name, tbl.Type
'Loop through the columns of the table.
For Each col In tbl.Columns
'Debug.Print , col.Name, col.Type,
strSQL = "Alter table [" & tbl.Name & "] alter column [" & col.Name & "] Text(255)"
Debug.Print strSQL
DBEngine(0)(0).Execute strSQL, dbFailOnError

Next
Debug.Print "--------------------------------"
End If
Next

'Clean up
Set col = Nothing
Set tbl = Nothing
Set cat = Nothing
End Function


VBS version

Dim strSQL 'As String
Dim Conn 'As ADODB.Connection
Dim cat 'As ADOX.Catalog 'Root object of ADOX.
Dim tbl 'As ADOX.Table 'Each Table in Tables.
Dim col 'As ADOX.Column 'Each Column in the Table.


Set Conn = CreateObject("ADODB.Connection")
Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=maplibrary.mdb"

'Create catalog object
Set Cat = CreateObject("ADOX.Catalog")

Set Cat.ActiveConnection = Conn

'Loop through the tables.
For Each tbl In cat.Tables
If InStr(tbl.Name, "Doc") Then
'Debug.Print tbl.Name, tbl.Type
'Loop through the columns of the table.
For Each col In tbl.Columns
'Debug.Print , col.Name, col.Type,
strSQL = "Alter table [" & tbl.Name & "] alter column [" & col.Name & "] Text(255)"
' Debug.Print strSQL
Conn.Execute strSQL, dbFailOnError

Next
'Debug.Print "--------------------------------"
End If

Next

WScript.Echo "Completed!"