DAO, Need to change field properties at run time

There must be a way, but I have yet to find it. Need to set several field properties at run time (vb6). This approach generates errors (in most cases) and I can find no other. Is it possibe (since allowzerolength and defaultvalue do work) or am I missing something basic. Code sample below.

dim db as database
dim tb as tabedef
dim f1 as field 'source field
dim f2 as field 'destination field
dim x as integer
dim y as integer
dim z as variant
dim w as integer
dim q as integer

set db = opendatabase("mydatabase.mdb")

Set tb = db.TableDefs("Test Table")

'get first field's properties
Set f1 = tb.Fields("field1")
x = f1.Type
y = f1.Size
z = f1.DefaultValue
w = f1.Attributes
q = f1.AllowZeroLength

'attempt to make 2nd field look like first field
Set f2 = tb.Fields("field2")
f2.Type = x 'errors
f2.Size = 50 'errors
f2.DefaultValue = z 'works
f2.Attributes = w 'errors
f2.AllowZeroLength = q 'works

Maybe these properties (noted with errors) can't be changed at run-time in this manner. Is there another way?

Randy

I have had the same problem with DAO. The last two days I have been looking for a solution as well. I went to Microsoft's MSDN January 2001 library cd, and found that the Tabledef is READ ONLY when using it that way. From what I can see, and as frustrating as it is I don't think you can change all field types at run time. I have attached a Word Document that I copied from the library, maybe this will help you.

I don't think you can do it in DAO (and I'm not really sure about ADO either). I have come across this situation before, and the only way I could resolve it was by adding a new column to the table:

ALTER TABLE table ADD COLUMN field type[(size)]

This will allow you to set 2 of the 3 properties you are getting errors on (i.e. Type and Size).
Hope this can help a little

It has been a while. I had to go back and look to see how this problem was resolved as I remeber that I had to be able to change the field size (I was doining database updates that included field changes). Well, it was not elegant but it worked. Seems I just created a blank database and setup the new tables and fields as I needed, then copied back over the data, deleted the original database and renaming the new one. hehe. Talk about putting up new walls when only a paint job is needed. Tells me that I was unable to solve the changing "size" at run time issue. Good luck.

I just checked out the DAO help on this, and it clearly says that for almost all the objects which contain the Field object, the Size and other properties are Read Only. Only if you create a new Field object and have not yet appended it to any collection such as the TableDef object, you can set the size and other properties.

So, in a situation where the properties of a field needed to be changed, the steps would be as follows:

• Create a new instance of the field using Set fldNew = New Field statement.
• Set the properties of this new field to what you want.
• Append the field object to the .Fields collection of the TableDef or other object you are using.
• Remove the old Field object from the .Fields collection of the TableDef or other object you are using.

.