Set field size at runtime

[Reznor_X]

How do I set the size of a field at runtime?

I've tryed the following...

----

dim db as database
set db=OpenDatabase("somedb.mdb")
db.TableDefs("sometable").Fields("somefield").size = 90




(this returns Error 3219, Invalid Operation)


db.Execute "ALTER TABLE sometable ALTER COLUMN somecolumn text(90)"




or


db.Execute "ALTER TABLE sometable ALTER COLUMN somecolumn c(90)"




(this returns syntax error in ALTER TABLE statement)

how can I do this from VB?? it can be easily done from Access...

Thanks in advance

[Kyle Burns]

Find the exact DDL statement that works in SQL view of an Access query and paste that into your code.

[Reznor_X]

thanks my friend, but this doesn't work...

here's the fact :

The size of a field can only be changed using Access 2k at runtime, otherwise it'll return an error.

rs.fields("somefield").size = x won't work with Acc97
db.execute "ALTER TABLE..." won't work with Acc97

I've found that the easiest way to do this is the old fashioned cut/paste method create a temp field, copy all data from the field you want to resize to the temp, delete the old one then re-create it with the new attributes, finally, return the data you've copied to the temp field to the newly created one and delete the temp... crappy, isn't it? but it works.

if anyone knows a better method, PLEASE, let me know.

thanks

Rez