#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Posts
    27
    Rep Power
    0

    Change a field name in a table


    Hi

    I'm using VB2008 and ADO to maintain an Access database.

    In a table I have a field named 'StudentID' which should be 'Student ID' (with a space).

    I've tried the following sql statements to change it but nothing seems to work. I've read in various places that the RENAME COLUMN command doesn't work anyway.


    sSql = "ALTER TABLE [Experiences] RENAME COLUMN StudentID to 'Student ID'"

    sSql = "ALTER TABLE [Experiences] RENAME COLUMN [StudentID] to [Student ID]"

    sSql = "ALTER TABLE [Experiences] CHANGE COLUMN StudentID, 'Student ID'"

    and various other combinations....

    Can anyone suggest something that will work, other than creating a new table and copying all of the data across ...?

    Thanks in anticipation......
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,031
    Rep Power
    4210
    Originally Posted by Rory Butler
    In a table I have a field named 'StudentID' which should be 'Student ID' (with a space).
    why?
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Posts
    27
    Rep Power
    0

    Because...


    Because I have a large number of customers out there using the database. Some have the field as 'Student ID' and others have 'StudentID'. The program crashes with the incorrect field and I would like to be able to change the faulty ones programmatically? OK????
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,031
    Rep Power
    4210
    Originally Posted by Rory Butler
    OK????
    don't get mad -- i was just askin

    how can some users use different column names from other users if they're using the same database?

    how can a program "crash" with the incorrect field?

    and if you are going to change it, please don't change the column name so it contains a space -- that's just axin fo trouble
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Posts
    27
    Rep Power
    0

    Apologies....


    I accidentally released a version of the software which created [StudentID] as the field and later changed it to create [Student ID]. (Yes I know its bad....) So I've ended up with 10% of my customers with the wrong field name and I need to release a version with a routine to correct the field name where it is wrong. Can you give me any help with code which would do the trick please?
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,031
    Rep Power
    4210
    Originally Posted by Rory Butler
    Can you give me any help with code which would do the trick please?
    sorry, i don't think what you're asking is possible

    here's a suggestion -- develop a script which creates a new table, populates it from ~either~ of your existing tables, then does a drop and rename
    r937.com | rudy.ca
    please visit Simply SQL and buy my book
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2008
    Posts
    27
    Rep Power
    0

    That's what I was afraid of....


    Yes, I guess that's what I will have to do. Looking up various forums I see that many people have asked to be able to do what I want to do and the reply is always 'That's easy - just use

    sSql = "ALTER TABLE [Table name] RENAME COLUMN Oldname to Newname

    and, of course, when I try it it doesn't work.

    Thanks for your advice. I'll create a new table, populate it with the contents of the old, then drop the old and have the program use the new table..... unless you reply 'Wait a minute - You can do that.....'

    Thanks again
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2011
    Posts
    2
    Rep Power
    0

    you can use a GUI tool


    you can use a GUI tool, i use dbschema

IMN logo majestic logo threadwatch logo seochat tools logo