Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2007
    Posts
    77

    Talking Unanswered: how to rename a column in access using SQL statements

    Hi to all,

    I have been using MS Access as my database and I want to rename a certain column which is called "CARD NUMBER", and is a primary key in my table. I tried using the ALTER TABLE statement but I get an error in my ALTER TABLE statement.

    I have researched it the internet about the syntax but it did me no good, I hope anyone could give me the correct syntax on the ALTER statement. I think there is something wrong with my syntax...

    Thanks and god bless!

  2. #2
    Join Date
    Feb 2007
    Posts
    348
    are you coding this or just in design view of a query? In design view, I'd just use NewName:Card Number in SQL it's normally NewName AS Card Number but I've never tried that in Access SQl view.

  3. #3
    Join Date
    Mar 2007
    Posts
    77
    I am coding this, I want the column in my table to be updated. I want it to be modified...Pure code, not in design view...

  4. #4
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    I don't think SQL supports any "rename" keywords, I think you'll need to create a new column, update it, then drop the existing column (air code)

    currentdb.execute "ALTER TABLE myTable ADD COLUMN myColumn text(25)", dbfailonerror
    currentdb.execute "UPDATE myTable SET myColumn = myOldColumn", dbfailonerror
    currentdb.execute "ALTER TABLE myTable DROP COLUMN myOldColumn", dbfailonerror

    But why not just use DAO or ADOX in stead, where I think you should be allowed to rename

    CurrentDb().TableDefs("myTable").Fields("myOldColu mn").Name = "myColumn"
    Roy-Vidar

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Access / JET may not let you change the primary key in code. you may have to drop the indexes, make your changes, then reassert the indexes.

    even if you do change the column name through code it will not ripple those changes throughout the code base (eg any queries, forms or reports).

    In all honesty If I were you Id do it manually, id want to thoroughly control the process and then test like crazy afterwards, before relasing on an unsuspecting userbase
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    Ah, I didn't pick up that it was the primary key.
    Roy-Vidar

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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