asked on

Change field size of Text data type fields in Microsoft Access

I need to be able to go through my Access database in Design view and change all the Data type fields that are 'Text' to a Field Size of about 120. Is there a macro I can get in Access to do this?

Daniel Wilson

Iterating the fields is easy:

Public Sub changeAllTextFields(iNewLength As Integer)
Dim T As TableDef
Dim F As Field

For Each T In CurrentDb.TableDefs
For Each F In T.Fields
If F.Type = 10 Then

End If
Next
Next
End Sub

Now ... changing the field size ... haven't found that piece yet! Maybe this will get you started ...

Rey Obrero (Capricorn1)

try this codes, place the codes in a module

Sub ChangeFieldSize(sTable As String, iSize As Integer)
Dim fld As DAO.Field, tdf As DAO.TableDef
For Each tdf In CurrentDb.TableDefs
If tdf.Name = sTable Then
For Each fld In tdf.Fields
If fld.Type = 10 Then
CurrentDb.Execute _
"Alter table " & sTable & " alter column " & fld.Name & " TEXT(" & iSize & ")"
End If
Next
End If
Next

End Sub

jefftwilley

Dim fld As DAO.Field, tdf As DAO.TableDef, TName as string
For Each tdf In CurrentDb.TableDefs
If (Not Tdf.Name Like "msys*") And (Not Tdf.Name = "~*") Then
For Each fld In tdf.Fields
If fld.Type = 10 Then
CurrentDb.Execute _
"Alter table " & sTable & " alter column " & fld.Name & " TEXT("120")"
End If
Next
End If
Next

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jefftwilley

yep...that's gotter Cap.

SOLUTION

jefftwilley

membership

This solution is only available to members.