Results 1 to 13 of 13
  1. #1
    Join Date
    Nov 2003
    Posts
    267

    Unanswered: Changing Field Descriptions using SQL or VB

    Okay guys, I need help (if possible)

    Is there a way for me, using either SQL or VB or whatever, to change the field descriptions in my Access DB.

    I have a database project that is used in multiple locations (each loaction has seperate Data DB) I need to make a massive amounts of data structure changes, due to new system requirements, And I was planning on writing a script, that each location could run, that would make the changes, without me having to manually get each location DB and changing it myself.

    The problem I have is they have a requirement that each field needs a description, but I can't find example, or instructions, anywhere that would show me how to edit, or include the field desciption as part of a Create Table statement, or any other way of changing the Field Desciption.

    Is there a way to do this and how can I do it?

    Thanks

    S-

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Changing Field Descriptions using SQL or VB

    Originally posted by sbaxter
    Okay guys, I need help (if possible)

    Is there a way for me, using either SQL or VB or whatever, to change the field descriptions in my Access DB.

    I have a database project that is used in multiple locations (each loaction has seperate Data DB) I need to make a massive amounts of data structure changes, due to new system requirements, And I was planning on writing a script, that each location could run, that would make the changes, without me having to manually get each location DB and changing it myself.

    The problem I have is they have a requirement that each field needs a description, but I can't find example, or instructions, anywhere that would show me how to edit, or include the field desciption as part of a Create Table statement, or any other way of changing the Field Desciption.

    Is there a way to do this and how can I do it?

    Thanks

    S-
    TableDef collection? I've not needed to do so myself ....

  3. #3
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    I'm assuming by Description, you are referring to the long text thingy beside the column/field data type definition.

    Using ADO and ADOX, the following will do it. I'm pretty sure you know how to loop collections:

    Dim cat As New ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim col As ADOX.Column
    Dim prp As ADOX.Property

    cat.ActiveConnection = CurrentProject.Connection ' or whatever connection string you need.
    Set tbl = cat.Tables("YourTableName")
    Set col = tbl.Columns("YourColumn")
    col.Properties("Description") = "New Description"

    Set tbl = Nothing
    Set cat = Nothing
    All code ADO/ADOX unless otherwise specified.
    Mike.

  4. #4
    Join Date
    Nov 2003
    Posts
    267
    Thanks Guys, I knew this had to be possible, I will give this a try and let you know how it goes.

    S-

  5. #5
    Join Date
    Nov 2003
    Posts
    267
    HomerBoo

    Got the ADOX to work with the Description, How do I change the Validation Rule?

    Thanks

    S-

  6. #6
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Not sure. What I did was loop through each column's properties to see what their names were, then see if I could set the Description. There may be props you can't access.

    Dim cat As New ADOX.Catalog
    Dim tbl As ADOX.Table
    Dim col As ADOX.Column
    Dim prp As ADOX.Property

    cat.ActiveConnection = CurrentProject.Connection ' or whatever connection string you need.
    Set tbl = cat.Tables("YourTableName")
    for each col in tbl.columns
    for each prp in col.properties
    debug.print col.name, prp.name, prp.value
    next prp
    next col

    ...

    there may be a syntax error, i'm not infront of Access, but that's how I enumerated the props.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  7. #7
    Join Date
    Nov 2003
    Posts
    267
    Thanks


    col.Properties("Jet OLEDB:Column Validation Rule")
    col.Properties("Jet OLEDB:Column Validation Text")

    If you ever want to know

  8. #8
    Join Date
    Nov 2003
    Posts
    267
    Any buddy know how to change the field format using ADOX. I can't find anything that lets me control it.

    S-

  9. #9
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    What do you mean, changing from text to numeric or changing from medium date to long date within a date column?
    All code ADO/ADOX unless otherwise specified.
    Mike.

  10. #10
    Join Date
    Nov 2003
    Posts
    267
    medium date to long date

    S-

  11. #11
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    I wish I could be more help, but I can't find anything on this.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  12. #12
    Join Date
    Nov 2003
    Posts
    267
    Thanks, Maybe someone else knows how.


    S-

  13. #13
    Join Date
    Nov 2003
    Posts
    267
    If any one cares... all the research I have doen has come back and tells me you can't change the field format using ADOX.

    S-

Posting Permissions

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