Problem renaming column with SQL 1

[PeDa]

I am trying to use the following:

MySQL = "ALTER TABLE MyTable RENAME COLUMN MyColumn to MyNewColumn;"
DoCmd.RunSQL MySQL

This yields:
Run time error '3293': Syntax error in ALTER TABLE statement

What is wrong here; MyTable and MyColumn are valid names.

 

[dodge20]

Access doesn't support this alter table statement. It has to be done using VB.

Dodge20

 

[dodge20]

Something like

Dim db As Database
Set db = CurrentDb
' Rename the field
db.TableDefs("[" & TblName & "]&quot.Fields("CurrentFieldName&quot.Name = NewFieldName


Dodge20

 

[MichaelRed]

It (renaming Fields in a data base) is also generally avoided. Other objects may (usually DO reference tables/fields through the name properties. Changing an object name would normally require corresponding changes in all other objects which reference the object, including secondary and teritary references.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[PeDa]

Thanks Dodge20 and MichaelRed,

dbf.TableDefs("MyTable&quot.Fields("MyColumn&quot.Name = "MyNewColumn" worked fine.

I am aware that changing column names is in principle not a great idea. I must change the content of my index field, so will batchwise (i) add a new field, (ii) fill this with the newly calculated index, (iii) delete the old index and (iv) rename the new field to the old index field name.