Rename Table?

[blam]

Can anybody tell me how to rename a table in VB?

Hard to believe this simple task is not a straightfowdard feature. Maybe it's so straightforward that I'm missing something?

I have resorted to creating a new table (with the new name desired) copying all contents from it to the new renamed table, then dropping the old one.

Surely there must be a better and faster way? If the table contains a lot of records, there is a big delay with my method.

I have tried

dbs.execute("RENAME TABLE name1 TO name2")
but this does not work.

Any help is appreciated.

[VorTechS]

Depends on what type your database is:-

If your using an access database, you can loop through each of the tabledefs in the database, using the .name property to identify/rename your existing table.

IE: (very rough and not tested so you may need to play with it...)

dim x as tabledef

for each x in dbMydatabase
if x.name = 'MyTable' then
x.name = 'MyNewTable'
endif
next x

Note: If you are just changing the case of a database name (ie to Upper/Lowercase) you will need to rename the table to something other than it's existing name first, before changing it. Or in better terms, you can't change a table to the same name without getting an error.

If it's anything other than an access table, then you can use a SELECT INTO statement to create a new table with the new name, and then use DROP TABLE to remove the old table.

That outta do it for you....

[This message has been edited by VorTechS (edited 09-27-1999).]