Change field size with code

[rbm]

I am trying to change all the text fields in my tables with a certain name to
a size of 33 characters using a module. It worked for setting
AllowZeroLength, but a similar code to change the field size returns an error
3219 "Invalid Operation".

The code runs through the tables and fields ok, but hangs up on the line
that sets the field size to 33. Any help would be appreciated.
The code I am using is this:

Public Function ChangeTxtFldSize()
On Error Resume Next

Dim i As Integer, J As Integer
Dim db As Database, td As TableDef, fld As Field
Set db = CurrentDb()

For i = 0 To db.TableDefs.Count - 1
Set td = db(i)

For J = 0 To td.Fields.Count - 1
Set fld = td(J)

If fld.Type = DB_TEXT And fld.Name Like ("*School*") Then
fld.Size = 33
End If

Next J
Next i
db.Close
Set db = Nothing

End Function

 

[Allen Browne]

You cannot alter the size of the field using DAO.

In JET 4 (Access 2000 and later), you can execute a DDL statement to do it.
This kind of thing:
Dim strSql As String
strSql = "ALTER TABLE MyTable ALTER COLUMN MyField TEXT(33);"
DBEngine(0)(0).Execute strSql, dbFailOnError

Using DAO, you have to CreateField() of the new size, execute an UPDATE
query to populate it, and then remove the old field.

 

[rbm]

Thank you for the quick response. I just figured that if I could do it with
AllowZeroLength, I could do it with FieldSize. I am using Access97 so I
guess I am stuck with changing each one individually. Thanks again.

 

[Pat Hartman\(MVP\)]

The DDL will probably work for A97 also. If you have syntax problems, look
in Access help. Open the table of contents and drill down. There should be
an entry for SQL or something like that. Within that you should find a
section on DDL.

 

[rbm]

Thanks. I will look there.

The DDL will probably work for A97 also. If you have syntax problems, look
in Access help. Open the table of contents and drill down. There should be
an entry for SQL or something like that. Within that you should find a
section on DDL.

 

[peregenem]

The DDL will probably work for A97 also.

Yes, it will work on a Jet 3.x file format but you have to use the OLE
DB Provider for Jet 4.0. The OLE DB Provider for Jet 3.51 simply does
not have the syntax.

 

[Douglas J. Steele]

There's no reason you can't automate Allen's suggestion of using
CreateField() to create the new field, executing an UPDATE query to populate
it, and then removing the old field.

Something like the following untested air-code:

Sub ChangeFieldSize(TableName As String, FieldName As String)
On Error GoTo Err_ChangeFieldSize

Dim dbCurr As DAO.Database
Dim tdfCurr As DAO.TableDef
Dim strSQL As String

Set dbCurr = CurrentDb()
Set tdfCurr = dbCurr(TableName)

With tdfCurr
' Rename the existing field
' (put the word "Old" in front of the existing name)
.Fields(FieldName).Name = "Old" & FieldName
' Create the new field
.Fields.Append .CreateField(FieldName, dbText, 33)
End With

' Update the new field with values from the old field
strSQL = "UPDATE [" & TableName & "] " & _
"SET [" & FieldName & "] = [" & "Old" & FieldName & "]"
dbCurr.Execute strSQL, dbFailOnError

' Delete the old field
tdfCurr.Fields.Delete "Old" & FieldName

End_ChangeFieldSize:
Set dbCurr = Nothing
Exit Sub

Err_ChangeFieldSize:
MsgBox Err.Number & ": " & Err.Description
Resume End_ChangeFieldSize

End Sub