Re: rename a column i Access

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Bob Barrows [MVP] (reb01501_at_NOyahoo.SPAMcom)
Date: 11/07/04 

Date: Sun, 7 Nov 2004 09:57:03 -0500

TroelsM wrote:
> 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.««

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.

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

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"

Relevant Pages

  • Re: rename a column i Access
    ... thanks for You answer - I can´t remember where I found the rename. ... But I followed Your example with changing the driver to JET and now my ... > ALTER TABLE DatoCurrency ADD COLUMN NewName datatype ... > You can use ADOX to rename a column. ...
    (microsoft.public.inetserver.asp.db)
  • Re: renaming an mdb table from D7 using ADOX
    ... >DRS wrote: ... > with ADOX. ... Add Variants to the uses clause for D6 and higher. ... It worked a treat and I can now rename ...
    (borland.public.delphi.database.ado)