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?
Last Comment
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
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
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
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
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
yep...that's gotter Cap.
SOLUTION
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Very Good Guys. Finally got to try it. : )
Microsoft Access
Microsoft Access is a rapid application development (RAD) relational database tool. Access can be used for both desktop and web-based applications, and uses VBA (Visual Basic for Applications) as its coding language.
226K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
TRUSTED BY
Public Sub changeAllTextFields(iNewLe
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 ...