Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    change propert by code (Access 2000)

    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

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: change propert by code (Access 2000)

    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
    Regards
    John



  3. #3
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: change propert by code (Access 2000)

    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

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: change propert by code (Access 2000)

    My second bit of code was how to change it.

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



  5. #5
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: change propert by code (Access 2000)

    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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: change propert by code (Access 2000)

    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.

  7. #7
    2 Star Lounger
    Join Date
    Jun 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Thank you

    Dear Hans,

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •