Changing Data Type 1

[mans]

Hello,

Below is the code I used to create a field within a pre-existing table. Can somebody please let me know what code I should use to change a PRE-EXISTING field data type from dbText to dbMemo without deleting it first (using the table and field below if possible).

Set td = daoDB36.TableDefs("Tale1&quot
Set F = td.CreateField("Comments", dbText)
F.Required = False
td.Fields.Append F

Thank You

 

[swilliams]

This is taken from VB help files:

"Settings and Return Values

The setting or return value is a constant that indicates an operational or data type. For a Field or Property object, this property is read/write until the object is appended to a collection or to another object, after which it's read-only."

So, you cannot change the data type of the field AFTER you have executed the line:

td.Fields.Append F

What you could do is create another field "Comments1" and set its type to dbMemo, then run an update query - UPDATE Table1 SET Comments1 = Comments - so that this field holds the same as the text field. Then, delete the text field and rename the memo field:

Dim td As TableDef
Dim f As Field
Dim db As Database
Dim fMemo As Field
Set db = Workspaces(0).OpenDatabase("C:\aa\db1.mdb", False, False)

Set td = db.TableDefs("Table1&quot

' add text field
Set f = td.CreateField("Comments", dbText)
f.Required = False
td.Fields.Append f

' add memo field
Set fMemo = td.CreateField("Comments1", dbMemo)
fMemo.Required = False
td.Fields.Append fMemo

' update info in memo field with info from text field
db.Execute "UPDATE Table1 SET Comments1 = Comments", dbFailOnError

' delete text field
td.Fields.Delete f.Name

' rename memo field
fMemo.Name = "Comments"

' reclaim memory
Set f = Nothing
Set fMemo = Nothing
Set td = Nothing
Set db = Nothing

Simon