Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Location
    Deer Park, Long Island
    Posts
    12

    Unanswered: Access, (Should be) Simple SQL to Rename Column

    Hello to all,

    I have, what should be a simple SQL statement to Rename a Column in an Access table:

    DoCmd.RunSQL "ALTER TABLE tblRenCol RENAME COLUMN Jeff TO Jeffrey"

    It fails with the error:
    Error #3293
    Syntax error in ALTER TABLE statement

    Simple eh?

    It also fails with:
    DoCmd.RunSQL "ALTER TABLE tblRenCol RENAME COLUMN Jeff TO Jeffrey;"
    And:
    DoCmd.RunSQL "ALTER TABLE [tblRenCol] RENAME COLUMN [Jeff] TO [Jeffrey]"

    Now, I am using these SQL commands to Convert Data Types, and even to DROP (Delete) columns, and they work fine.
    (And, no I am not trying RENAME the same column I DROPed!)


    What could the problem be?
    OR, is there another way to Rename a Column in an Access table with VBA?

    Thanks,

    Jeff
    jeffcoach

  2. #2
    Join Date
    Mar 2005
    Posts
    261
    Can't be done in Access DDL statements - doesn't support RENAME.

    Instead use DAO or ADO(/X). e.g. in DAO...

    Code:
    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)

    HTH

    Wayne Phillips
    http://www.everythingaccess.com

  3. #3
    Join Date
    Jan 2004
    Location
    Deer Park, Long Island
    Posts
    12
    waynephillips,

    Thanks for the speedy reply!


    I will try it out tommorrw and let you know.
    jeffcoach

  4. #4
    Join Date
    Jan 2004
    Location
    Deer Park, Long Island
    Posts
    12
    waynephillips,

    Thanks!

    It worked great!

    Also, Is there a list somewhere of the SQL keywords that WONT work in Access?


    Jeffc
    jeffcoach

  5. #5
    Join Date
    Apr 2012
    Posts
    3

    A Simpler Way?

    this is not dao or ado. One line only, in Access 2007:

    Code:
    CurrentDb.TableDefs("MyTable").Fields("OldFieldName").Name="NewFieldName"

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by johnywhy View Post
    this is not dao or ado.
    Actually, this is DAO: CurrentDb is a DAO.Database object that has DAO.Tabledef objects that have DAO.Fields objects.
    Have a nice day!

Posting Permissions

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