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

Re: rename a column i Access

http://www.aspfaq.com/5003

Ray at home

"TroelsM" wrote in message
news:6B858CF2-8C71-4E30-B51E-D6B1CA8DBD9B@microsoft.com...
> 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
>

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:

> http://www.aspfaq.com/5003
>
> Ray at home
>
> "TroelsM" wrote in message
> news:6B858CF2-8C71-4E30-B51E-D6B1CA8DBD9B@microsoft.com...
> > 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
> >
>
>
>

Re: rename a column i Access

You didn't crosspost. You multiposted.

Ray at home

"TroelsM" wrote in message
news:51AF18CC-C71A-4E83-A47A-9B76F0185419@microsoft.com...
> 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:
>
>> http://www.aspfaq.com/5003
>>
>> Ray at home
>>
>> "TroelsM" wrote in message
>> news:6B858CF2-8C71-4E30-B51E-D6B1CA8DBD9B@microsoft.com...
>> > 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
>> >
>>
>>
>>

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:

> You didn't crosspost. You multiposted.
>
> Ray at home
>
> "TroelsM" wrote in message
> news:51AF18CC-C71A-4E83-A47A-9B76F0185419@microsoft.com...
> > 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:
> >
> >> http://www.aspfaq.com/5003
> >>
> >> Ray at home
> >>
> >> "TroelsM" wrote in message
> >> news:6B858CF2-8C71-4E30-B51E-D6B1CA8DBD9B@microsoft.com...
> >> > 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
> >> >
> >>
> >>
> >>
>
>
>

Re: rename a column i Access

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"

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:

> 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"
>
>
>