I have the below code to change the length of an existing field (in an existing table); it seems to work fine, until it does not.
That is to say:
- it creates a new field of the proper length,
- copies the data from the old field to the new,
- deletes the old field,
- renames the new field to the old field's name,
and then...
- the new field length is back at being the same as the old one (?!?)
I'm certain that it will be obvious; but then again, it's not obvious to me !
with much appreciation in advance,
mark
Code:
Function ChangeFieldSize(ByVal vFilePath As Variant, TblName As String, FldName As String, NewSize As Byte)
Dim Td As TableDef
Dim Db As Database
Dim DbPath As Variant
Dim FldPos As Integer
Dim rs As Recordset
Dim X As Integer
'get back end path of linked table
Set Db = OpenDatabase(vFilePath)
'get table
Set Td = Db.TableDefs(TblName)
'change field size
If Td.Fields(FldName).Size <> NewSize Then
With Td
On Error Resume Next
If NewSize > 0 And NewSize < 256 Then 'text field
.Fields.Append .CreateField("TempFld", dbText, NewSize)
Else '0 is memo field
.Fields.Append .CreateField("TempFld", dbMemo)
End If
Set rs = Db.OpenRecordset(TblName)
While Not rs.EOF
rs.Edit
rs!TempFld = rs.Fields(FldName)
rs.Update
rs.MoveNext
Wend
rs.Close
'delete old field
.Fields.Delete FldName
'rename new field to original
.Fields("TempFld").Name = FldName
'*****
'at this point the length of the field is back at the original length, the change to the new length has been dropped (?)
MsgBox .Fields(FldName).Size
'*****
End With
If Err <> 0 Then GoTo Done
End If
ChangeFieldSize = True 'defaults to false if it fails to get here
Done:
If Not Db Is Nothing Then Db.Close
End Function