renaming a column using sql. 1

[barny2006]

Hi folks,
i have and access table that is loded from an excel table. i have to change a column name using vbs and sql. i tried the RENAME command, and it didn't work. the add column function works, and i can add columns. but not the REAME command. anybody knows why this is not working?
here's the code.

commandstring = "ALSTER TABLE May_data RENAME old_col TO new_col;"
objConnection.Execute commandstring

thanks.

 

[PHV]

Provided you have instantiated a Database object:
yourDBobject.TableDefs("May_data").Fields("old_col").Name = "new_col"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[barny2006]

i'm not sure if understand the thing about instantiating the database object. the database is an access database named. steve.mdb. the table's name is may_data. how would i "instantiate" the database object please?

 

[barny2006]

here is the code:

Set objConnection = CreateObject("ADODB.Connection")
objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = c:\steve.mdb"
commandstring = "ALTER TABLE May_data ..??????
objConnection.Execute commandstring

 

[PHV]

I think the ADO way is to play with an ADOX.Catalog

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[barny2006]

phv,
my question is how would i instantiate the db oeject to make this piece of code work:
""""
Provided you have instantiated a Database object:
yourDBobject.TableDefs("May_data").Fields("old_col").Name = "new_col"

 

[PHV]

You may try something like this:
Set myDB = GetObject("c:\steve.mdb").CurrentDb
myDB.TableDefs("May_data").Fields("old_col").Name = "new_col"
myDB.Close
set myDB = Nothing

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[barny2006]

phv,
you're great. that did the trick. i had looked all over google to find the syntax for rename. most people just put "alter table mytable rename old_col to new_col column" which doesn't work.
this definitely works. thanks again.