Rename table name

[Hoang Vu]

Hi

I tried to rename a table name by using SQL as:

Dim db As Database
Set db = CurrentDb
db.Execute "ALTER TABLE tabname RENAME new_tabname"

Can you see what is wrong with this code?

I'm using Access 2000.

Thanks
Hoang

[Mike MacSween]

I can't find any reference to RENAME in the help file for the ALTER TABLE
statement. The VBA DoCmd.Rename method looks like the way to go. Is there
some reason you want to use SQL for this?

HTH, Mike MacSween

"Hoang Vu" <hoan...@vianova.no> wrote in message
news:ap0mgh$1u2$1...@oslo-nntp.eunet.no...

[Gareth Gibbings]

In message <ap0mgh$1u2$1...@oslo-nntp.eunet.no>, Hoang Vu
<hoan...@vianova.no> writes

Hoang,
The syntax (from Access 2000 Help) is:

ALTER TABLE table {ADD {COLUMN field type[(size)][NOT NULL][CONSTRAINT
index] |
ALTER COLUMN field type[(size)] |
CONSTRAINT multifieldindex} |
DROP {COLUMN field I CONSTRAINT indexname} }

The syntax does not include a RENAME clause.

You could, however, use Docmd.Rename on a closed database object:

DoCmd.Rename "New Table Name", acTable, "Old Table Name"
--
Gareth Gibbings

[Douglas J. Steele]

The following will do it:

Dim db As Database
Set db = CurrentDb

db.TableDefs("tabname").Name = "new_tabname"

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele

"Hoang Vu" <hoan...@vianova.no> wrote in message
news:ap0mgh$1u2$1...@oslo-nntp.eunet.no...

[Hoang Vu]

Hi Gareth, Mike and Douglas

Thank you for your answers, but
why I don't want to use Docmd.Rename or db.TableDefs("tabname").Name =
"new_tabname" is
because I would like to move my application from Access to Visual Basic as
stand alone appl.

By using db.Execute we are not depending on DAO or ADO, right?

Gareth and Mike:
The RENAME clause I found is for Visual FoxPro (MSDN)

I also would like to convert my code from DAO to ADO. Is it possible to
modify tables by using ADO?

Hoang

[Gareth Gibbings]

In message <ap354r$s6h$1...@oslo-nntp.eunet.no>, Hoang Vu
<hoan...@vianova.no> writes

>By using db.Execute we are not depending on DAO or ADO, right?

EXECUTE is used as part of Jet SQL.

>
>Gareth and Mike:
>The RENAME clause I found is for Visual FoxPro (MSDN)

In Visual FoxPro (from version 3.0) there is a NATIVE command - RENAME
TABLE - this is not a clause of an SQL statement. The command does not
actually rename a table, it provides an ALIAS for the target table.

>
>I also would like to convert my code from DAO to ADO. Is it possible to
>modify tables by using ADO?

Yes, using DML statements, as one would with ANSI SQL.

You should also question any renaming solution you decide on.
Consider the effects on referential integrity.

--
Gareth Gibbings

[Hoang Vu]

Thanks Gareth
It seems that we can use ADOX.

Hoang

"Gareth Gibbings" <gar...@gkgib.demon.co.uk> wrote in message
news:76Z2xwNI...@gkgib.demon.co.uk...

[Douglas J. Steele]

Database is a DAO object, therefore db.Execute uses DAO.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele

"Hoang Vu" <hoan...@vianova.no> wrote in message

news:ap354r$s6h$1...@oslo-nntp.eunet.no...

[Mike MacSween]

"Hoang Vu" <hoan...@vianova.no> wrote in message
news:ap354r$s6h$1...@oslo-nntp.eunet.no...

> Hi Gareth, Mike and Douglas
>
> Thank you for your answers, but
> why I don't want to use Docmd.Rename or db.TableDefs("tabname").Name =
> "new_tabname" is
> because I would like to move my application from Access to Visual Basic as
> stand alone appl.

db.TableDefs etc. should work in VB. Assuming you want to use DAO, which is
probably still best for a Jet backend.

> By using db.Execute we are not depending on DAO or ADO, right?

It looks to me like your db variable is a database object, so yes you are
using DAO.

> Gareth and Mike:
> The RENAME clause I found is for Visual FoxPro (MSDN)

I think that's a FoxPro command, not a SQL statement. Don't let the CAPITALS
fool you. And why are you referring to the FoxPro help files anyway?

Mike