Modify a field data type

[larcheve]

Hi,

Could you help me on the following question:

I would like to modify a field data type of an already built structure of a table in an Access database.
So far, i can only change the name of the field without any problem. When i try to change the field type, i got
a run-time error 3192 informing me that it is illegal and probably a read only property.

Here is the sample code : (the line that the compiler does'nt like is -> td.Fields(j).Attributes = dbUpdatableField Or dbVariableField)

Set dbmap = DBEngine.Workspaces(0).OpenDatabase("test.mdb")
For intctr = 0 To dbmap.TableDefs.Count - 1
Set td = dbmap.TableDefs(intctr)
If (td.Attributes And dbSystemObject) = 0 Then
List1.AddItem td.Name
List1.AddItem td.Attributes
List1.AddItem td.Updatable
For j = 0 To td.Fields.Count - 1
Set fld = td.Fields(j)
List1.AddItem fld.Name + " " + " " + CStr(fld.Type) + " " + CStr(fld.Attributes)
If fld.Name = "v1" Then
td.Fields(j).Attributes = dbUpdatableField Or dbVariableField
td.Fields.Refresh
fld.Type = 1
End If
Next j
td.Fields.Refresh
End If
Next intctr
dbmap.Close




Here is there a way around ?

bye

[Lothar Haensler]

IMHO you have to change the underlying table definition using SQL or the Access GUI.

[czimmerman]

I think that this from the Visual Studio help file explains your problem:

"For a Field or Property object, [the type] property is read/write until the object is appended to a collection or to another object, after which it's read-only."

In other words, since your field object is part of the tabledef.fields collection, you can't modify the type property.

The only solution I can think of is not pretty: 1) Create a new field object and set its properties. 2) Save all the data from the old field into an array. 3) Delete the old field. 4) Append the new field to the database's tabledefs collection. 5) Write the saved data back to the database.

Charlie Zimmerman
http://www.freevbcode.com
czimmerman@freevbcode.com