changing data types

[Thai]

Is there a way to change data types of a field using ado/adox? I ahve tried and it failed, here is my code:

Dim cat As New ADOX.Catalog

cat.ActiveConnection = myCN  'myCN is already open

For X = 0 to cat.Tables("Table1").Columns.Count -1
  cat.Tables("Table1").Columns(X).Type = adLongVarWChar
Next

That doesn't work, it gives me an error. Is there a way to do this in code?

Thanks,
Thai

Do you want to change the actual table structure(DataType) or do you want to change the data type returned in your recordset? What type of database(can I use Transact-SQL or does it have to be ANSII)

[Thai]

It is a Microsoft Access DB and I want to change the table structure, so field1 would go from TEXT to MEMO.

Thanks,
Thai

[JHausmann]

Yes you can but you have to do it indirectly.

Make sure you have a backup of your database before proceeding.

Create your new table with the fields the way you want to look, copy the data from the old table (insert into newtable select * from oldtable will work as long as you have the same amount of fields and the datatypes are compatible [changing a char field to a memo field is allowed]), drop your old table, then copy the new table back to the old tables name (select * from newtable insert into oldtable) and, finally drop the new table with the "new name".