Professional Web Applications Themes

rename a column i Access - ASP Database

Hi there ! I am trying to rename a column i Access Set Conn1 = Server.CreateObject("ADODB.Connection") Conn1.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("\fpdb\currency.mdb") sSQL = "ALTER TABLE DatoCurrency " sSQL = sSQL & " COLUMN " & ucase(retcur) & " RENAME TO " & UCASE(chkstring(request.form("cur"),"SQLString")) conn1.execute sSQL conn1.close but I keep getting this error. »»Microsoft][ODBC Microsoft Access Driver] Syntax error in ALTER TABLE statement.«« AnyOne know what I am doing wrong or have any idea on how to do this...

  1. #1

    Default rename a column i Access

    Hi there !
    I am trying to rename a column i Access
    Set Conn1 = Server.CreateObject("ADODB.Connection")
    Conn1.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" &
    Server.MapPath("\fpdb\currency.mdb")
    sSQL = "ALTER TABLE DatoCurrency "
    sSQL = sSQL & " COLUMN " & ucase(retcur) & " RENAME TO " &
    UCASE(chkstring(request.form("cur"),"SQLString"))
    conn1.execute sSQL
    conn1.close

    but I keep getting this error.
    »»Microsoft][ODBC Microsoft Access Driver] Syntax error in ALTER TABLE
    statement.««

    AnyOne know what I am doing wrong or have any idea on how to do this

    TroelsM Guest

  2. #2

    Default Re: rename a column i Access

    http://www.aspfaq.com/5003

    Ray at home

    "TroelsM" <microsoft.com> wrote in message
    news:com... 


    Ray Guest

  3. #3

    Default Re: rename a column i Access

    HI Ray !
    Yes I know I crosspost - but I didnt know in wich of the 2 groups I colud
    get an answer.


    "Ray Costanzo [MVP]" wrote:
     
    >
    >
    >[/ref]
    TroelsM Guest

  4. #4

    Default Re: rename a column i Access

    You didn't crosspost. You multiposted.

    Ray at home

    "TroelsM" <microsoft.com> wrote in message
    news:com... 
    >>
    >>
    >>[/ref][/ref]


    Ray Guest

  5. #5

    Default Re: rename a column i Access

    OK, sorry, i'll dont do it again,
    but do You have a solution to my prob. I have made a reply in the other forum.

    TroelsM

    "Ray Costanzo [MVP]" wrote:
     [/ref]
    >
    >
    >[/ref]
    TroelsM Guest

  6. #6

    Default Re: rename a column i Access

    TroelsM wrote: 

    Nothing to do with your problem, but you should stop using the obsolete ODBC
    driver. The native Jet OLEDB provider offers more functionality and is more
    robust. See www.able-consulting.com/ado_conn.htm for an example.
     

    Ray's already taken care of the multiposting problem, so I will concentrate
    on the actual problem, doing it here in this group since it is a
    database-related question (hence the "db" in the newsgroup name).

    Where did you find the RENAME keyword? It certainly is not mentioned in
    Access online help (see the Microsoft Jet SQL Reference section in the table
    of contents).

    The only way to rename a column using DDL SQL (Data Definition Language) is
    to add a column:

    ALTER TABLE DatoCurrency ADD COLUMN NewName datatype(size)

    Run an update statement to copy the data from the old column to the new one:
    UPDATE DatoCurrency SET NewName = OldName

    Then drop the old column:
    ALTER TABLE DatoCurrency DROP COLUMN OldName

    You can use ADOX to rename a column. Search www.aspfaq.com for an example
    (use the keywords column and ADOX).

    Bob Barrows

    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"


    Bob Guest

  7. #7

    Default Re: rename a column i Access

    Hi Bob,
    thanks for You answer - I can´t remember where I found the rename.
    So I made it with ADD new , and DELETE old and used a loop to transfer
    values, because I could not get UPDATE to work.

    But I followed Your example with changing the driver to JET and now my
    Update command works - so I don't have to loop to all records to transfer the
    values.

    Tx

    "Bob Barrows [MVP]" wrote:
     
    >
    > Nothing to do with your problem, but you should stop using the obsolete ODBC
    > driver. The native Jet OLEDB provider offers more functionality and is more
    > robust. See www.able-consulting.com/ado_conn.htm for an example.

    >
    > Ray's already taken care of the multiposting problem, so I will concentrate
    > on the actual problem, doing it here in this group since it is a
    > database-related question (hence the "db" in the newsgroup name).
    >
    > Where did you find the RENAME keyword? It certainly is not mentioned in
    > Access online help (see the Microsoft Jet SQL Reference section in the table
    > of contents).
    >
    > The only way to rename a column using DDL SQL (Data Definition Language) is
    > to add a column:
    >
    > ALTER TABLE DatoCurrency ADD COLUMN NewName datatype(size)
    >
    > Run an update statement to copy the data from the old column to the new one:
    > UPDATE DatoCurrency SET NewName = OldName
    >
    > Then drop the old column:
    > ALTER TABLE DatoCurrency DROP COLUMN OldName
    >
    > You can use ADOX to rename a column. Search www.aspfaq.com for an example
    > (use the keywords column and ADOX).
    >
    > Bob Barrows
    >
    > --
    > Microsoft MVP - ASP/ASP.NET
    > Please reply to the newsgroup. This email account is my spam trap so I
    > don't check it very often. If you must reply off-line, then remove the
    > "NO SPAM"
    >
    >
    >[/ref]
    TroelsM Guest

Similar Threads

  1. Rename Query Column Name
    By DickBobUK in forum Coldfusion Database Access
    Replies: 5
    Last Post: March 4th, 10:00 PM
  2. Replies: 1
    Last Post: December 14th, 11:53 AM
  3. How to rename Access tables and field names?
    By Marshal in forum ASP Database
    Replies: 3
    Last Post: October 9th, 05:46 PM
  4. Convert an MS Access Yes/No column to a checkbox column in C# datagrid
    By Gregory Rampton in forum ASP.NET Data Grid Control
    Replies: 0
    Last Post: August 6th, 04:09 PM
  5. Adding a new column in Access
    By Peter Richards in forum ASP Database
    Replies: 11
    Last Post: July 30th, 12:59 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139