Rename Table from Visual Basic 1

[briglass]

I'm trying to rename table RED in apple.mdb to RED1 using SQL commands issued by visual basic.

Or, is there an easier way with ADO and visual basic?

Thanks,
Brian

 

[tlbroadbent]

In VBA you can use the DoCMD.Rename method.

DoCmd.Rename "OldName", acTable, "NewName"
Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm

 

[briglass]

thanks, i'll try that.

but is there an SQL method?

 

[Kubilus1]

SQL Books online gives the following syntax to rename objects in T-SQL:

sp_rename [@objname =] 'object_name',
[@newname =] 'new_name'
[, [@objtype =] 'object_type']


object_name-> Current name of the object

new_name-> new name for the object

object_type -> Says wether you are renaming a column or object. For your purposes, this can be ignored.

Matt

 

[Kubilus1]

I should add, this code is for SQL7. I am not sure how compatible it is for jet SQL, as in Access. It should be worth a shot though.

 

[briglass]

Thanks for you help, but I'm still having problems.

I have tried the SQL script "RENAME TABLE happy TO nothappy;" but that didn't work.

I also tried dbTable.Name = "happy" but that didn't work.

**Is there any way to change the name of a table that is in an Access 97 database using visual basic 6?**

Thanks,
Brian

 

[tlbroadbent]

I don't know of any SQL command to rename a table. You could use the following to rename a table in two steps but I don't recommend it.

Select * into NewName From OldName
Drop Table OldName

Of course, you will have recreate the primary keys, relationships, indexes, etc.

Here is another solution using DAO as "Docmd.Rename" may not be available in VB6.

Dim db As Database
Set db = OpenDatabase("C:\SomePath\Your.mdb&quot
db.TableDefs("OldTable&quot.Name = "NewTable"
db.Close

Terry L. Broadbent
Programming and Computing Resources

http://tlbroadbent.home.attbi.com/prog.htm