hange Field Size in VBA

  • Thread starter Thread starter Pat
  • Start date Start date
P

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
 
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
 
Just a comment that if the field is involved in any relationships, you need
to delete the relationship and readd it as well.
 
Douglas said:
Just a comment that if the field is involved in any relationships, you need
to delete the relationship and readd it as well.
Click to expand...
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.
 
Code could intercept this case... I don't think I am goning to do
that, however.
Click to expand...

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
 
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?
Click to expand...

I didn't get beyond thinking "There's something wrong with the table
design".
 
100 tables suggests that someone is Storing

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

Great Minds Think Alike .... <g>


Tim F
 
Tim said:
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?
Click to expand...

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