Alter table

[timothyl]

New to writing sql. From my books and on line search this seems like it would work.

ALTER TABLE tblparts1 CHANGE HCPCRow1 Hcpc

get syntac error, CHANGE , have tried: change Column, Modify, no luck. Would some one tell me the proper statment.

Thanks Tim

 

[rainman89]

What are you trying to do? Change the values or change the column name?

this changes a column name

ALTER TABLE [I]table[/I] [ * ]
      RENAME [ COLUMN ] [I]column[/I] TO [I]newcolumn[/I]

see here

http://www.commandprompt.com/ppbook/r22871.htm

 

[timothyl]

Thank you rainman89. Rainman89 if I have more then one column is it
ALTER TABLE mytable RENAME COLUMN mycolumn1, mycolumn2 TO new1, new2

Or

ALTER TABLE mytable RENAME COLUMN mycolumn1 TO new, RENAME COLUMN mycolumn2 TO new2

 

[rainman89]

I believe it would be like this

ALTER TABLE mytable RENAME COLUMN (mycolumn1 TO new1, mycolumn2 TO new2)

but i have not tried it

 

[timothyl]

Thanks again, I'll give it a try.

 

[timothyl]

Turns out Access dose not support RENAME, I found this on another fourm, works well

Sub RenameField(strTableName As String, _
strFieldFrom As String, _
strFieldTo As String)

Dim dbs As DAO.Database
Dim tDef As DAO.TableDef
Dim fDef As DAO.Field

Set dbs = CurrentDb()
Set tDef = dbs.TableDefs(strTableName)
Set fDef = tDef.Fields(strFieldFrom)

fDef.Name = strFieldTo

Set fDef = Nothing
Set tDef = Nothing
Set dbs = Nothing

'Then just call it like: RenameField "tblRenCol","Jeff","Jeffrey"

End Sub

 

[Slade2000]

vb i understand a bit. Can i just copy that code into a button?

 

[timothyl]

No copy the code, hit alt and f11 then paste. Under a button go

RenameField "myTableName","ColumnName","NewColumnName"

RenameField "myTableName","ColumnName2","NewColumnName2"
ect
if you have trouble post back. Tim

 

[Slade2000]

Private Sub Command1_Click()
RenameField "Table1", "Field1", "Jeffrey"


End Sub

This is my code in the button, table1 is my actual table name and field1 is what my field name is and jeffry is what i want it to be. id i click the button it gives me a compile error on the botled line.

Sub RenameField(strTableName As String, _
strFieldFrom As String, _
strFieldTo As String)

Dim dbs As DAO.Database
Dim tDef As DAO.TableDef
Dim fDef As DAO.Field

Set dbs = CurrentDb()
Set tDef = dbs.TableDefs(strTableName)
Set fDef = tDef.Fields(strFieldFrom)

fDef.Name = strFieldTo

Set fDef = Nothing
Set tDef = Nothing
Set dbs = Nothing

'Then just call it like: RenameField "tblRenCol","Jeff","Jeffrey"

End Sub

 

[Slade2000]

error is a Run Time: 3265

 

[Slade2000]

Sorry it works i see it does it when i press the button for the seccond time coz it does not contain the origenal field name any more. Tnx alllllot

 

[timothyl]

I dont know if this is helpful, but you could make three text boxes MyTable, MyFieldName and MyNewFieldName then go

RenameField "" & Me.MyTable & "", "" & Me.TableFieldName & "", "" & Me.MyNewFieldName &""

anyway glade to see you got it sorted out. Tim

 

[Slade2000]

Jip this is what i wanted to do. tnx allot for all the help.