Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2009
    Posts
    85

    Unanswered: ALTER TABLE change column name

    Having trouble with alter column statment it erase's my column, could some write me the corect statement, I've look on line but there seems to be no uniformity in how to do this and I have tried numerous ways and i'm having no luck

    ALTER TABLE mytable ALTER COLUMN (mycolumn1 TO new1, mycolumn2 TO new2)

    Thanks. Tim

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Assuming you have to do this in SQL, you'll need to do it in three stages:
    Create new column
    Update new column to old column values
    Delete old column

    Intermediate Microsoft Jet SQL for Access 2000
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Apr 2009
    Posts
    85
    Thanks pootle flump, if not in sql then how ?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Well, using the Access GUI? Any reason not to just do it like that?

    Maybe using DAO\ ADO\ ADOX otherwise.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Apr 2009
    Posts
    85
    Sub RenameField(strTableName As String, _
    strFieldFrom As String, _
    strFieldTo As String)

    Dim dbs As DAO.Database
    Dim tDef As DAO.TableDef
    Dim fDef As DAO.Field

    Set dbs = CurrentDb()
    Set tDef = dbs.TableDefs(strTableName)
    Set fDef = tDef.Fields(strFieldFrom)

    fDef.Name = strFieldTo

    Set fDef = Nothing
    Set tDef = Nothing
    Set dbs = Nothing

    End Sub
    Then just call it like: RenameField "tblRenCol","Jeff","Jeffrey"
    (Note: no error handling in the above example)

    Yes many reasons, the above works like a charm, found it on a earlier thread on this fourm

Posting Permissions

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