change propert by code (Access 2000)

[pilgrimswake]

How could i change an existing field per code? My existing field in the table is called description and i want to change its property from from text to memo.i have a function that creates this field, but i do not now how to change the property from text to memo.Can you help me ?
here is my function for creation of the field :
Public Function CreateFieldsInCustomers()
On Error GoTo ErrProc
' create new fields in table Products

Dim StrPassword As String
StrPassword = "secret"
Dim wsp As DAO.Workspace
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property
Set wsp = DAO.DBEngine.Workspaces(0)
Set dbs = wsp.OpenDatabase("C:BEstoreBE.mdb", False, False, ";PWD=" & StrPassword)

Set tdf = dbs.TableDefs("TblClients")

'create the field customerid
Set fld = tdf.CreateField("Description", dbText)
tdf.Fields.Append fld
tdf.Fields.Refresh


dbs.Close
Set fld = Nothing
Set tdf = Nothing
Set dbs = Nothing
Set wsp = Nothing
Exit Function
ErrProc:
If Err = 3191 Then
Resume Next
Else
MsgBox Err.Description ' or something else you want
End If
End Function

[johnhutchison]

Do you want to create it as a memo? or create it as text then change it to a memo?

'create the field description
Set fld = tdf.CreateField("Description", dbMemo)
tdf.Fields.Append fld
tdf.Fields.Refresh

or
'get the field description
Set fld = tdf.Fields("Description")
fld.Type = dbMemo
tdf.Fields.Refresh

[pilgrimswake]

Thank you for your reply.The point is that i have already a field with a text property and i need to amend it, or change it to have a memo property.How can i proceed ? I am araid of doubling the properties.Thank you in advance

[johnhutchison]

My second bit of code was how to change it.

Test it with something that does not matter to try it out.

[pilgrimswake]

Thank you for the reply. I get the error "invalid operation.perhaps i didnt use exactly your recommendation.Could you have a look at my code? it is :

Public Function CreateFieldsInCustomers()
On Error GoTo ErrProc

Dim StrPassword As String
StrPassword = "srq"
Dim wsp As DAO.Workspace
Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property
Set wsp = DAO.DBEngine.Workspaces(0)
Set dbs = wsp.OpenDatabase("C:BEstoreBE.mdb", False, False, ";PWD=" & StrPassword)

Set tdf = dbs.TableDefs("Products")

Set fld = tdf.Fields("Description")
fld.Type = dbMemo
tdf.Fields.Refresh

dbs.Close
Set fld = Nothing
Set tdf = Nothing
Set dbs = Nothing
Set wsp = Nothing
Exit Function
ErrProc:
If Err = 3191 Then
Resume Next
Else
MsgBox Err.Description ' or something else you want
End If
End Function

Please note i already have a text field called descripton in the table Products

[HansV]

According to the DAO help, the Type property of a field can only be changed when you have created a new field and haven't appended it to the table's Fields collection yet. After you have appended the field, the Type property is read-only.

You can execute an SQL statement to change the field type:

Function CreateFieldsInCustomers()
Dim strPassword As String
Dim strSQL As String
Dim wsp As DAO.Workspace
Dim dbs As DAO.Database
On Error GoTo ErrProc
strPassword = "srq"
Set wsp = DAO.DBEngine.Workspaces(0)
Set dbs = wsp.OpenDatabase("C:BEstoreBE.mdb", False, False, ";PWD=" & strPassword)
strSQL = "ALTER TABLE Products ALTER COLUMN Description MEMO"
dbs.Execute strSQL, dbFailOnError
ExitProc:
dbs.Close
Set dbs = Nothing
Set wsp = Nothing
Exit Function
ErrProc:
MsgBox Err.Description ' or something else you want
Resume ExitProc
End Function

PS The name of your function is confusing. You're not creating a field but trying to change it, and the field is in the Products table, not in the Customers table. This makes it more difficult to maintain the code. I would name it ChangeFieldInProducts or something like that.

[pilgrimswake]

Dear Hans,

Just to let you know that my programme is flying now and i thank you so much for that