hange Field Size in VBA

[Pat]

I've got the code, it compiles, but when I run it I
get "Invalid Operation".

Public Sub pChangeSize()
On Error GoTo pChangeSizeError

Dim db As Database
Dim tableName As String
Dim tbd As TableDef
Dim thisField As Field

Stop
Set db = CurrentDb
Set tbd = db.TableDefs("tempWebInspReport")
Set thisField = tbd.Fields("Estab_id")
With tbd
thisField.Size = thisField.Size + 1
End With

pChangeSizeError:
MsgBox Error$, 0, "Restaurant Database"
Exit Sub
End Sub

I have about 100 files to change the size of 1 field in.
Can you help, please?

Thanks!

Pat Siers

 

[Bas Cost Budde]

Function changeFieldType(cTabel As String, cVeld As String, nType As
Long, Optional nSize = 50) As Boolean
'workhorse for ChangeStructure
'changes field type in all tables it occurs in
'returns True on success
Dim db As Database, td As TableDef, fd As Field
Dim cFile As String
Dim cLocalTable As String
Dim nCol As Long
changeFieldType = True
set db=currentdb
cLocalTable = cTabel
Set td = db.TableDefs(cLocalTable)
'it is forbidden to directly change type. So we do:
'1 insert new field
Set fd = td.CreateField(cVeld & "1", nType, nSize)
td.Fields.Append fd
td.Fields.Refresh
'2 copy data (convert, you may hope)
db.Execute "update [" & td.Name & "] set [" & fd.Name & "]=" & cVeld
'3 remove old field but store its position
nCol = td.Fields(cVeld).OrdinalPosition
td.Fields.Delete cVeld
'4 rename new field
td.Fields(cVeld & "1").Name = cVeld
'5 put field on former position
td.Fields(cVeld).OrdinalPosition = nCol
On Error Resume Next
Set fd = Nothing
Set td = Nothing
db.Close
Set db = Nothing
End Function

 

[Douglas J. Steele]

Just a comment that if the field is involved in any relationships, you need
to delete the relationship and readd it as well.

 

[Bas Cost Budde]

Just a comment that if the field is involved in any relationships, you need
to delete the relationship and readd it as well.

Indeed. Does Access do this behind the scenes when I change the field
type? No; it bounces with the message "You cannot change the type fof
this field@@it is part of one or more relationships"

Code could intercept this case... I don't think I am goning to do that,
however.

 

[Tim Ferguson]

Code could intercept this case... I don't think I am goning to do
that, however.

Although we all make errors and have to go back and change field sizes, is
it only me that read this message and thought that having to make the same
change programmatically in 100 tables suggests that someone is Storing
Information in Table Names instead of fields?

B Wishes


Tim F

 

[John Nurick]

Although we all make errors and have to go back and change field sizes, is
it only me that read this message and thought that having to make the same
change programmatically in 100 tables suggests that someone is Storing
Information in Table Names instead of fields?

I didn't get beyond thinking "There's something wrong with the table
design".

 

[Tim Ferguson]

100 tables suggests that someone is Storing

I didn't get beyond thinking "There's something wrong with the table
design".

Great Minds Think Alike .... <g>


Tim F

 

[Bas Cost Budde]

Although we all make errors and have to go back and change field sizes, is
it only me that read this message and thought that having to make the same
change programmatically in 100 tables suggests that someone is Storing
Information in Table Names instead of fields?

You can find out you have an error after delivering the application to
100 customers... But the point needs consideration and further study, OP!